SQL Server BOM展开方法整理

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

-- Author : htl258(Tony)

-- Date   : 2010-07-06 18:33:50

-- 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.csdn.net/htl258 (转载保留此信息)

-- Subject: SQL Server BOM展开方法整理

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

 

--> 生成测试数据表: [tb]

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

    DROP TABLE [tb]

GO

CREATE TABLE [tb] ([ID] [int],[Name] [nvarchar](10),[PID] [int])

INSERT INTO [tb]

SELECT '1','A','0' UNION ALL

SELECT '2','B','0' UNION ALL

SELECT '3','A1','1' UNION ALL

SELECT '4','B1','2' UNION ALL

SELECT '5','B2','2' UNION ALL

SELECT '6','A11','3' UNION ALL

SELECT '7','A12','3' UNION ALL

SELECT '8','A111','6' UNION ALL

SELECT '9','A112','6' UNION ALL

SELECT '10','A1111','8' UNION ALL

SELECT '11','A1112','8'

 

--SELECT * FROM [tb]

 

-->SQL查询如下:

--1.BOM展开并按节点深度排序查询方法:

--1.1 SQL2000 指定某节点展开,并按节点深度排序:

IF OBJECT_ID('dbo.f_GetTree') IS NOT NULL  

    DROP FUNCTION dbo.f_GetTree;  

GO  

CREATE FUNCTION dbo.f_GetTree

(

    @ID AS INT=NULL

)

RETURNS @r TABLE

(

    ID INT,

    lvl INT,

    px VARBINARY(8000)

)

AS

BEGIN

    DECLARE @lvl INT   

    SET @lvl = 0 

   

    IF ISNULL(@ID,0)<>0

       INSERT @r VALUES(@ID,@lvl,CAST(@ID AS VARBINARY))

    ELSE

       INSERT @r

       SELECT ID, @lvl ,CAST(ID AS VARBINARY)

       FROM tb

       WHERE PID = 0 

        

    WHILE @@rowcount>0

    BEGIN

        SET @lvl = @lvl+1;  

        INSERT @r

        SELECT a.ID, @lvl ,b.px+CAST(a.ID AS VARBINARY)

        FROM tb a

        JOIN @r b

            ON  a.PID = b.ID

                AND b.lvl = @lvl-1

    END

    RETURN;

END

GO

--显示所有节点:

SELECT a.*, b.lvl

FROM tb a

    JOIN dbo.f_GetTree(1) b

       ON  a.ID = b.ID  

ORDER BY b.px

--结果:

/*

ID          Name       PID         lvl

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

1           A          0           0

3           A1         1           1

6           A11        3           2

8           A111       6           3

10          A1111      8           4

11          A1112      8           4

9           A112       6           3

7           A12        3           2

2           B          0           0

4           B1         2           1

5           B2         2           1

 

(11 行受影响)

*/

--查询指定节点,如显示节点下的所有节点:

SELECT a.*, b.lvl

FROM tb a

    JOIN dbo.f_GetTree(2) b

       ON  a.ID = b.ID  

ORDER BY b.px

/*

ID          Name       PID         lvl

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

2           B          0           0

4           B1         2           1

5           B2         2           1

 

(3 行受影响)

*/

 

--1.2 SQL2005 指定某节点展开,并按节点深度排序:

--显示所有节点:

;WITH t AS

(

    SELECT ID,lvl=0,px=CAST(ID AS VARBINARY)

    FROM tb t

    WHERE PID=0

    UNION ALL

    SELECT a.ID,lvl+1,CAST(px+CAST(a.ID AS VARBINARY) AS VARBINARY)

    FROM tb a

       JOIN t b

           ON  a.PID = b.ID

)

SELECT a.*,lvl

FROM tb a

    JOIN t b 

       ON a.ID=b.ID

ORDER BY b.px

/*

ID          Name       PID         lvl

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

1           A          0           0

3           A1         1           1

6           A11        3           2

8           A111       6           3

10          A1111      8           4

11          A1112      8           4

9           A112       6           3

7           A12        3           2

2           B          0           0

4           B1         2           1

5           B2         2           1

 

(11 行受影响)

*/

 

--显示指定节点,如显示节点下的所有节点:

;WITH t AS

(

    SELECT ID,lvl=0,px=CAST(ID AS VARBINARY)

    FROM tb t

    WHERE ID=2

    UNION ALL

    SELECT a.ID,lvl+1,CAST(px+CAST(a.ID AS VARBINARY) AS VARBINARY)

    FROM tb a

       JOIN t b

           ON  a.PID = b.ID

)

SELECT a.*,lvl

FROM tb a

    JOIN t b 

       ON a.ID=b.ID

ORDER BY b.px

/*

ID          Name       PID         lvl

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

2           B          0           0

4           B1         2           1

5           B2         2           1

 

(3 行受影响)

*/

 

--2. BOM反查并按节点深度排序查询方法:

--2.1 SQL2000 BOM反查,并按节点深度排序:

IF OBJECT_ID('dbo.f_GetPTree') IS NOT NULL  

    DROP FUNCTION dbo.f_GetPTree;  

GO  

CREATE FUNCTION dbo.f_GetPTree

(

    @ID AS INT=NULL

)

RETURNS @r TABLE

(

    ID INT,

    PID INT,

    lvl INT,

    px VARBINARY(8000)

)

AS

BEGIN

    DECLARE @lvl INT   

    SET @lvl = 0 

   

    INSERT @r

    SELECT ID,PID, @lvl ,CAST(ID AS VARBINARY)

    FROM tb

    WHERE ID = @ID

        

    WHILE @@rowcount>0

    BEGIN

        SET @lvl = @lvl+1;  

        INSERT @r

        SELECT a.ID,a.PID, @lvl ,b.px+CAST(a.ID AS VARBINARY)

        FROM tb a

        JOIN @r b

            ON  a.ID = b.PID

                AND b.lvl = @lvl-1

    END

    RETURN;

END

GO

--查询指定节点,如反查节点的所有父节点:

SELECT a.*, b.lvl

FROM tb a

    JOIN dbo.f_GetPTree(11) b

       ON  a.ID = b.ID  

ORDER BY b.px

/*

ID          Name       PID         lvl

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

11          A1112      8           0

8           A111       6           1

6           A11        3           2

3           A1         1           3

1           A          0           4

 

(5 行受影响)

*/

 

--2.2 SQL2005 BOM反查,并按节点深度排序:

--查询指定节点,如反查节点的所有父节点:

;WITH t AS

(

    SELECT ID,PID,lvl=0,px=CAST(ID AS VARBINARY)

    FROM tb t

    WHERE ID=11

    UNION ALL

    SELECT a.ID,a.PID,lvl+1,CAST(px+CAST(a.ID AS VARBINARY) AS VARBINARY)

    FROM tb a

       JOIN t b

           ON  a.ID = b.PID

)

SELECT a.*,lvl

FROM tb a

    JOIN t b 

       ON a.ID=b.ID

ORDER BY b.px

/*

ID          Name       PID         lvl

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

11          A1112      8           0

8           A111       6           1

6           A11        3           2

3           A1         1           3

1           A          0           4

 

(5 行受影响)

*/

 

 

评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值