Sql Server2005 TransactSQL 新兵器学习总结之公用表表达式(CTE)

原文地址：http://www.cnblogs.com/aierong/archive/2008/07/31/1257250.html

 这些数据存放在表Co_ItemNameSet中,表结构和部分数据如下：

ItemId      ParentItemId ItemName
2           0            管理费用
3           0            销售费用
4           0            财务费用
5           0            生产成本
35          5            材料
36          5            人工
37          5            制造费用
38          35           原材料
39          35           主要材料
40          35           间辅材料
41          36           工资
42          36           福利
43          36           年奖金

现在需求是:我想查询ItemId=2,也就是管理费用和其下属所有节点的信息

通过CTE可以很简单达到需求要的数据

结果如下：
ItemId ParentItemId ItemName level
2     0             管理费用     1
52     2             汽车费用     2
55     2             招聘费     2
56     2             排污费     2
53     52             燃料          3
54     52             轮胎          3

大家看到sql2000解决这个问题比较麻烦，要实现这需求编写的代码比较多，比较复杂
现在好了，在sql2005中通过CTE的递归特点可以2步就实现.
得到同样的结果，sql2005的CTE代码简单了许多.这就是CTE支持递归查询的魅力。
请看下面的代码：

--sql2005版本
DECLARE @i INT
SELECT @i=2;

WITH Co_ItemNameSet_CTE(ItemId, ParentItemId, ItemName,Level)
AS
(
    SELECT  ItemId, ParentItemId, ItemName ,1 AS [Level]
    FROM  Co_ItemNameSet
    WHERE itemid=@i  
    UNION ALL
    SELECT  c.ItemId, c.ParentItemId, c.ItemName ,[Level] + 1
    FROM  Co_ItemNameSet c  INNER JOIN Co_ItemNameSet_CTE ct
    ON c.ParentItemId=ct.ItemId
)

SELECT * FROM Co_ItemNameSet_CTE
go