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查询如下:
--BY:TONY
;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 A
2 B 01.01 A-->B
3 C 01.01.01 A-->B-->C
4 F 01.01.01.01 A-->B-->C-->F
5 E 01.01.01.02 A-->B-->C-->E
6 D 01.01.01.03 A-->B-->C-->D
7 O 02 O
8 P 02.01 O-->P
9 Q 02.01.01 O-->P-->Q
(9 行受影响)
*/
--BY:LDSLOVE
;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 A
2 B 01.01 A-->B
3 C 01.01.01 A-->B-->C
4 F 01.01.01.01 A-->B-->C-->F
5 E 01.01.01.02 A-->B-->C-->E
6 D 01.01.01.03 A-->B-->C-->D
7 O 02 O
8 P 02.01 O-->P
9 Q 02.01.01 O-->P-->Q
(9 行受影响)
*/
--BY:NIANRAN520
SELECT T.*,
REPLACE(STUFF(
(SELECT ','+COL1 FROM TB
WHERE CHARINDEX('.'+COL2+'.','.'+T.COL2+'.') > 0
FOR XML PATH('')),1,1,''),',','-->') AS ITEM
FROM TB T
/*
GUID COL1 COL2 PATH
----------- ---------- -------------------- --------------------
1 A 01 A
2 B 01.01 A-->B
3 C 01.01.01 A-->B-->C
4 F 01.01.01.01 A-->B-->C-->F
5 E 01.01.01.02 A-->B-->C-->E
6 D 01.01.01.03 A-->B-->C-->D
7 O 02 O
8 P 02.01 O-->P
9 Q 02.01.01 O-->P-->Q
(9 行受影响)
*/
本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/htl258/archive/2010/04/28/5540003.aspx