27,580
社区成员
发帖
与我相关
我的任务
分享
DECLARE @TB TABLE (INFO1 VARCHAR(10),INFO2 VARCHAR(10))
INSERT INTO @TB SELECT '中国','北京'
UNION ALL SELECT '亚洲','上海'
UNION ALL SELECT '亚洲','中国'
UNION ALL SELECT '亚洲','天津'
UNION ALL SELECT '宇宙','地球'
UNION ALL SELECT '地球','亚洲'
UNION ALL SELECT '地球','欧洲'
SELECT A.INFO1,A.INFO2,INFO3=B.INFO2,INFO4=C.INFO2,INFO5=D.INFO2 INTO #T FROM @TB A
LEFT JOIN @TB B
ON A.INFO2=B.INFO1
LEFT JOIN @TB C
ON B.INFO2=C.INFO1
LEFT JOIN @TB D
ON C.INFO2=D.INFO1
SELECT * FROM #T A
WHERE NOT EXISTS(SELECT 1 FROM #T
WHERE (INFO2=A.INFO1 OR INFO3=A.INFO1 OR INFO4=A.INFO1 OR INFO5=A.INFO1) AND INFO1<>A.INFO1 )
/*
INFO1 INFO2 INFO3 INFO4 INFO5
---------- ---------- ---------- ---------- ----------
宇宙 地球 亚洲 上海 NULL
宇宙 地球 亚洲 中国 北京
宇宙 地球 亚洲 天津 NULL
宇宙 地球 欧洲 NULL NULL
(所影响的行数为 4 行)
*/
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,id
FROM t
WHERE ParentID 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.ParentID=b.ID
AND b.Level=@Level-1
END
Select 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
select a.信息1,a.信息2,b.信息2 as 信息3 from table a,table b where a.信息2=b.信息1
union all
select * ,null 信息3 from table where 信息1 not in
(select a.信息2 as 信息3 from table a,table b where a.信息2=b.信息1)
and 信息2 not in
(select a.信息2 as 信息3 from table a,table b where a.信息2=b.信息1)