BOM树形节点展开实例

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

-- Author : htl258(Tony)

-- Date   : 2009-07-30 17:19:15

-- 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 2)

-- Blog   : http://blog.csdn.net/htl258

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

 

--> 生成测试数据表:tree

 

IF NOT OBJECT_ID('[tree]') IS NULL

    DROP TABLE [tree]

GO

CREATE TABLE [tree]

(

    [id] INT, [pid] INT,

    [name] NVARCHAR(3)

)

INSERT tree

SELECT 1, 1, 'A' UNION ALL

SELECT 2, 2, 'B' UNION ALL

SELECT 11, 1, 'AA' UNION ALL

SELECT 111, 11, 'AAA' UNION ALL

SELECT 22, 2, 'BB' UNION ALL

SELECT 33, 2, 'CC' UNION ALL

SELECT 44, 11, 'DDD' UNION ALL

SELECT 55, 1, 'EE'

GO

--Select * from tree

 

-->SQL查询如下:

;WITH t AS

(

    SELECT *,CAST(id AS VARBINARY) AS px,lvl=0

    FROM [tree] AS A

    WHERE NOT EXISTS(

              SELECT *

              FROM [tree]

              WHERE id = A.pid

                  AND id <> A.id

          )

    UNION ALL

    SELECT A.*,CAST(px+CAST(a.id AS VARBINARY) AS VARBINARY),lvl+1

    FROM [tree] AS A

    JOIN t AS B

        ON CASE a.pid

                WHEN a.id THEN 0

                ELSE a.pid

           END=b.id

 

)

SELECT REPLICATE('-', lvl)+name AS name

FROM t

ORDER BY px;

/*

name

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

A

-AA

--DDD

--AAA

-EE

B

-BB

-CC

 

(8 行受影响)

*/