MS SQL Server树型结构数据显示的SQL语句(纯SQL语句，不用函数)
SELECT dpcode1+dpcode2+dpcode3 as 部门代码,dpname1+dpname2+dpname3 as 部门名称 FROM T_Dpt where dpname1 is not null and dpname2 is not null and dpname3 is not null union all SELECT dpcode1+dpcode2+dpcode3 as 部门代码,dpname1+dpname2 as 部门名称 FROM T_Dpt where dpname1 is not null and dpname2 is not null and dpname3 is null UNION ALL SELECT dpcode1+dpcode2+dpcode3 as 部门代码,dpname1 as 部门名称 FROM T_Dpt where dpname1 is not null and dpname2 is null and dpname3 is null;运行结果：
部门代码 部门名称
01外语系
0102 外语系日语专业
0102001外语系日语专业0331班
0103 外语系商务英语
0103001外语系商务英语0341班
0104 外语系教工
0103002 外语系商务英语0242班
附：
用到的基本表
CREATE TABLE . (
(2) COLLATE Chinese_PRC_CI_AS NOT NULL ,
(2) COLLATE Chinese_PRC_CI_AS NULL ,
(3) COLLATE Chinese_PRC_CI_AS NULL ,
(30) COLLATE Chinese_PRC_CI_AS NULL ,
(30) COLLATE Chinese_PRC_CI_AS NULL ,
(30) COLLATE Chinese_PRC_CI_AS NULL ,
) ON
示例数据:
DpCode1DpCode2DpCode3DpName1DpName2DpName301外语系nullnull0102外语系日语专业null0102001外语系日语专业0331班0103外语系商务英语null0103001外语系商务英语0341班0104外语系教工null0103002外语系商务英语0242班
页:
[1]