SELECT a.*, b.ci_name AS leixingname, c.ci_name AS yuyanname, 
      d.ci_name AS pingtainame, e.ci_name AS huanjingname, f.ui_name
FROM dbo.xj_TeachresInfo a LEFT OUTER JOIN
      dbo.xj_CanshuInfo b ON a.ti_leixing = b.id LEFT OUTER JOIN
      dbo.xj_CanshuInfo c ON a.ti_yuyan = c.id LEFT OUTER JOIN
      dbo.xj_CanshuInfo d ON a.ti_pingtai = d.id LEFT OUTER JOIN
      dbo.xj_CanshuInfo e ON a.ti_huanjing = e.id LEFT OUTER JOIN
      dbo.xj_UserInfo f ON a.ti_ui_id = f.id

CREATE TABLE [xj_TeachresInfo] (
    [id] [bigint] IDENTITY (1, 1) NOT NULL ,
    [teachres_info] [bit] NULL ,
    [ti_biaoti] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
    [ti_fubiaoti] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
    [ti_key] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
    [ti_file] [varchar] (8000) COLLATE Chinese_PRC_CI_AS NULL ,
    [ti_filetype] [nvarchar] (7) COLLATE Chinese_PRC_CI_AS NULL ,
    [ti_memo] [text] COLLATE Chinese_PRC_CI_AS NULL ,
    [ti_banquan] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
    [ti_laiyuan] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
    [ti_zuozhe] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
    [ti_timea] [smalldatetime] NULL ,
    [ti_time] [datetime] NULL ,
    [ti_ui_id] [int] NULL ,
    [ti_lanmu] [int] NULL ,
    [ti_leixing] [int] NULL ,
    [ti_yuyan] [int] NULL ,
    [ti_pingtai] [int] NULL ,
    [ti_huanjing] [int] NULL ,
    [ti_path0] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
    [ti_path1] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
    [ti_path2] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
    [ti_xianshi] [bit] NULL ,
    [ti_shenhe] [bit] NULL ,
    [ti_bianji] [int] NULL ,
    [ti_cs_dianji] [bigint] NULL ,
    [ti_cs_xiazai] [bigint] NULL ,
    [ti_byte] [bigint] NULL ,
    [ti_pingjia] [nvarchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
    [ti_pingjiatimes] [int] NOT NULL CONSTRAINT [DF_teachres_info_v_ti_pingjiatimes] DEFAULT (0),
    [ti_uid] [char] (10) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [xj_CanshuInfo] (
    [id] [int] NOT NULL ,
    [canshu_info] [bit] NULL CONSTRAINT [DF_canshu_info_canshu_info] DEFAULT (1),
    [ci_name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
    [ci_cl_id] [int] NULL CONSTRAINT [DF_canshu_info_ci_cl_id] DEFAULT (0),
    [ci_next_ids] [nvarchar] (500) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF_canshu_info_ci_next_id] DEFAULT ('0'),
    [ci_path] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF_canshu_info_ci_path] DEFAULT ('0'),
    [ci_memo] [text] COLLATE Chinese_PRC_CI_AS NULL ,
    CONSTRAINT [PK_canshu_info] PRIMARY KEY  CLUSTERED 
    (
        [id]
    )  ON [PRIMARY] ,
    CONSTRAINT [FK_canshu_info_canshu_list] FOREIGN KEY 
    (
        [ci_cl_id]
    ) REFERENCES [xj_CanshuList] (
        [id]
    ) ON DELETE CASCADE  ON UPDATE CASCADE 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO