SQL Server CTEs 递归功能

核心提示：CTEs支持在定义时引用自身,从而可以达到递归的目的，看下面的例子： ---prepare test data SET NOCOUNT ON; CREATE TABLE dbo.Parts ( partid INT NOT NULL PRIMARY KEY, partname VARCHAR(25) NOT NULL

CTEs支持在定义时引用自身,从而可以达到递归的目的，看下面的例子：

---prepare test data

SET NOCOUNT ON;

CREATE TABLE dbo.Parts

(

partid INT NOT NULL PRIMARY KEY,

partname VARCHAR(25) NOT NULL

);

INSERT INTO dbo.Parts(partid, partname) VALUES( 1, 'Black Tea');

INSERT INTO dbo.Parts(partid, partname) VALUES( 2, 'White Tea');

INSERT INTO dbo.Parts(partid, partname) VALUES( 3, 'Latte');

INSERT INTO dbo.Parts(partid, partname) VALUES( 4, 'Espresso');

INSERT INTO dbo.Parts(partid, partname) VALUES( 5, 'Double Espresso');

INSERT INTO dbo.Parts(partid, partname) VALUES( 6, 'Cup Cover');

INSERT INTO dbo.Parts(partid, partname) VALUES( 7, 'Regular Cup');

INSERT INTO dbo.Parts(partid, partname) VALUES( 8, 'Stirrer');

INSERT INTO dbo.Parts(partid, partname) VALUES( 9, 'Espresso Cup');

INSERT INTO dbo.Parts(partid, partname) VALUES(10, 'Tea Shot');

INSERT INTO dbo.Parts(partid, partname) VALUES(11, 'Milk');

INSERT INTO dbo.Parts(partid, partname) VALUES(12, 'Coffee Shot');

INSERT INTO dbo.Parts(partid, partname) VALUES(13, 'Tea Leaves');

INSERT INTO dbo.Parts(partid, partname) VALUES(14, 'Water');

INSERT INTO dbo.Parts(partid, partname) VALUES(15, 'Sugar Bag');