27,579
社区成员
发帖
与我相关
我的任务
分享
if OBJECT_ID('tempdb..#t_class') is not null
drop table #t_class
create table #t_class(cid int, cname varchar(10), p_cid int)
insert into #t_class
select 1, 'Class1', null union all
select 2, 'Class2', 1 union all
select 3, 'Class3', 1 union all
select 4, 'Class4', 2 union all
select 5, 'Class5', 2 union all
select 6, 'Class6', 3 union all
select 7, 'Class7', 3 union all
select 8, 'Class8', 5
;
With cte as
(
select cid,cname,p_cid,0 lvl, cast('/'+cname as nvarchar) pth from #t_class
where p_cid is null
union all
select e.cid,e.cname,e.p_cid,c.lvl+1, cast(c.pth + '/' + e.cname as nvarchar) pth from cte c
inner join #t_class e
on c.cid = e.p_cid
)
select * from cte
where pth like '%Class2%' --找出class2下的班级
--参考查询指定父节点下的所有子节点:
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
)
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
/*
标题:获取某个节点所有的最底层节点
作者:爱新觉罗·毓华(十八年风雨,守得冰山雪莲花开)
时间:2009-07-16
地点:新疆乌鲁木齐
*/
/*
有一个表名为:tb
结构如下:
id --int
parentid --int 上层的父节点
sname --varchar(50)
如有以下数据:
id parentid sname
1 -1 根节点
2 1 节点1
3 1 节点2
4 2 节点3
5 2 节点4
6 3 节点5
7 3 节点6
7 6 节点7
假如:我要得到某个节点下所有的最底层的节点数据怎么实现:
如:我要得到id=3 的所有最底层的数据是:
id parentid sname
7 3 节点6
7 6 节点7
请大家多多帮忙,谢谢!
*/
create table tb(id int, parentid int, sname varchar(10))
insert into tb values(1 , -1 , '根节点')
insert into tb values(2 , 1 , '节点1')
insert into tb values(3 , 1 , '节点2')
insert into tb values(4 , 2 , '节点3')
insert into tb values(5 , 2 , '节点4')
insert into tb values(6 , 3 , '节点5')
insert into tb values(7 , 3 , '节点6')
insert into tb values(7 , 6 , '节点7')
go
--查询指定节点及其所有子节点的函数
CREATE FUNCTION f_Cid(@ID int) RETURNS @t_Level TABLE(ID int,Level int)
AS
BEGIN
DECLARE @Level int
SET @Level=1
INSERT @t_Level SELECT @ID,@Level
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,@Level
FROM tb a,@t_Level b
WHERE a.parentid=b.ID
AND b.Level=@Level-1
END
RETURN
END
GO
SELECT distinct a.* FROM tb a,f_Cid(3) b WHERE a.ID=b.ID and a.id not in (select parentid from tb)
drop table tb
drop function dbo.f_cid
/*
id parentid sname
----------- ----------- ----------
7 3 节点6
7 6 节点7
(所影响的行数为 2 行)
*/
--2005
create table tb(id int, parentid int, sname nvarchar(10))
insert into tb values(1 , -1 , N'根节点')
insert into tb values(2 , 1 , N'节点1')
insert into tb values(3 , 1 , N'节点2')
insert into tb values(4 , 2 , N'节点3')
insert into tb values(5 , 2 , N'节点4')
insert into tb values(6 , 3 , N'节点5')
insert into tb values(7 , 3 , N'节点6')
insert into tb values(7 , 6 , N'节点7')
go
;WITH
TREE AS(
SELECT * FROM TB
WHERE parentid = 3 -- 要查询的父 id
UNION ALL
SELECT TB.* FROM TB, TREE
WHERE TB.parentid = TREE.id
)
SELECT * FROM TREE where id not in (select parentid from tb)
drop table tb
if OBJECT_ID('tempdb..#t_class') is not null
drop table #t_class
create table #t_class(cid int, cname varchar(10), p_cid int)
insert into #t_class
select 1, 'Class1', null union all
select 2, 'Class2', 1 union all
select 3, 'Class3', 1 union all
select 4, 'Class4', 2 union all
select 5, 'Class5', 2 union all
select 6, 'Class6', 3 union all
select 7, 'Class7', 3 union all
select 8, 'Class8', 5
;
With cte as
(
select cid,cname,p_cid,0 lvl, cast('/'+cname as nvarchar) pth from #t_class
where p_cid is null
union all
select e.cid,e.cname,e.p_cid,c.lvl+1, cast(c.pth + '/' + e.cname as nvarchar) pth from cte c
inner join #t_class e
on c.cid = e.p_cid
)
select * from cte
where pth like '%Class2%' --找出class2下的班级
and not exists(select 1 from #t_class
where #t_class.p_cid = cte.cid) --去掉中间节点