SQL Server 2005 CTE学习总结

lomg

　　SQL Server 2005引入了一个很有价值的Tracsact-SQL组件：通用表表达式（Common Table Expression,CTE）,它是一个可以由定义语句引用的临时命名的结果集，类似于引用派生表和视图的方式。
　　1，简单的使用   

USE AdventureWorks;
GO
--DirReps为CTE的名字，包含两个字段
WITH DirReps(ManagerID, DirectReports) AS
(
    SELECT ManagerID, COUNT(*)
    FROM HumanResources.Employee AS e
    WHERE ManagerID IS NOT NULL
    GROUP BY ManagerID
)
--返回表DirReps的内容
SELECT ManagerID, DirectReports
FROM DirReps
ORDER BY ManagerID;
GO　　

USE AdventureWorks;
GO
WITH Sales_CTE (SalesPersonID, NumberOfOrders, MaxDate)
AS
(
    SELECT SalesPersonID, COUNT(*), MAX(OrderDate)
    FROM Sales.SalesOrderHeader
    GROUP BY SalesPersonID
)
--这里我们再一次的使用了CTE Sales_CTE
SELECT E.EmployeeID, OS.NumberOfOrders, OS.MaxDate,
    E.ManagerID, OM.NumberOfOrders, OM.MaxDate
FROM HumanResources.Employee AS E
    JOIN Sales_CTE AS OS
    ON E.EmployeeID = OS.SalesPersonID
    LEFT OUTER JOIN Sales_CTE AS OM
    ON E.ManagerID = OM.SalesPersonID
ORDER BY E.EmployeeID;
GO　　
　　2，递归应用
　　利用CTE对一个表进行排序那是相当简单，我之前写过一个排序的例子，现在想想感觉很可笑。在同一个表内显示数据分级
　　

--创建原表
create table #test
(
id int,
level int,
pid int,
sortorder int
)
--插入数据,假定有三级数据
insert into #test select 1,0,0,1
insert into #test select 2,1,1,2
insert into #test select 3,0,0,3
insert into #test select 4,1,3,4
insert into #test select 5,0,0,5
insert into #test select 6,1,1,6
insert into #test select 7,2,4,7
go
--排序前
SELECT * FROM #test
go
--排序后
WITH CTE
AS
(
    SELECT *,cast('0' AS nvarchar(max)) + cast(ROW_NUMBER() OVER(PARTITION BY pid ORDER BY pid)AS nvarchar(20)) AS newsortorder FROM #test WHERE level = 0
    UNION ALL
    SELECT t.*,  (c.newsortorder + CAST(ROW_NUMBER() OVER(PARTITION BY t.pid ORDER BY t.pid) AS nvarchar(max))) AS newsortorder
    FROM #test t
    INNER JOIN CTE c ON t.pid = c.id
)
SELECT * FROM CTE ORDER BY newsortorder
go
DROP TABLE #test　　
　　参考：使用SQL2005 递归查询结合Row_Number()实现完全SQL端树排序
　　


作者：冰碟
出处：http://www.iyunv.com/icebutterfly/
版权：本文版权归作者和博客园共有
转载：欢迎转载，为了保存作者的创作热情，请按要求【转载】，谢谢
要求：未经作者同意，必须保留此段声明；必须在文章中给出原文连接；否则必究法律责任