T-SQL:CTE用法(十)

2019-12-17 14:55栏目:产品测评
TAG:

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 结果 来自哪儿呢  正是上有的结出集 要是是第2回运转结果集就是上一些运维的结果   记住下部分操作结果集都以当前的上部分结果集。

默认情况下递归是100次 也可在 外部查询 指定递归次数 MAXRECURSION N 0~32767 次范围 MAXRECURSION 0 并不是0次实际上是递归次数无限制

 

版权声明:本文由本港台现场报码发布于产品测评,转载请注明出处:T-SQL:CTE用法(十)