--测试数据 深度排序 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) DECLARE @Level int SET @Level=0 INSERT @t_Level SELECT ID,@Level FROM @t WHERE PID IS NULL WHILE @@ROWCOUNT>0 BEGIN SET @Level=@Level+1 INSERT @t_Level SELECT a.ID,@Level FROM @t a,@t_Level b WHERE a.PID=b.ID AND b.Level=@Level-1 END select * from @t_LevelID Level ---- ----------- 001 0 005 0 002 1 003 1 006 1 004 2 007 2(7 行受影响) 给你个例子
DECLARE @tb3 TABLE(id int ,pid int) INSERT @tb3 SELECT 1,0 UNION ALL SELECT 2,1 UNION ALL SELECT 3,1 UNION ALL SELECT 4,1 UNION ALL SELECT 5,2 UNION ALL SELECT 6,2 UNION ALL SELECT 7,3 declare @id int set @id=6 --节点id号 DECLARE @layer int set @layer=1 --层 while exists (select * from @tb3 where id=@Id and pid<>0) begin select @Id=b.id from @tb3 a, @tb3 b where a.pid=b.id and a.id=@id set @layer=@layer+1 end print @layer
--> 测试数据: [a] if object_id('[a]') is not null drop table [a] create table [a] (c1 varchar(1),c2 varchar(1)) insert into [a] select 'a','b' union all select 'a','c' union all select 'b','d' union all select 'c','e' union all select 'd','f' union all select 'e','i' union all select 'd','k' union all select 'c','n' gocreate function wsp() returns @t table (c1 varchar(50),c2 varchar(50),level int) as begin --declare @t table(c1 varchar(50),pid varchar(50),level int) declare @level int set @level=1 insert into @t select null,c1,@level from a t where not exists(select 1 from a where c2=t.c1) while(@@rowcount>0) begin set @level=@Level+1 insert into @t select a.*,@level from a,@t b where a.c1=b.c2 and b.level=@level-1 end return end go select distinct c2,level from dbo.wsp()
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)
DECLARE @Level int
SET @Level=0
INSERT @t_Level SELECT ID,@Level
FROM @t
WHERE PID IS NULL
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,@Level
FROM @t a,@t_Level b
WHERE a.PID=b.ID
AND b.Level=@Level-1
END
select * from @t_LevelID Level
---- -----------
001 0
005 0
002 1
003 1
006 1
004 2
007 2(7 行受影响)
给你个例子
INSERT @tb3 SELECT 1,0
UNION ALL SELECT 2,1
UNION ALL SELECT 3,1
UNION ALL SELECT 4,1
UNION ALL SELECT 5,2
UNION ALL SELECT 6,2
UNION ALL SELECT 7,3
declare @id int
set @id=6 --节点id号
DECLARE @layer int
set @layer=1 --层
while exists (select * from @tb3 where id=@Id and pid<>0)
begin
select @Id=b.id from @tb3 a, @tb3 b where a.pid=b.id and a.id=@id
set @layer=@layer+1
end
print @layer
if object_id('[a]') is not null drop table [a]
create table [a] (c1 varchar(1),c2 varchar(1))
insert into [a]
select 'a','b' union all
select 'a','c' union all
select 'b','d' union all
select 'c','e' union all
select 'd','f' union all
select 'e','i' union all
select 'd','k' union all
select 'c','n'
gocreate function wsp()
returns @t table (c1 varchar(50),c2 varchar(50),level int)
as
begin
--declare @t table(c1 varchar(50),pid varchar(50),level int)
declare @level int
set @level=1
insert into @t select null,c1,@level from a t where not exists(select 1 from a where c2=t.c1)
while(@@rowcount>0)
begin
set @level=@Level+1
insert into @t select a.*,@level from a,@t b
where a.c1=b.c2 and b.level=@level-1
end
return
end
go
select distinct c2,level from dbo.wsp()