CTE 中字符串拼接

1>cte语法

1.1>基础语句和递归语句中必须有字段

1.2>最后只能跟一条查询语句

1.3>字符串拼接，要将拼接的字段转成字符串类型,cast(fieldName as nvarchar(max))

?

1 2 3 4 5 6 7 8 9 10

with cte[(field1,field2,...)] as (    --基础语句，必须有字段    select field1,field2,... from tableName    union all    --递归语句，必须有字段    select field1,field2,... from tableName a,cte b where a.fieldName=b.fieldName ) select * from cte;--只能跟一条查询语句

　　

2>cte 实例,添加一些拼接的字符串

?

1 2 3 4 5 6 7 8

with dept as (    select DEPTNAME as DEPTNAME,DEPTID,PDEPTID,cast(DEPTNAME as nvarchar(max)) as name from DEPARTMENT where DEPARTMENT.DEPTNAME='信息中心'    union all    select a.DEPTNAME,a.DEPTID,a.PDEPTID,cast(a.DEPTNAME+'\'+b.name as nvarchar(max)) as name from DEPARTMENT a  inner join dept b on a.DEPTID=b.PDEPTID ) select * from dept where DEPTNAME='信息中心';

返回路径

?

1 2 3 4 5 6 7 8

with dept as (    select DEPTNAME as DEPTNAME,DEPTID,PDEPTID,cast(DEPTNAME as nvarchar(max)) as namepath,0 as pathlevel from DEPARTMENT where DEPARTMENT.DEPTID='121'    union all    select a.DEPTNAME,a.DEPTID,a.PDEPTID,cast(a.DEPTNAME+'\'+b.namepath as nvarchar(max)) as namepath,b.pathlevel+1 as pathlevel from DEPARTMENT a  inner join dept b on a.DEPTID=b.PDEPTID ) select namepath from dept where pathlevel=(select max(pathlevel) from dept);

　　程序员的基础教程：菜鸟程序员