BOM展开方法整理代码,MSSQL代码库,德仔网

代码语言
代码分类
【MSSQL】 BOM展开方法整理
作者:静华 / 发布于2017/6/30/ 759
---------------------------------------------------------------------------------
-- 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 行受影响)
*/
试试其它关键字
同语言下
可能有用的
静华贡献的其它代码(9)