我们写下CTE sql,如下所示
WITH yourCTE AS ( SELECT .... FROM :... WHERE..... ) SELECT * FROM yourCTE
将sql放入块中会有什么好处.我认为如果我们把复杂的sql与块,那么我们只需要像SELECT * FROM yourCTE一样编写sql.好像我正在访问视图.
在性能方面使用CTE的附加优势.请讨论.谢谢
解决方法
有一些CTE可以真正有用的情况：
>递归查询,比如走上一个层次结构树,这是非常棘手和繁琐的,没有CTE(见here for a sample of a recursive CTE)
>任何时候想使用其中一个排序函数,如ROW_NUMBER(),RANK(),NTILE()等等(见here for info on ranking functions)
一般情况下,您需要首先根据一些条件选择几行/列,然后再执行这些操作.更新表,删除重复等
一种情况我经常使用CTE来删除一组给定的数据的最新行,例如,如果你有客户和他们的订单有1：n的关系,并且你想删除除最新订单之外的所有订单(基于OrderDate),对于每个客户来说,在没有CTE的情况下,在sql中这样做是相当毛茸茸的.
有了CTE和排名功能,这很轻松：
;WITH CustomerOrders AS
(
SELECT
c.CustomerID,o.OrderID,ROW_NUMBER() OVER(PARTITION BY c.CustomerID ORDER BY o.OrderDate DESC) AS 'RowN'
FROM
dbo.Customer c
INNER JOIN
dbo.Orders o ON o.CustomerID = c.CustomerID
)
DELETE FROM
dbo.Orders
FROM
CustomerOrders co
WHERE
dbo.Orders.OrderID = co.OrderID
AND co.RowN > 1
通过这种方式,您可以创建一个由CustomerID分区的“内联视图”(例如,每个客户从1开始获取rownumbers),按OrderDate DESC(最新的顺序)排序.对于每个客户,最新的最新订单都有RowN = 1,因此您可以轻松地删除所有其他行,并且已经完成了您想要做的 – 一个带有CTE的蛋糕 – 没有它的凌乱的代码….
