T-SQL:CTE用法（十）

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中查询使用参数

DECLARE @empid AS INT = 3;

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
WHERE numcusts > 70;

多个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
    ON Cur.orderyear = Prv.orderyear + 1;

可以需要在多个相同表结果做物理实例化  这样可以节省很多查询时间 或者在临时表和表变量中固化内部查询结果

递归CTE

递归CTE至少由两个查询定义，至少一个查询作为定位点成员，一个查询作为递归成员。

递归成员是一个引用CTE名称的查询 ，在第一次调用递归成员，上一个结果集是由上一次递归成员调用返回的。 其实就和C# 方法写递归一样  返回上一个结果集 依次输出

   WITH    Emp
 AS ( SELECT  * FROM  dbo.dt_users
               WHERE  id=2
                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
               WHERE  id=2
                UNION ALL  
                SELECT  * FROM  dbo.dt_users
               WHERE  id=3
                UNION ALL  
                SELECT  * FROM  dbo.dt_users
               WHERE  id=4
                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
               WHERE  id=2

   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次实际上是递归次数无限制