SQL Server BOM展开方法整理

最新推荐文章于 2021-01-14 16:18:40 发布

最新推荐文章于 2021-01-14 16:18:40 发布 · 144 阅读

文章标签：

#SQL Server #SQL #Go #F# #Microsoft

本文介绍了使用 SQL Server 2000 和 2005 版本进行物料清单 (BOM) 的展开和反查操作的方法。通过定义递归函数和使用 WITH 语句实现了对树形结构数据的查询，并提供了按节点深度排序的功能。

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

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

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

-- Blog : http://blog.youkuaiyun.com/htl258 (转载保留此信息)

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

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

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

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

DROP TABLE [tb]

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;

CREATE FUNCTION dbo.f_GetTree

(

@ID AS INT=NULL

)

RETURNS @r TABLE

(

ID INT,

lvl INT,

px VARBINARY(8000)

)

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

--显示所有节点：

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;

CREATE FUNCTION dbo.f_GetPTree

(

@ID AS INT=NULL

)

RETURNS @r TABLE

(

ID INT,

PID INT,

lvl INT,

px VARBINARY(8000)

)

BEGIN

DECLARE @lvl INT

SET @