表 A 数据如下
id parentID ReadStatus
1 0 0
2 1 1
3 2 1
4 1 1
5 3 1
6 5 0
7 0 0
8 7 1想得到
id parentID total
1 0 4
7 0 1就是计算出parentID=0的子级(包含子级) ReadStatus=1的数量
id parentID ReadStatus
1 0 0
2 1 1
3 2 1
4 1 1
5 3 1
6 5 0
7 0 0
8 7 1想得到
id parentID total
1 0 4
7 0 1就是计算出parentID=0的子级(包含子级) ReadStatus=1的数量
go
--> -->
if not object_id(N'Tempdb..#A') is null
drop table #A
Go
Create table #A([id] int,[parentID] int,[ReadStatus] int)
Insert #A
select 1,0,0 union all
select 2,1,1 union all
select 3,2,1 union all
select 4,1,1 union all
select 5,3,1 union all
select 6,5,0 union all
select 7,0,0 union all
select 8,7,1
Go
;WITH C
AS
(
Select *,ID2=ID from #A WHERE parentID=0
UNION ALL
SELECT a.*,ID2=c.ID2 FROM #A AS a INNER JOIN C ON a.[parentID]=C.ID
)
SELECT ID2 AS ID,
COUNT(*)AS 記錄
FROM C WHERE ReadStatus=1
GROUP BY ID2
/*
ID 記錄
1 4
7 1
*/
AS
(
Select *,ID2=ID from #A WHERE parentID=0
UNION ALL
SELECT a.*,ID2=c.ID2 FROM #A AS a INNER JOIN C ON a.[parentID]=C.ID
)
SELECT ID2 AS ID,
[parentID]=0,--這是傳參
COUNT(*)AS 記錄
FROM C WHERE ReadStatus=1
GROUP BY ID2
/*
ID parentID 記錄
1 0 4
7 0 1
*/
insert into tb select 1,0,0
insert into tb select 2,1,1
insert into tb select 3,2,1
insert into tb select 4,1,1
insert into tb select 5,3,1
insert into tb select 6,5,0
insert into tb select 7,0,0
insert into tb select 8,7,1
go
;with cte as(
select id,id as parentid,0 as l from tb where parentid=0
union all
select b.id,a.parentid,a.l+1 from cte a inner join tb b on a.id=b.parentid
)select parentid,l from cte a where not exists(select 1 from cte where parentid=a.parentid and l>a.l)
/*
*/
parentid l
----------- -----------
7 1
1 4(2 行受影响)
go
drop table tb
GO-- 建立演示环境
CREATE TABLE Dept(
id int PRIMARY KEY,
parent_id int,
name nvarchar(20))
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, 4, N'销售部' UNION ALL
SELECT 6, 4, N'MIS' UNION ALL
SELECT 7, 6, N'UI' UNION ALL
SELECT 8, 6, N'软件开发' UNION ALL
SELECT 9, 8, N'内部开发'
GO-- 查询指定部门下面的所有部门
DECLARE @Dept_name nvarchar(20)
SET @Dept_name = N'MIS'
;WITH
DEPTS AS(
-- 定位点成员
SELECT * FROM Dept
WHERE name = @Dept_name
UNION ALL
-- 递归成员, 通过引用CTE自身与Dept基表JOIN实现递归
SELECT A.*
FROM Dept A, DEPTS B
WHERE A.parent_id = B.id
)
SELECT * FROM DEPTS
GO-- 删除演示环境
DROP TABLE Dept----CTE的综合应用USE tempdb
GO-- 建立演示环境
CREATE TABLE Dept(
id int PRIMARY KEY,
parent_id int,
name nvarchar(20))
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, 4, N'销售部' UNION ALL
SELECT 6, 4, N'MIS' UNION ALL
SELECT 7, 6, N'UI' UNION ALL
SELECT 8, 6, N'软件开发' UNION ALL
SELECT 9, 8, N'内部开发'
GO-- 查询指定部门下面的所有部门, 并汇总各部门的下级部门数
DECLARE @Dept_name nvarchar(20)
SET @Dept_name = N'MIS'
;WITH
DEPTS AS( -- 查询指定部门及其下的所有子部门
-- 定位点成员
SELECT * FROM Dept
WHERE name = @Dept_name
UNION ALL
-- 递归成员, 通过引用CTE自身与Dept基表JOIN实现递归
SELECT A.*
FROM Dept A, DEPTS B
WHERE A.parent_id = B.id
),
DEPTCHILD AS( -- 引用第1个CTE,查询其每条记录对应的部门下的所有子部门
SELECT
Dept_id = P.id, C.id, C.parent_id
FROM DEPTS P, Dept C
WHERE P.id = C.parent_id
UNION ALL
SELECT
P.Dept_id, C.id, C.parent_id
FROM DEPTCHILD P, Dept C
WHERE P.id = C.parent_id
),
DEPTCHILDCNT AS( -- 引用第2个CTE, 汇总得到各部门下的子部门数
SELECT
Dept_id, Cnt = COUNT(*)
FROM DEPTCHILD
GROUP BY Dept_id
)
SELECT -- JOIN第1,3个CTE,得到最终的查询结果
D.*,
ChildDeptCount = ISNULL(DS.Cnt, 0)
FROM DEPTS D
LEFT JOIN DEPTCHILDCNT DS
ON D.id = DS.Dept_id
GO-- 删除演示环境
DROP TABLE Dept本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/fredrickhu/archive/2009/09/19/4569529.aspx
insert into tb select 1,0,0
insert into tb select 2,1,1
insert into tb select 3,2,1
insert into tb select 4,1,1
insert into tb select 5,3,1
insert into tb select 6,5,0
insert into tb select 7,0,0
insert into tb select 8,7,1
go
;with cte as(
select id,id as parentid,0 as l from tb where parentid=0
union all
select b.id,a.parentid,a.l+1 from cte a inner join tb b on a.id=b.parentid
)select parentid,l from cte a where not exists(select 1 from cte where parentid=a.parentid and l>a.l)
/*
*/
parentid l
----------- -----------
7 1
1 4(2 行受影响)
go
drop table tb
结果有点不正确,如果我又加了一列name
表 A 数据如下
id parentID ReadStatus name
1 0 0 a
2 1 1 b
3 2 1 c
4 1 1 d
5 3 1 e
6 5 0 f
7 0 0 g
8 7 1 h
9 0 0 i
想得到
id parentID total name
1 0 4 a
7 0 1 g
8 0 0 i即使total=0 也需要显示出来
insert into tb select 1,0,0,'a'
insert into tb select 2,1,1,'b'
insert into tb select 3,2,1,'c'
insert into tb select 4,1,1,'d'
insert into tb select 5,3,1,'e'
insert into tb select 6,5,0,'f'
insert into tb select 7,0,0,'g'
insert into tb select 8,7,1,'h'
insert into tb select 9,0,0,'i'
go
;with cte as(
select id,id as parentid,parentid as pid,readstatus,name,0 as l from tb where parentid=0
union all
select b.id,a.parentid,a.pid,b.readstatus,b.name,a.l+1 from cte a inner join tb b on a.id=b.parentid
)--select * from cte
select parentid as id,pid as parentid,l as total,name from cte a where not exists(select 1 from cte where parentid=a.parentid and l>a.l)
order by 1
/*
id parentid total name
----------- ----------- ----------- ----------
1 0 4 f
7 0 1 h
9 0 0 i(3 行受影响)*/go
drop table tb
insert into tb select 1,0,0,'a'
insert into tb select 2,1,1,'b'
insert into tb select 3,2,1,'c'
insert into tb select 4,1,1,'d'
insert into tb select 5,3,1,'e'
insert into tb select 6,5,0,'f'
insert into tb select 7,0,0,'g'
insert into tb select 8,7,1,'h'
insert into tb select 9,0,0,'i'
go
;with cte as(
select id,id as parentid,parentid as pid,readstatus,name,0 as l from tb where parentid=0
union all
select b.id,a.parentid,a.pid,b.readstatus,a.name,a.l+1 from cte a inner join tb b on a.id=b.parentid
)--select * from cte
select parentid as id,pid as parentid,l as total,name from cte a where not exists(select 1 from cte where parentid=a.parentid and l>a.l)
order by 1
/*
id parentid total name
----------- ----------- ----------- ----------
1 0 4 a
7 0 1 g
9 0 0 i(3 行受影响)*/go
drop table tb