22,210
社区成员
发帖
与我相关
我的任务
分享
引用:
--测试数据 深度排序
DECLARE @t TABLE(ID char(3),PID char(3),Name nvarchar(10))
INSERT @t SELECT '001',NULL ,'山东省'
UNION ALL SELECT '002','001','烟台市'
UNION ALL SELECT '004','002','招远市'
UNION ALL SELECT '003','001','青岛市'
UNION ALL SELECT '005',NULL ,'四会市'
UNION ALL SELECT '006','005','清远市'
UNION ALL SELECT '007','006','小分市'
--深度排序显示处理
--生成每个节点的编码累计(相同当单编号法的编码)
DECLARE @t_Level TABLE(ID char(3),Level int,Sort varchar(8000))
DECLARE @Level int
SET @Level=0
INSERT @t_Level SELECT ID,@Level,ID
FROM @t
WHERE PID IS NULL
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,@Level,b.Sort+a.ID
FROM @t a,@t_Level b
WHERE a.PID=b.ID
AND b.Level=@Level-1
END
--显示结果
SELECT a.*
FROM @t a,@t_Level b
WHERE a.ID=b.ID
ORDER BY b.Sort
/*--结果
ID PID Name
------ --------- ----------
001 NULL 山东省
002 001 烟台市
004 002 招远市
003 001 青岛市
005 NULL 四会市
006 005 清远市
007 006 小分市
--*/
--查询指定节点及其所有子节点的函数
CREATE FUNCTION f_Cid(@ID char(3))
RETURNS @t_Level TABLE(ID char(3),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.PID=b.ID
AND b.Level=@Level-1
END
RETURN
END
GO
--调用函数查询002及其所有子节点
SELECT a.*
FROM tb a,f_Cid('002') b
WHERE a.ID=b.ID
/*--结果
ID PID Name
------ ------- ----------
002 001 烟台市
004 002 招远市
--*/
--测试数据
DECLARE @t TABLE(ID char(3),PID char(3),Name nvarchar(10))
INSERT @t SELECT '001',NULL ,'山东省'
UNION ALL SELECT '002','001','烟台市'
UNION ALL SELECT '004','002','招远市'
UNION ALL SELECT '003','001','青岛市'
UNION ALL SELECT '005',NULL ,'四会市'
UNION ALL SELECT '006','005','清远市'
UNION ALL SELECT '007','006','小分市'
--深度排序显示处理
--生成每个节点的编码累计(相同当单编号法的编码)
DECLARE @t_Level TABLE(ID char(3),Level int,Sort varchar(8000))
DECLARE @Level int
SET @Level=0
INSERT @t_Level SELECT ID,@Level,ID
FROM @t
WHERE PID IS NULL
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,@Level,b.Sort+a.ID
FROM @t a,@t_Level b
WHERE a.PID=b.ID
AND b.Level=@Level-1
END
--显示结果
SELECT SPACE(b.Level*2)+'|--'+a.Name
FROM @t a,@t_Level b
WHERE a.ID=b.ID
ORDER BY b.Sort
/*--结果
|--山东省
|--烟台市
|--招远市
|--青岛市
|--四会市
|--清远市
|--小分市
--*/
create table folder
(id int,pid int,rowname nvarchar(40))
insert into folder
select 1,0,'山东'
union all
select 2,1,'青岛'
union all
select 3,1,'大连'
union all
select 4,0,'广东'
union all
select 5,4,'广州'
union all
select 6,3,'大连公司'
union all
select 7,6,'部门'
union all
select 8,7,'小李'
with cte(id,pid,rowname,leaves)
as
(
select a.id,a.pid,a.rowname,1 as leaves from folder a where a.id = 8
union all
select b.id,b.pid,b.rowname,cte.leaves +1 from folder b inner join cte on b.id = cte.pid
)
select * from cte where leaves>1
id pid rowname leaves
----------- ----------- ---------------------------------------- -----------
7 6 部门 2
6 3 大连公司 3
3 1 大连 4
1 0 山东 5
(4 row(s) affected)
create table folder
(id int,pid int,rowname nvarchar(40))
insert into folder
select 1,0,'山东'
union all
select 2,1,'青岛'
union all
select 3,1,'大连'
union all
select 4,0,'广东'
union all
select 5,4,'广州'
union all
select 6,3,'大连公司'
union all
select 7,6,'部门'
union all
select 8,7,'小李'
with cte(id,pid,rowname,leaves)
as
(
select a.id,a.pid,a.rowname,1 as leaves from folder a where a.id = 8
union all
select b.id,b.pid,b.rowname,cte.leaves +1 from folder b inner join cte on b.id = cte.pid
)
select * from cte where leaves>1
id pid rowname leaves
----------- ----------- ---------------------------------------- -----------
7 6 部门 2
6 3 大连公司 3
3 1 大连 4
1 0 山东 5
(4 row(s) affected)