CET公用代码实现递归等

 

WITH
TBS AS (
 SELECT object_id,OBJECT_NAME = name
 FROM sys.tables
),
COL AS (
 SELECT OBJECT_ID,column_name = name
 From sys.columns
)
select TBS.Object_id,TBS.object_name,COL.column_name
from TBS,COL
where TBS.object_id = col.object_id
order by TBS.object_id,col.column_name
option (maxrecursion 32767);

GO


Create table t1(
ID int
)
insert t1
select 1 union all
select 2

Create table t2(
 id int
)

---CTE

;with 
T2 as
(
 select * from t1
)

select * from T2

use tempdb
go

Create table Dept(
id int primary key,
parent_id int,
name_nvarchar nvarchar(100)
)

Insert Dept
select 0,0,N'全部'union all 
select 1,0,N'财政部' union all
select 2,0,N'行政部' union all
select 3,0,N'业务部' union all
select 4,0,N'销售部' union all
select 5,0,N'MIS' union all
select 6,0,N'UI'

update Dept set parent_id = 7 where id in(8)


Insert Dept select 7,5,N'软件研发部'union all 
select 8,6,'软件开发' union all select 9,8,'内部开发'

select * from Dept

declare @dept_name nvarchar(20)
set @dept_name = 'MIS';
with depts as
(
 --定位点成员
 select * from Dept 
 where name_nvarchar = @dept_name
 union all 
 --递归成员,通过引用CTE自身与Dept表Join实现递归
 select A.*
 from dept as A,depts as B
 where A.parent_id = B.id
)

select * from depts
go

-----------------------------


declare @dept_name nvarchar(20)
set @dept_name = 'MIS';
with depts as
(
 --定位点成员
 select * from Dept 
 where name_nvarchar = @dept_name
 union all 
 --递归成员,通过引用CTE自身与Dept表Join实现递归
 select A.*
 from dept as A,depts as B
 where A.parent_id = B.id
),Deptchild as 
(
 select Dept_id = p.id,c.id,c.parent_id 
 from depts as p,Dept as c
 where p.id = c.parent_id
 union all
 select p.Dept_id,c.id,c.parent_id
 from Deptchild as p,Dept as c
 where p.id = c.parent_id
),
DeptChildCNT as
(
 select Dept_id,CNT = COUNT(1)
 from Deptchild
 group by Dept_id
)
select D.*,DS.CNT
from depts as D
 left join DeptChildCNT as DS
 on d.id = DS.Dept_id


----------------------
5 4 MIS 3
7 5 软件研发部 2
8 7 软件开发 1
9 8 内部开发 NULL

--------------------

 

declare @dept_name nvarchar(20)
set @dept_name = 'MIS';
with depts as
(
 --定位点成员
 select id,parent_id,name_nvarchar as names from Dept 
 where name_nvarchar = @dept_name
 union all 
 --递归成员,通过引用CTE自身与Dept表Join实现递归
 select A.id,A.parent_id,cast(B.names+'_'+A.name_nvarchar as nvarchar(100)) as name
 from dept as A,depts as B
 where A.parent_id = B.id
)
select * from depts

------------------------------
5 4 MIS
7 5 MIS_软件研发部
8 7 MIS_软件研发部_软件开发
9 8 MIS_软件研发部_软件开发_内部开发
------------------------------