CTE 也叫公用表表达式和派生表非常类似 先定义一个USACusts的CTE  

公用表表达式(Common Table Expression,CTE)和派生表类似,都是虚拟的表,但是相比于派生表,CTE具有一些优势和方便之处。

SQL Server 2005参考:CTE 公用表表达式

WITH USACusts AS
(
  SELECT custid, companyname
  FROM Sales.Customers
  WHERE country = N'USA'
)
SELECT * FROM USACusts;

CTE有两种类型:非递归的CTE和递归CTE。

当一个查询定义需要被多次调用时,一般可以采用临时表、视图、派生表或者是子查询缓存结果集(或是查询定义),但是,如果这个查询定义只为当前的处理服务,则上面的集中方式都不太合适:

with  ()  称为内部查询 
 与派生表相同,一旦外部查询完成后,CTE就自动释放了

CTE是标准SQL的特性,属于表表达式的一种,MariaDB支持CTE,MySQL
8才开始支持CTE。

A.       临时表会有额外的I/O开销;

CTE内部方式 就是上面代码所表示的方式  其实还有一种外部方式

1.非递归CTE

CTE是使用WITH子句定义的,包括三个部分:CTE名称cte_name、定义CTE的查询语句inner_query_definition和引用CTE的外部查询语句outer_query_definition。

它的格式如下:

WITH cte_name1[(column_name_list)] AS (inner_query_definition_1)
   [,cte_name2[(column_name_list)] AS (inner_query_definition_2)]
[,...]
outer_query_definition

其中column_name_list指定inner_query_definition中的列列表名,如果不写该选项,则需要保证在inner_query_definition中的列都有名称且唯一,即对列名有两种命名方式:内部命名和外部命名。

注意,outer_quer_definition必须和CTE定义语句同时执行,因为CTE是临时虚拟表,只有立即引用它,它的定义才是有意义的。

www.qy186.com 1

 

下面语句是一个简单的CTE的用法。首先定义一张虚拟表,也就是CTE,然后在外部查询中引用它。

CREATE OR REPLACE TABLE t(id INT NOT NULL PRIMARY KEY,sex CHAR(3),NAME CHAR(20));
INSERT INTO t VALUES (1,'nan','David'),(2,'nv','Mariah'),(3,'nv','gaoxiaofang'),(4,'nan','Jim'),
        (5,'nv','Selina'),(6,'nan','John'),(7,'nan','Monty'),(8,'nv','xiaofang');

# 定义CTE,顺便为每列重新命名,且使用ORDER BY子句
WITH nv_t(myid,mysex,myname) AS (
    SELECT * FROM t WHERE sex='nv' ORDER BY id DESC
)
# 使用CTE
SELECT * FROM nv_t;
+------+-------+-------------+
| myid | mysex | myname      |
+------+-------+-------------+
|    2 | nv    | Mariah      |
|    3 | nv    | gaoxiaofang |
|    5 | nv    | Selina      |
|    8 | nv    | xiaofang    |
+------+-------+-------------+

从结果中可以看到,在CTE的定义语句中使用ORDER BY子句是没有任何作用的。

在这里可以发现,CTE和派生表需要满足的几个共同点:每一列要求有列名,包括计算列;列名必须唯一;不能使用ORDER
BY子句,除非使用了TOP关键字(标准SQL严格遵守不能使用ORDER
BY的规则,但MySQL/MariaDB中允许)。不仅仅是CTE和派生表,其他表表达式(内联表值函数(sql
server才支持)、视图)也都要满足这些条件。究其原因,表表达式的本质是表,尽管它们是虚拟表,也应该满足形成表的条件。

一方面,在关系模型中,表对应的是关系,表中的行对应的是关系模型中的元组,表中的字段(或列)对应的是关系中的属性。属性由三部分组成:属性的名称、属性的类型和属性值。因此要形成表,必须要保证属性的名称,即每一列都有名称,且唯一。

另一方面,关系模型是基于集合的,在集合中是不要求有序的,因此不能在形成表的时候让数据按序排列,即不能使用ORDER
BY子句。之所以在使用了TOP后可以使用ORDER BY子句,是因为这个时候的ORDER
BY只为TOP提供数据的逻辑提取服务,并不提供排序服务。例如使用ORDER
BY帮助TOP选择出前10行,但是这10行数据在形成表的时候不保证是顺序的。

相比派生表,CTE有几个优点:

1.多次引用:避免重复书写。

2.多次定义:避免派生表的嵌套问题。

3.可以使用递归CTE,实现递归查询。

例如:

# 多次引用,避免重复书写
WITH nv_t(myid,mysex,myname) AS (
    SELECT * FROM t WHERE sex='nv'
)
SELECT t1.*,t2.*
FROM nv_t t1 JOIN nv_t t2
WHERE t1.myid = t2.myid+1;

# 多次定义,避免派生表嵌套
WITH
nv_t1 AS (          /* 第一个CTE */
    SELECT * FROM t WHERE sex='nv' 
),
nv_t2 AS (          /* 第二个CTE */
    SELECT * FROM nv_t1 WHERE id>3
)
SELECT * FROM nv_t2;

如果上面的语句不使用CTE而使用派生表的方式,则它等价于:

SELECT * FROM
(SELECT * FROM
(SELECT * FROM t WHERE sex='nv') AS nv_t1) AS nv_t2;

B.       视图是永久性的,不太适合用于临时性定义的处理;

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

2.递归CTE

SQL语言是结构化查询语言,它的递归特性非常差。使用递归CTE可稍微改善这一缺陷。

公用表表达式(CTE)具有一个重要的优点,那就是能够引用其自身,从而创建递归CTE。递归CTE是一个重复执行初始CTE以返回数据子集直到获取完整结果集的公用表表达式。

当某个查询引用递归CTE时,它即被称为递归查询。递归查询通常用于返回分层数据,例如:显示某个组织图中的雇员或物料清单方案(其中父级产品有一个或多个组件,而那些组件可能还有子组件,或者是其他父级产品的组件)中的数据。

递归CTE可以极大地简化在SELECT、INSERT、UPDATE、DELETE或CREATE
VIEW语句中运行递归查询所需的代码。

也就是说,递归CTE通过引用自身来实现。它会不断地重复查询每一次递归得到的子集,直到得到最后的结果。这使得它非常适合处理”树状结构”的数据或者有”层次关系”的数据。

C.        派生表或子查询会增加编写SQL语句的复杂性,也就降低的可读性。

定义多个CTE

2.1 语法

递归cte中包含一个或多个定位点成员,一个或多个递归成员,最后一个定位点成员必须使用”union
[all]”(mariadb中的递归CTE只支持union
[all]集合算法)联合第一个递归成员。

以下是单个定位点成员、单个递归成员的递归CTE语法:

with recursive cte_name as (
    select_statement_1       /* 该cte_body称为定位点成员 */
  union [all]
    cte_usage_statement      /* 此处引用cte自身,称为递归成员 */
)
outer_definition_statement    /* 对递归CTE的查询,称为递归查询 */

其中:

select_statement_1:称为”定位点成员“,这是递归cte中最先执行的部分,也是递归成员开始递归时的数据来源。

cte_usage_statement:称为”递归成员“,该语句中必须引用cte自身。它是递归cte中真正开始递归的地方,它首先从定位点成员处获取递归数据来源,然后和其他数据集结合开始递归,每递归一次都将递归结果传递给下一个递归动作,不断重复地查询后,当最终查不出数据时才结束递归。

outer_definition_statement:是对递归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;

2.2 递归CTE示例(1)

举个最经典的例子:族谱。

例如,下面是一张族谱表

CREATE OR REPLACE TABLE fork(id INT NOT NULL UNIQUE,NAME CHAR(20),father INT,mother INT);
INSERT INTO fork VALUES
    (1,'chenyi',2,3),(2,'huagner',4,5),(3,'zhangsan',NULL,NULL),
    (4,'lisi',6,7),(5,'wangwu',8,9),(6,'zhaoliu',NULL,NULL),(7,'sunqi',NULL,NULL),
    (8,'songba',NULL,NULL),(9,'yangjiu',NULL,NULL);

MariaDB [test]> select * from fork;
+----+----------+--------+--------+
| id | name     | father | mother |
+----+----------+--------+--------+
|  1 | chenyi   |      2 |      3 |
|  2 | huagner  |      4 |      5 |
|  3 | zhangsan |   NULL |   NULL |
|  4 | lisi     |      6 |      7 |
|  5 | wangwu   |      8 |      9 |
|  6 | zhaoliu  |   NULL |   NULL |
|  7 | sunqi    |   NULL |   NULL |
|  8 | songba   |   NULL |   NULL |
|  9 | yangjiu  |   NULL |   NULL |
+----+----------+--------+--------+

该族谱表对应的结构图: 

www.qy186.com 2

如果要找族谱中某人的父系,首先在定位点成员中获取要从谁开始找,例如上图中从”陈一”开始找。那么陈一这个记录就是第一个递归成员的数据源,将这个数据源联接族谱表,找到陈一的父亲黄二,该结果将通过union子句结合到上一个”陈一”中。再次对黄二递归,找到李四,再对李四递归找到赵六,对赵六递归后找不到下一个数据,所以这一分支的递归结束。

递归cte的语句如下:

WITH recursive fuxi AS (
    SELECT * FROM fork WHERE `name`='chenyi'
    UNION
    SELECT f.* FROM fork f JOIN fuxi a WHERE f.id=a.father
)
SELECT * FROM fuxi;

演变结果如下:

首先执行定位点部分的语句,得到定位点成员,即结果中的第一行结果集:

www.qy186.com 3

根据该定位点成员,开始执行递归语句:

www.qy186.com 4

递归时,按照f.id=a.father的条件进行筛选,得到id=2的结果,该结果通过union和之前的数据结合起来,作为下一次递归的数据源fuxi。

再进行第二次递归:

www.qy186.com 5

第三次递归:

www.qy186.com 6

由于第三次递归后,id=6的father值为null,因此第四次递归的结果为空,于是递归在第四次之后结束。 

SQL Server 2005 中新增了公用表表达式(CTE)来解决这样的问题,它是在当前的select、

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

2.2 递归CTE示例(2)

该CTE示例主要目的是演示切换递归时的字段名称。

例如,有几个公交站点,它们之间的互通性如下图:

www.qy186.com 7

对应的表为:

CREATE OR REPLACE TABLE bus_routes (src char(50), dst char(50));
INSERT INTO bus_routes VALUES 
  ('stopA','stopB'),('stopB','stopA'),('stopA','stopC'),('stopC','stopB'),('stopC','stopD');
MariaDB [test]> select * from bus_routes;
+-------+-------+
| src   | dst   |
+-------+-------+
| stopA | stopB |
| stopB | stopA |
| stopA | stopC |
| stopC | stopB |
| stopC | stopD |
+-------+-------+

要计算以stopA作为起点,能到达哪些站点的递归CTE如下:

WITH recursive dst_stop AS (
    SELECT src AS dst FROM bus_routes WHERE src='stopA'   /* note: src as dst */
    UNION
    SELECT b.dst FROM bus_routes b 
      JOIN dst_stop d 
    WHERE d.dst=b.src
)
SELECT * FROM dst_stop;

结果如下:

+-------+
| dst   |
+-------+
| stopA |
| stopB |
| stopC |
| stopD |
+-------+

首先执行定位点语句,得到定位点成员stopA,字段名为dst。

再将定位点成员结果和bus_routes表联接进行第一次递归,如下图:

www.qy186.com 8

再进行第二次递归:

www.qy186.com 9

再进行第三次递归,但第三次递归过程中,stopD找不到对应的记录,因此递归结束。 

insert、update、delete或是create view语句执行范围内定义的临时结果集。CTE与派生表类似,具体表现在不存储为对象,并且只在查询期间有效。与派生表的不同之处在于,CTE可自引用,还可在同一查询中引用多次。

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;

2.2 递归CTE示例(3)

仍然是公交路线图:

www.qy186.com 10

计算以stopA为起点,可以到达哪些站点,并给出路线图。例如: stopA–>stopC–>stopD 。

以下是递归CTE语句:

WITH recursive bus_path(bus_path,bus_dst) AS (
    SELECT src,src FROM bus_routes WHERE src='stopA'
    UNION
    SELECT CONCAT(b2.bus_path,'-->',b1.dst),b1.dst
    FROM bus_routes b1
      JOIN bus_path b2
    WHERE b2.bus_dst = b1.src AND LOCATE(b1.dst,b2.bus_path)=0
)
SELECT * FROM bus_path;

首先获取起点stopA,再获取它的目标stopB和stopC,并将起点到目标使用”–>”连接,即 concat(src,”–>”,”dst”) 。再根据stopB和stopC,获取它们的目标。stopC的目标为stopD和stopB,stopB的目标为stopA。如果连接成功,那么路线为:

stopA-->stopB-->stopA   目标:stopA
stopA-->stopC-->stopD   目标:stopD
stopA-->stopC-->stopB   目标:stopB

这样会无限递归下去,因此我们要判断何时结束递归。判断的方法是目标不允许出现在路线中,只要出现,说明路线会重复计算。

这样,可以提高复杂T-SQL语句的可读性和可维护性,查询可以分为单独快、简单块、逻辑生成块,之后这些简单快可以生成更复杂的CTE,知道生成最终结果集。

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

使用范围

递归CTE

CTE可以在函数、存储过程、触发器或是视图中定义和使用CTE。

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

          同时从使用角度可以分为简单CTE和递归CTE:

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

(1)         简单CTE,你可以理解为一个简单视图来使用;

   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 

(2)         递归CTE,就是CTE可以引用自身,来创建递归的CTE,实现递归查询(早期为实现递归查询需要使用临时表、游标等来实现)。

在前面也写过 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 

语法:

简单理解可以把它看成两部分

WITH cte_name ( column_name [,…n] )

SELECT  * FROM  dbo.dt_users
               WHERE  id=2

   SELECT d.* FROM  Emp
                         INNER JOIN dbo.dt_users d ON d.agent_id = Emp.id

AS

上部分的结果集 会储存成最后显示的结果 下部分的结果集  就是下一次递归的
上部分结果集 依次拼接  就是这个递归最后的结果集 

(

下部分 在详解  认真看很有意思

   
CTE_query_definition –- Anchor member is
defined(定位定成员).

  SELECT d.* FROM  Emp

SELECT d.* FROM   dbo.dt_users d

    UNION ALL

from Emp 源数据来自  d  在 on  d.agent_id = Emp.id 就是自连接 而 Emp.id
结果 来自哪里呢  就是上部分结果集
如果是第一次运行结果集就是上部分运行的结果 
 记住下部分操作结果集都是当前的上部分结果集。

   
CTE_query_definition –- Recursive member is
defined referencing

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

cte_name(递归成员).

 

)

我们这里将其过程简述如下:

(1)         将CTE表达式拆分为定位点成员和递归成员

(2)         运行定位点成员,创建第一个调用或基准结果(R1),递归的级数为i

(3)         运行递归成员,将Ri作为输入,将Ri+1作为输出,i为递归级数,每将运行递归成员后,i加1.

(4)         重复步骤3,直到返回空集。

(5)         返回结果集。这是对R1到Ri+1进行union all的结果。

 

         使用CTE还有一些注意事项,可以参考Sql server联机丛书的”WITH common_table_expression” 部分内容,同时还可以获得更多的示例。

示例

首先我们创建一个表Table, 只为示例使用,虚构内容

CREATE TABLE dept

(

    id INT PRIMARY
KEY,
— 部门编号

    parent_id
INT,       —
所属部门的编号

    NAME VARCHAR(20)  
  — 部门名称

)

INSERT INTO dept

SELECT 0,0,’全部’ UNION ALL

SELECT 1,0,’财务部’ UNION ALL

SELECT 2,0,’行政部’ UNION ALL

SELECT 3,0,’业务部’ UNION ALL

SELECT 4,3,’销售部’ UNION ALL

SELECT 5,3,’销售部’ UNION ALL

SELECT 6,3,’销售部’ UNION ALL

SELECT 7,0,’技术部’ UNION ALL

SELECT 8,7,’技术部’ UNION ALL

SELECT 9,7,’技术部’ UNION ALL

SELECT 10,7,’技术部’ UNION ALL

SELECT 11,8,’内部研发’ UNION ALL

SELECT 12,8,’外联部’ UNION ALL

SELECT 13,8,’事业部’ UNION ALL

SELECT 14,9,’内部测试’ UNION ALL

SELECT 15,9,’外联部’ UNION ALL

SELECT 16,9,’知识产权’ UNION ALL

SELECT 17,16,’自裁办’

(1)简单CTE

从dept表中获取部门编号为7的直接子部门的信息:

WITH W_1

AS

(

    SELECT *
FROM dept WHERE parent_id=7

)

SELECT * FROM
w_1

结果:

id         
  parent_id    NAME



8          
  7             技术部1

9          
  7             技术部2

10         
  7             技术部3

(3 row(s)
affected)

 

(2)覆盖基表的CTE

 在本例中定义了两个表t1和t2,然后定义一个名为t2的CTE,该CTE查询t1的内容,随后在CTE定义的有效范围内查询t2,然后在CTE的有效范围外查询t2,通过两个结果对比,一方面说明CTE定义的名称与基表名称冲突时,对该名称的引用实际引用CTE的内容,而非基表的内容;另一方面又说面了,需要在CTE定义后引用它,否则引用是
无效的(语法本身已经限制了)。

–table1

CREATE TABLE t1(id
INT);

INSERT INTO t1

SELECT 1 UNION ALL
SELECT 2;

–table2

CREATE TABLE t2(id
INT);

INSERT INTO t2

SELECT 3 UNION ALL
SELECT 4;

SELECT * FROM
t1;

WITH t2

as

(

    SELECT *
FROM t1

)

SELECT * FROM
t2;

SELECT * FROM
t2;

DROP TABLE t1,t2;

结果:

(2 row(s)
affected)

(2 row(s)
affected)

id

———–  
来自Table t1

1

2

(2 row(s)
affected)

id

———–   
来自CTE t2

1

2

(2 row(s)
affected)

id

———–   
来自Table t2

3

4

(2 row(s)
affected)

 

(3) 递归CTE

下面演示通过一个指定的部门编号,查询部门及其下面的所有子部门,使用dept表。

思路:

定义如下CTE dep,在CTE中,首先通过查询基表dept查询出指定的部门(即为
定点成员);然后通过对这个查询结果的引用(即引用CTE自身),与基表dept做join(递归成员),查询出指定部门的下级部门;由于递归成员会反复执行,直到查询的结果集为空。

DECLARE @sID INT;

SET @sID=7;

WITH dep as

(

–定位点成员

    SELECT *
FROM dept WHERE id=@sID

    UNION ALL


递归成员,通过引用CTE自身与dept基表JOIN实现递归

    SELECT dt.*
FROM dept dt JOIN dep d ON dt.parent_id=d.id

)

SELECT * FROM dep
ORDER BY id

结果:

id         
parent_id   NAME



7          
0           技术部

8          
7           技术部

9          
7           技术部

10         
7           技术部

11         
8           内部研发

12         
8           外联部

13         
8           事业部

14         
9           内部测试

15         
9           外联部

16         
9           知识产权

17         
16          自裁办

(11 row(s)
affected)

(6) 综合应用的CTE

该示例演示的基本需求与示例3一样,由指定的部门编号,查询其及以所包含的所有子部门,在这个结果基础上查询出每个记录对应的部门及其下包含的子部门数(包含其下所有层级的部门)。

首先你需要理解清楚上面的需求。

我们这里定义3个CTE,第一个(同上)查询出指定的部门及其所包含的所有各层级子部门;第2个CTE引用第一个CTE的内容,同样通过递归查询每个子部门(这里的部门由第一个CTE确定);第3个CTE,仅仅为了做一个汇总,;最后JOIN 1和3这两个CTE得到最后的结果。

DECLARE @sID INT;

SET @sID=7;

WITH d_1 as

(

    — 定位点成员

    SELECT *
FROM dept WHERE id=@sID

    UNION ALL

    — 递归成员,通过引用CTE自身与dept基表JOIN实现递归

    SELECT dt.*
FROM dept dt JOIN d_1 d ON dt.parent_id=d.id

)

–SELECT
* FROM dep ORDER BY id

,

d_2

AS

(

    SELECT d_id=dp.id,dt.id,dt.parent_id FROM dept dt JOIN d_1 dp ON dt.parent_id=dp.id

    UNION ALL

    SELECT dpd.d_id,dd.id,dd.parent_id FROM dept dd JOIN d_2 dpd ON dd.parent_id=dpd.id

)

–SELECT
* FROM depchild ORDER BY d_id

,

d_3

AS

(

    SELECT d_id,Cnt = COUNT(*)
FROM d_2 GROUP BY
d_id

)

SELECT d.id,d.[NAME],ChildCount=ISNULL(Cnt,0) FROM d_1
d LEFT JOIN d_3 dc

ON d.id=dc.d_id

结果:

id         
NAME                 ChildCount



7          
技术部                
10

8          
技术部               
 3

9          
技术部               
 4

10         
技术部               
 0

14         
内部测试              
0

15         
外联部               
 0

16         
知识产权              
1

17         
自裁办                
0

11         
内部研发             
 0

12         
外联部                
0

13         
事业部                
0

(11 row(s)
affected)

相关文章