CTE 也叫公用表表达式和派生表非常类似 先定义一个USACusts的CTE
WITH USACusts AS ( SELECT custid, companyname FROM Sales.Customers WHERE country = N'USA' ) SELECT * FROM USACusts;
with () 称为内部查询 与派生表相同,一旦外部查询完成后,CTE就自动释放了
CTE内部方式 就是上面代码所表示的方式 其实还有一种外部方式
WITH C(orderyear, custid) AS ( SELECT YEAR(orderdate), custid FROM Sales.Orders ) SELECT orderyear, COUNT(DISTINCT custid) AS numcusts FROM C GROUP BY orderyear; GO
C(orderyear, custid) 可以理解为 select orderyear, custid from C 指定返回你想要的列 不过个人感觉没什么用! 它和派生表相同 也可以在CTE中查询使用参数
; WITH C AS ( SELECT YEAR(orderdate) AS orderyear, custid FROM Sales.Orders WHERE empid = @empid ) SELECT orderyear, COUNT(DISTINCT custid) AS numcusts FROM C GROUP BY orderyear; GO
定义多个CTE
WITH C1 AS ( SELECT YEAR(orderdate) AS orderyear, custid FROM Sales.Orders ), C2 AS ( SELECT orderyear, COUNT(DISTINCT custid) AS numcusts FROM C1 GROUP BY orderyear ) SELECT orderyear, numcusts FROM C2 ;
多个CTE用 , 隔开 通过with 内存 可以在外查询中多次引用
WITH YearlyCount AS ( SELECT YEAR(orderdate) AS orderyear, COUNT(DISTINCT custid) AS numcusts FROM Sales.Orders GROUP BY YEAR(orderdate) ) SELECT Cur.orderyear, Cur.numcusts AS curnumcusts, Prv.numcusts AS prvnumcusts, Cur.numcusts - Prv.numcusts AS growth FROM YearlyCount AS Cur LEFT OUTER JOIN YearlyCount AS Prv ;
可以需要在多个相同表结果做物理实例化 这样可以节省很多查询时间 或者在临时表和表变量中固化内部查询结果
递归CTE
递归CTE至少由两个查询定义,至少一个查询作为定位点成员,一个查询作为递归成员。
递归成员是一个引用CTE名称的查询 ,在第一次调用递归成员,上一个结果集是由上一次递归成员调用返回的。 其实就和C# 方法写递归一样 返回上一个结果集 依次输出
WITH Emp AS ( SELECT * FROM dbo.dt_users UNION ALL SELECT d.* FROM Emp INNER JOIN dbo.dt_users d ON d.agent_id = Emp.id ) SELECT * FROM Emp
在前面也写过 sql 语句的执行顺序 其实到 FROM Emp 时 就进行了节点第一次递归 当我们递归到第三次的时候 这个为执行的sql 语句实际是什么样的呢
WITH Emp AS ( SELECT * FROM dbo.dt_users UNION ALL SELECT * FROM dbo.dt_users UNION ALL SELECT * FROM dbo.dt_users UNION ALL SELECT d.* FROM Emp INNER JOIN dbo.dt_users d ON d.agent_id = Emp.id ) SELECT * FROM Emp
简单理解可以把它看成两部分
SELECT * FROM dbo.dt_users
SELECT d.* FROM Emp INNER JOIN dbo.dt_users d ON d.agent_id = Emp.id
上部分的结果集 会储存成最后显示的结果 下部分的结果集 就是下一次递归的 上部分结果集 依次拼接 就是这个递归最后的结果集
下部分 在详解 认真看很有意思
SELECT d.* FROM Emp
SELECT d.* FROM dbo.dt_users d
from Emp 源数据来自 d 在 on d.agent_id = Emp.id 就是自连接 而 Emp.id 结果 来自哪里呢 就是上部分结果集 如果是第一次运行结果集就是上部分运行的结果 记住下部分操作结果集都是当前的上部分结果集。
默认情况下递归是100次 也可在 外部查询 指定递归次数 MAXRECURSION N 0~32767 次范围 MAXRECURSION 0 并不是0次实际上是递归次数无限制