SQL Server CTEs 递归功能

核心提示： VALUES( 6, 2, 'EA', 1.00); INSERT INTO dbo.BOM(partid, assemblyid, unit, qty) VALUES( 7, 2, 'EA', 1.00); INSERT INTO dbo.BOM(part

VALUES( 6, 2, 'EA', 1.00);

INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)

VALUES( 7, 2, 'EA', 1.00);

INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)

VALUES(10, 2, 'EA', 1.00);

INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)

VALUES(14, 2, 'mL', 205.00);

INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)

VALUES(11, 2, 'mL', 25.00);

INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)

VALUES( 6, 3, 'EA', 1.00);

INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)

VALUES( 7, 3, 'EA', 1.00);

INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)

VALUES(11, 3, 'mL', 225.00);

INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)

VALUES(12, 3, 'EA', 1.00);

INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)

VALUES( 9, 4, 'EA', 1.00);

INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)

VALUES(12, 4, 'EA', 1.00);

INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)

VALUES( 9, 5, 'EA', 1.00);

INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)

VALUES(12, 5, 'EA', 2.00);

INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)

VALUES(13, 10, 'g' , 5.00);

INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)

VALUES(14, 10, 'mL', 20.00);

INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)

VALUES(14, 12, 'mL', 20.00);

INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)

VALUES(16, 12, 'g' , 15.00);

INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)

VALUES(17, 16, 'g' , 15.00);

-- perform the test

WITH BOMTC AS(

SELECT assemblyid, partid

FROM dbo.BOM

WHERE assemblyid IS NOT NULL

UNION ALL

SELECT P.assemblyid, C.partid

FROM BOMTC AS P

JOIN dbo.BOM AS C ON C.assemblyid = P.partid

)

SELECT DISTINCT assemblyid, partid FROM BOMTC;

输出结果如下：

assemblyid partid

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

1 6

1 7

1 10

1 13

1 14

2 6

2 7

2 10

2 11

2 13

2 14

3 6

3 7

3 11

3 12

3 14

3 16

3 17

4 9

4 12

4 14

4 16

4 17

5 9

5 12

5 14

5 16

5 17

10 13

10 14

12 14

12 16

12 17

16 17