posts - 710,  comments - 81,  views - 260万
< 2025年3月 >
23 24 25 26 27 28 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 31 1 2 3 4 5

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);

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

posted on   itprobie-菜鸟程序员  阅读(319)  评论(0编辑  收藏  举报
点击右上角即可分享
微信分享提示