SQL Server CTEs 递归功能

核心提示： INSERT INTO dbo.Parts(partid, partname) VALUES(16, 'Ground Coffee'); INSERT INTO dbo.Parts(partid, partname) VALUES(17, 'Coffee Beans

INSERT INTO dbo.Parts(partid, partname) VALUES(16, 'Ground Coffee');

INSERT INTO dbo.Parts(partid, partname) VALUES(17, 'Coffee Beans');

CREATE TABLE dbo.BOM

(

partid INT NOT NULL REFERENCES dbo.Parts,

assemblyid INT NULL REFERENCES dbo.Parts,

unit VARCHAR(3) NOT NULL,

qty DECIMAL(8, 2) NOT NULL,

UNIQUE(partid, assemblyid),

CHECK (partid <> assemblyid)

);

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

VALUES( 1, NULL, 'EA', 1.00);

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

VALUES(14, 1, 'mL', 230.00);

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