数据层级显示（根据代码级次显示名称）

问题贴：http://topic.youkuaiyun.com/u/20100428/14/51caf51d-bfc7-4832-9783-2805e13085e5.html?11679

--------------------------------------------------------------------------

--  Author : htl258(Tony)

--  Date   : 2010-04-28 14:48:10

--  Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)

--          Jul  9 2008 14:43:34

--          Copyright (c) 1988-2008 Microsoft Corporation

--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)

--  Blog   : http://blog.youkuaiyun.com/htl258

--  Subject: 数据层级显示（根据代码级次显示名称）

--------------------------------------------------------------------------

--> 生成测试数据表: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查询如下:

--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 行受影响)

*/