if not object_id('Tempdb..#T') is null drop table #T Go Create table #T([站点1] nvarchar(1),[站点2] nvarchar(1)) Insert #T select N'A',N'B' union all select N'B',N'C' union all select N'C',N'D' union all select N'B',N'E' union all select N'E',N'D' Go ;with b as ( Select *,[站点1]+'-'+[站点2] as stop,Path=cast([站点1]+'-'+[站点2] as nvarchar(1000))from #T as a where not exists(select 1 from #T where a.[站点2]=[站点1]) union all select a.*,b.stop,Path=cast(a.[站点1]+'-'+b.Path as nvarchar(1000)) from #T as a inner join b on a.[站点2]=b.站点1 ) select a.Path from b as a where not exists(select 1 from b where Path<a.Path and stop =a.stop) /* A-B-E-D A-B-C-D */
--参考一下实例 --> 生成测试数据表:tb IF NOT OBJECT_ID('[tb]') IS NULL DROP TABLE [tb] GO CREATE TABLE [tb](GUID INT IDENTITY,[col1] NVARCHAR(10),[col2] NVARCHAR(20)) INSERT [tb] SELECT N'A','01' UNION ALL SELECT N'B','01.01' UNION ALL SELECT N'C','01.01.01' UNION ALL SELECT N'F','01.01.01.01' UNION ALL SELECT N'E','01.01.01.02' UNION ALL SELECT N'D','01.01.01.03' UNION ALL SELECT N'O','02' UNION ALL SELECT N'P','02.01' UNION ALL SELECT N'Q','02.01.01' GO --SELECT * FROM [tb]-->SQL查询如下:---另一种方法 ;WITH T AS ( SELECT *,PATH=CAST([COL1] AS VARCHAR(1000)) FROM TB A WHERE NOT EXISTS( SELECT 1 FROM TB WHERE A.COL2 LIKE COL2+'%' AND LEN(A.COL2)>LEN(COL2)) UNION ALL SELECT A.*,CAST(PATH+'-->'+A.COL1 AS VARCHAR(1000)) FROM TB A JOIN T B ON A.COL2 LIKE B.COL2+'%' AND LEN(A.COL2)-3=LEN(B.COL2) )SELECT * FROM T ORDER BY LEFT(COL2,2)/*GUID COL1 COL2 PATH----------- ---------- -------------------- --------------------1 A 01 A2 B 01.01 A-->B3 C 01.01.01 A-->B-->C4 F 01.01.01.01 A-->B-->C-->F5 E 01.01.01.02 A-->B-->C-->E6 D 01.01.01.03 A-->B-->C-->D7 O 02 O8 P 02.01 O-->P9 Q 02.01.01 O-->P-->Q (9 行受影响)*/ ;WITH T AS( SELECT *,CAST(COL1 AS VARCHAR(1000)) AS PATH FROM TB WHERE COL2 NOT LIKE '%.%' UNION ALL SELECT A.*,CAST(B.PATH+'-->'+A.COL1 AS VARCHAR(1000)) FROM TB A,T B WHERE A.COL2 LIKE B.COL2+'.[01-99][01-99]' )SELECT * FROM T ORDER BY LEFT(COL2,2)/*GUID COL1 COL2 PATH----------- ---------- -------------------- --------------------1 A 01 A2 B 01.01 A-->B3 C 01.01.01 A-->B-->C4 F 01.01.01.01 A-->B-->C-->F5 E 01.01.01.02 A-->B-->C-->E6 D 01.01.01.03 A-->B-->C-->D7 O 02 O8 P 02.01 O-->P9 Q 02.01.01 O-->P-->Q (9 行受影响)*/
select 'A'站点1,'B'站点2 into zdb union all select 'B','C' union all select 'C','D' union all select 'B','E' union all select 'E','D' ;with a as( select [站点1],[站点2], [p]=cast([站点1]+'-'+[站点2] as nvarchar(1000)) from zdb where 站点1='a' union all select b.*,[p]=cast(p+'-'+b.[站点2] as nvarchar(1000)) from a join zdb b on b.站点1=a.站点2 ) select p from a where 站点2='d' p ------------- A-B-E-D A-B-C-D(2 行受影响)
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([站点1] nvarchar(1),[站点2] nvarchar(1))
Insert #T
select N'A',N'B' union all
select N'B',N'C' union all
select N'C',N'D' union all
select N'B',N'E' union all
select N'E',N'D'
Go
;with b
as
(
Select *,[站点1]+'-'+[站点2] as stop,Path=cast([站点1]+'-'+[站点2] as nvarchar(1000))from #T as a where not exists(select 1 from #T where a.[站点2]=[站点1])
union all
select a.*,b.stop,Path=cast(a.[站点1]+'-'+b.Path as nvarchar(1000)) from #T as a inner join b on a.[站点2]=b.站点1
)
select a.Path
from b as a
where not exists(select 1 from b where Path<a.Path and stop =a.stop)
/*
A-B-E-D
A-B-C-D
*/
http://blog.csdn.net/roy_88/article/details/2045842
--> 生成测试数据表:tb
IF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb](GUID INT IDENTITY,[col1] NVARCHAR(10),[col2] NVARCHAR(20))
INSERT [tb]
SELECT N'A','01' UNION ALL
SELECT N'B','01.01' UNION ALL
SELECT N'C','01.01.01' UNION ALL
SELECT N'F','01.01.01.01' UNION ALL
SELECT N'E','01.01.01.02' UNION ALL
SELECT N'D','01.01.01.03' UNION ALL
SELECT N'O','02' UNION ALL
SELECT N'P','02.01' UNION ALL
SELECT N'Q','02.01.01'
GO
--SELECT * FROM [tb]-->SQL查询如下:---另一种方法
;WITH T AS
(
SELECT *,PATH=CAST([COL1] AS VARCHAR(1000)) FROM TB A
WHERE NOT EXISTS(
SELECT 1 FROM TB
WHERE A.COL2 LIKE COL2+'%'
AND LEN(A.COL2)>LEN(COL2))
UNION ALL
SELECT A.*,CAST(PATH+'-->'+A.COL1 AS VARCHAR(1000))
FROM TB A
JOIN T B
ON A.COL2 LIKE B.COL2+'%' AND LEN(A.COL2)-3=LEN(B.COL2)
)SELECT * FROM T ORDER BY LEFT(COL2,2)/*GUID COL1 COL2 PATH----------- ---------- -------------------- --------------------1 A 01 A2 B 01.01 A-->B3 C 01.01.01 A-->B-->C4 F 01.01.01.01 A-->B-->C-->F5 E 01.01.01.02 A-->B-->C-->E6 D 01.01.01.03 A-->B-->C-->D7 O 02 O8 P 02.01 O-->P9 Q 02.01.01 O-->P-->Q
(9 行受影响)*/
;WITH T AS(
SELECT *,CAST(COL1 AS VARCHAR(1000)) AS PATH
FROM TB
WHERE COL2 NOT LIKE '%.%'
UNION ALL
SELECT A.*,CAST(B.PATH+'-->'+A.COL1 AS VARCHAR(1000))
FROM TB A,T B
WHERE A.COL2 LIKE B.COL2+'.[01-99][01-99]'
)SELECT * FROM T
ORDER BY LEFT(COL2,2)/*GUID COL1 COL2 PATH----------- ---------- -------------------- --------------------1 A 01 A2 B 01.01 A-->B3 C 01.01.01 A-->B-->C4 F 01.01.01.01 A-->B-->C-->F5 E 01.01.01.02 A-->B-->C-->E6 D 01.01.01.03 A-->B-->C-->D7 O 02 O8 P 02.01 O-->P9 Q 02.01.01 O-->P-->Q (9 行受影响)*/
select 'A'站点1,'B'站点2 into zdb
union all select 'B','C'
union all select 'C','D'
union all select 'B','E'
union all select 'E','D'
;with a as(
select [站点1],[站点2], [p]=cast([站点1]+'-'+[站点2] as nvarchar(1000)) from zdb where 站点1='a'
union all select b.*,[p]=cast(p+'-'+b.[站点2] as nvarchar(1000)) from a join zdb b on b.站点1=a.站点2
)
select p from a
where 站点2='d' p
-------------
A-B-E-D
A-B-C-D(2 行受影响)