隔开 通过with 内存 可以在外查询中多次引用

日期:2019-11-10编辑作者:北京pk10官网-数据库

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

 

本文由北京pk赛车官网下载发布于北京pk10官网-数据库,转载请注明出处:隔开 通过with 内存 可以在外查询中多次引用

关键词:

mssql sqlserver 将字段null(空值)值替换为指定值的三

转自:http://www.maomao365.com/?p=6965   摘要: 下文将分享两种将字段中null值替换为指定值的方法分享,如下所示: 实验环境...

详细>>

因为安装的过程中没让设置密码

我的操作系统是ubuntu18.04,以下是我的mysql版本: 安装完成后,登录mysql的时候就出现了如下错误: 因为安装的过程中...

详细>>

LogBackups事务日志备份会追加到同一个备份文件)

一台数据库服务器的事务日志备份作业偶尔会出现几次备份失败的情况,具体的错误信息为:     DATE/TIME:    2018...

详细>>

忽然发现会报一个date类型的字段的默认值错误

在给一个表添加字段的时候,忽然发现会报一个date类型的字段的默认值错误,郁闷~ 经过排查,原来是MySQL的配置问题...

详细>>