create table t(ID varchar(10), ParentID varchar(10) ,Node varchar(100))
insert t
select '1001' ,null ,'The World'
union all select '1002' ,'1001' ,'Americas '
union all select '1003' ,'1002' ,'United States '
union all select '1004' ,'1003' ,'California '
union all select '1005' ,'1004' ,'Sonoma Valley '
union all select '1006' ,'1001' ,'Europe '
union all select '1007' ,'1006' ,'Bulgaria '
union all select '1008' ,'1006' ,'France '
union all select '1009' ,'1008' ,'Alsace '
union all select '1010' ,'1008' ,'Bordeaux '
union all select '1011' ,'1010' ,'Graves '
union all select '1012' ,'1010' ,'Medoc '
union all select '1013' ,'1012' ,'Bas-Medoc '
union all select '1014' ,'1013' ,'Listrac '
union all select '1015' ,'1013' ,'Pauillac '
union all select '1016' ,'1013' ,'Saint-Estephe '
union all select '1017' ,'1013' ,'Saint-Julien '
union all select '1018' ,'1012' ,'Haut-Medoc '
union all select '1019' ,'1018' ,'Margaux '
union all select '1020' ,'1018' ,'Moulis '
union all select '1021' ,'1006' ,'Germany '
union all select '1022' ,'1021' ,'Rheingau '
union all select '1023' ,'1001' ,'Oceania '
union all select '1024' ,'1023' ,'Australia '
union all select '1025' ,'1024' ,'South Australia '
union all select '1026' ,'1025' ,'Barossa Valley'
union all select '1026' ,'1025' ,'Barossa Valley 'go--深度排序显示处理
--生成每个节点的编码累计(相同当单编号法的编码)
DECLARE @t_Level TABLE(ID varchar(10),Level int,Sort varchar(8000))
DECLARE @Level int
SET @Level=0
INSERT @t_Level SELECT ID,@Level,Node
FROM t
WHERE ParentID IS NULL
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,@Level,b.Sort+' | '+a.Node
FROM t a,@t_Level b
WHERE a.ParentID=b.ID
AND b.Level=@Level-1
END
Select * From @t_Level Ooder By Sort
Drop Table t/* 结果
1001 0 The World
1002 1 The World | Americas
1003 2 The World | Americas | United States
1004 3 The World | Americas | United States | California
1005 4 The World | Americas | United States | California | Sonoma Valley
1006 1 The World | Europe
1007 2 The World | Europe | Bulgaria
1008 2 The World | Europe | France
1009 3 The World | Europe | France | Alsace
1010 3 The World | Europe | France | Bordeaux
1011 4 The World | Europe | France | Bordeaux | Graves
1012 4 The World | Europe | France | Bordeaux | Medoc
1013 5 The World | Europe | France | Bordeaux | Medoc | Bas-Medoc
1014 6 The World | Europe | France | Bordeaux | Medoc | Bas-Medoc | Listrac
1015 6 The World | Europe | France | Bordeaux | Medoc | Bas-Medoc | Pauillac
1016 6 The World | Europe | France | Bordeaux | Medoc | Bas-Medoc | Saint-Estephe
1017 6 The World | Europe | France | Bordeaux | Medoc | Bas-Medoc | Saint-Julien
1018 5 The World | Europe | France | Bordeaux | Medoc | Haut-Medoc
1019 6 The World | Europe | France | Bordeaux | Medoc | Haut-Medoc | Margaux
1020 6 The World | Europe | France | Bordeaux | Medoc | Haut-Medoc | Moulis
1021 2 The World | Europe | Germany
1022 3 The World | Europe | Germany | Rheingau
1023 1 The World | Oceania
1024 2 The World | Oceania | Australia
1025 3 The World | Oceania | Australia | South Australia
1026 4 The World | Oceania | Australia | South Australia | Barossa Valley
1026 4 The World | Oceania | Australia | South Australia | Barossa Valley */
insert t
select '1001' ,null ,'The World'
union all select '1002' ,'1001' ,'Americas '
union all select '1003' ,'1002' ,'United States '
union all select '1004' ,'1003' ,'California '
union all select '1005' ,'1004' ,'Sonoma Valley '
union all select '1006' ,'1001' ,'Europe '
union all select '1007' ,'1006' ,'Bulgaria '
union all select '1008' ,'1006' ,'France '
union all select '1009' ,'1008' ,'Alsace '
union all select '1010' ,'1008' ,'Bordeaux '
union all select '1011' ,'1010' ,'Graves '
union all select '1012' ,'1010' ,'Medoc '
union all select '1013' ,'1012' ,'Bas-Medoc '
union all select '1014' ,'1013' ,'Listrac '
union all select '1015' ,'1013' ,'Pauillac '
union all select '1016' ,'1013' ,'Saint-Estephe '
union all select '1017' ,'1013' ,'Saint-Julien '
union all select '1018' ,'1012' ,'Haut-Medoc '
union all select '1019' ,'1018' ,'Margaux '
union all select '1020' ,'1018' ,'Moulis '
union all select '1021' ,'1006' ,'Germany '
union all select '1022' ,'1021' ,'Rheingau '
union all select '1023' ,'1001' ,'Oceania '
union all select '1024' ,'1023' ,'Australia '
union all select '1025' ,'1024' ,'South Australia '
union all select '1026' ,'1025' ,'Barossa Valley'
union all select '1026' ,'1025' ,'Barossa Valley 'go--深度排序显示处理
--生成每个节点的编码累计(相同当单编号法的编码)
DECLARE @t_Level TABLE(ID varchar(10),Level int,Sort varchar(8000))
DECLARE @Level int
SET @Level=0
INSERT @t_Level SELECT ID,@Level,Node
FROM t
WHERE ParentID IS NULL
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,@Level,b.Sort+' | '+a.Node
FROM t a,@t_Level b
WHERE a.ParentID=b.ID
AND b.Level=@Level-1
END
Select * From @t_Level Ooder By Sort
Drop Table t/* 结果
1001 0 The World
1002 1 The World | Americas
1003 2 The World | Americas | United States
1004 3 The World | Americas | United States | California
1005 4 The World | Americas | United States | California | Sonoma Valley
1006 1 The World | Europe
1007 2 The World | Europe | Bulgaria
1008 2 The World | Europe | France
1009 3 The World | Europe | France | Alsace
1010 3 The World | Europe | France | Bordeaux
1011 4 The World | Europe | France | Bordeaux | Graves
1012 4 The World | Europe | France | Bordeaux | Medoc
1013 5 The World | Europe | France | Bordeaux | Medoc | Bas-Medoc
1014 6 The World | Europe | France | Bordeaux | Medoc | Bas-Medoc | Listrac
1015 6 The World | Europe | France | Bordeaux | Medoc | Bas-Medoc | Pauillac
1016 6 The World | Europe | France | Bordeaux | Medoc | Bas-Medoc | Saint-Estephe
1017 6 The World | Europe | France | Bordeaux | Medoc | Bas-Medoc | Saint-Julien
1018 5 The World | Europe | France | Bordeaux | Medoc | Haut-Medoc
1019 6 The World | Europe | France | Bordeaux | Medoc | Haut-Medoc | Margaux
1020 6 The World | Europe | France | Bordeaux | Medoc | Haut-Medoc | Moulis
1021 2 The World | Europe | Germany
1022 3 The World | Europe | Germany | Rheingau
1023 1 The World | Oceania
1024 2 The World | Oceania | Australia
1025 3 The World | Oceania | Australia | South Australia
1026 4 The World | Oceania | Australia | South Australia | Barossa Valley
1026 4 The World | Oceania | Australia | South Australia | Barossa Valley */
insert t
select '1001' ,null ,'The World'
union all select '1002' ,'1001' ,'Americas '
union all select '1003' ,'1002' ,'United States '
union all select '1004' ,'1003' ,'California '
union all select '1005' ,'1004' ,'Sonoma Valley '
union all select '1006' ,'1001' ,'Europe '
union all select '1007' ,'1006' ,'Bulgaria '
union all select '1008' ,'1006' ,'France '
union all select '1009' ,'1008' ,'Alsace '
union all select '1010' ,'1008' ,'Bordeaux '
union all select '1011' ,'1010' ,'Graves '
union all select '1012' ,'1010' ,'Medoc '
union all select '1013' ,'1012' ,'Bas-Medoc '
union all select '1014' ,'1013' ,'Listrac '
union all select '1015' ,'1013' ,'Pauillac '
union all select '1016' ,'1013' ,'Saint-Estephe '
union all select '1017' ,'1013' ,'Saint-Julien '
union all select '1018' ,'1012' ,'Haut-Medoc '
union all select '1019' ,'1018' ,'Margaux '
union all select '1020' ,'1018' ,'Moulis '
union all select '1021' ,'1006' ,'Germany '
union all select '1022' ,'1021' ,'Rheingau '
union all select '1023' ,'1001' ,'Oceania '
union all select '1024' ,'1023' ,'Australia '
union all select '1025' ,'1024' ,'South Australia '
union all select '1026' ,'1025' ,'Barossa Valley'
union all select '1026' ,'1025' ,'Barossa Valley 'go
DECLARE @t_Level TABLE(ID varchar(10),Level int,Sort varchar(8000))
DECLARE @Level intSET @Level=0INSERT @t_Level
SELECT ID,@Level,id
FROM t
WHERE ParentID IS NULLWHILE @@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.ParentID=b.ID
AND b.Level=@Level-1
ENDSelect id,
p1=(select top 1 node from t where substring(b.Sort,1,4)=id),
p2=(select top 1 node from t where substring(b.Sort,5,4)=id),
p3=(select top 1 node from t where substring(b.Sort,9,4)=id),
p4=(select top 1 node from t where substring(b.Sort,13,4)=id),
p5=(select top 1 node from t where substring(b.Sort,17,4)=id),
p6=(select top 1 node from t where substring(b.Sort,21,4)=id),
p7=(select top 1 node from t where substring(b.Sort,25,4)=id),
p8=(select top 1 node from t where substring(b.Sort,29,4)=id)
From @t_Level b
Drop Table t/* 结果
1001 The World NULL NULL NULL NULL NULL NULL NULL
1002 The World Americas NULL NULL NULL NULL NULL NULL
1006 The World Europe NULL NULL NULL NULL NULL NULL
1023 The World Oceania NULL NULL NULL NULL NULL NULL
1003 The World Americas United States NULL NULL NULL NULL NULL
1021 The World Europe Germany NULL NULL NULL NULL NULL
1007 The World Europe Bulgaria NULL NULL NULL NULL NULL
1008 The World Europe France NULL NULL NULL NULL NULL
1024 The World Oceania Australia NULL NULL NULL NULL NULL
1004 The World Americas United States California NULL NULL NULL NULL
1009 The World Europe France Alsace NULL NULL NULL NULL
1010 The World Europe France Bordeaux NULL NULL NULL NULL
1022 The World Europe Germany Rheingau NULL NULL NULL NULL
1025 The World Oceania Australia South Australia NULL NULL NULL NULL
1005 The World Americas United States California Sonoma Valley NULL NULL NULL
1011 The World Europe France Bordeaux Graves NULL NULL NULL
1012 The World Europe France Bordeaux Medoc NULL NULL NULL
1026 The World Oceania Australia South Australia Barossa Valley NULL NULL NULL
1026 The World Oceania Australia South Australia Barossa Valley NULL NULL NULL
1013 The World Europe France Bordeaux Medoc Bas-Medoc NULL NULL
1018 The World Europe France Bordeaux Medoc Haut-Medoc NULL NULL
1014 The World Europe France Bordeaux Medoc Bas-Medoc Listrac NULL
1015 The World Europe France Bordeaux Medoc Bas-Medoc Pauillac NULL
1016 The World Europe France Bordeaux Medoc Bas-Medoc Saint-Estephe NULL
1017 The World Europe France Bordeaux Medoc Bas-Medoc Saint-Julien NULL
1019 The World Europe France Bordeaux Medoc Haut-Medoc Margaux NULL
1020 The World Europe France Bordeaux Medoc Haut-Medoc Moulis NULL
*/
insert t
select '1001' ,null ,'The World'
union all select '1002' ,'1001' ,'Americas '
union all select '1003' ,'1002' ,'United States '
union all select '1004' ,'1003' ,'California '
union all select '1005' ,'1004' ,'Sonoma Valley '
union all select '1006' ,'1001' ,'Europe '
union all select '1007' ,'1006' ,'Bulgaria '
union all select '1008' ,'1006' ,'France '
union all select '1009' ,'1008' ,'Alsace '
union all select '1010' ,'1008' ,'Bordeaux '
union all select '1011' ,'1010' ,'Graves '
union all select '1012' ,'1010' ,'Medoc '
union all select '1013' ,'1012' ,'Bas-Medoc '
union all select '1014' ,'1013' ,'Listrac '
union all select '1015' ,'1013' ,'Pauillac '
union all select '1016' ,'1013' ,'Saint-Estephe '
union all select '1017' ,'1013' ,'Saint-Julien '
union all select '1018' ,'1012' ,'Haut-Medoc '
union all select '1019' ,'1018' ,'Margaux '
union all select '1020' ,'1018' ,'Moulis '
union all select '1021' ,'1006' ,'Germany '
union all select '1022' ,'1021' ,'Rheingau '
union all select '1023' ,'1001' ,'Oceania '
union all select '1024' ,'1023' ,'Australia '
union all select '1025' ,'1024' ,'South Australia '
union all select '1026' ,'1025' ,'Barossa Valley'
union all select '1026' ,'1025' ,'Barossa Valley 'go
DECLARE @t_Level TABLE(ID varchar(10),Level int,Sort varchar(8000))
DECLARE @Level intSET @Level=0INSERT @t_Level
SELECT ID,@Level,id
FROM t
WHERE ParentID IS NULLWHILE @@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.ParentID=b.ID
AND b.Level=@Level-1
ENDSelect id,
p1=(select top 1 node from t where substring(b.Sort,1,4)=id),
p2=(select top 1 node from t where substring(b.Sort,5,4)=id),
p3=(select top 1 node from t where substring(b.Sort,9,4)=id),
p4=(select top 1 node from t where substring(b.Sort,13,4)=id),
p5=(select top 1 node from t where substring(b.Sort,17,4)=id),
p6=(select top 1 node from t where substring(b.Sort,21,4)=id),
p7=(select top 1 node from t where substring(b.Sort,25,4)=id),
p8=(select top 1 node from t where substring(b.Sort,29,4)=id)
From @t_Level b
Drop Table t/* 结果
1001 The World NULL NULL NULL NULL NULL NULL NULL
1002 The World Americas NULL NULL NULL NULL NULL NULL
1006 The World Europe NULL NULL NULL NULL NULL NULL
1023 The World Oceania NULL NULL NULL NULL NULL NULL
1003 The World Americas United States NULL NULL NULL NULL NULL
1021 The World Europe Germany NULL NULL NULL NULL NULL
1007 The World Europe Bulgaria NULL NULL NULL NULL NULL
1008 The World Europe France NULL NULL NULL NULL NULL
1024 The World Oceania Australia NULL NULL NULL NULL NULL
1004 The World Americas United States California NULL NULL NULL NULL
1009 The World Europe France Alsace NULL NULL NULL NULL
1010 The World Europe France Bordeaux NULL NULL NULL NULL
1022 The World Europe Germany Rheingau NULL NULL NULL NULL
1025 The World Oceania Australia South Australia NULL NULL NULL NULL
1005 The World Americas United States California Sonoma Valley NULL NULL NULL
1011 The World Europe France Bordeaux Graves NULL NULL NULL
1012 The World Europe France Bordeaux Medoc NULL NULL NULL
1026 The World Oceania Australia South Australia Barossa Valley NULL NULL NULL
1026 The World Oceania Australia South Australia Barossa Valley NULL NULL NULL
1013 The World Europe France Bordeaux Medoc Bas-Medoc NULL NULL
1018 The World Europe France Bordeaux Medoc Haut-Medoc NULL NULL
1014 The World Europe France Bordeaux Medoc Bas-Medoc Listrac NULL
1015 The World Europe France Bordeaux Medoc Bas-Medoc Pauillac NULL
1016 The World Europe France Bordeaux Medoc Bas-Medoc Saint-Estephe NULL
1017 The World Europe France Bordeaux Medoc Bas-Medoc Saint-Julien NULL
1019 The World Europe France Bordeaux Medoc Haut-Medoc Margaux NULL
1020 The World Europe France Bordeaux Medoc Haut-Medoc Moulis NULL
*/