详解公用表表达式(CTE)

作者:数据库

能够供给在八个近似表结果做物理实例化  那样能够节约数不清询问时间 只怕在一时表和表变量中固化内部查询结果

2.递归CTE

SQL语言是结构化查询语言,它的递归天性比较不佳。使用递归CTE可有个别改良那大器晚成缺欠。

公用表表明式(CTE)具备二个首要的优点,那就是力所能致援用其自己,进而创设递归CTE。递归CTE是二个重新推行起来CTE以回到数据子集直到获取完整结果集的公用表表明式。

当有些查询援用递归CTE时,它即被称作递归查询。递归查询普通用于再次来到分层数据,比方:彰显有些团体图中的雇员或货物清单方案(个中父级产品有一个或四个零件,而这个组件也许还会有子组件,大概是别的父级产品的零部件)中的数据。

递归CTE能够超级大地简化在SELECT、INSERT、UPDATE、DELETE或CREATE VIEW语句中运转递归查询所需的代码。

也便是说,递归CTE通过援用小编来贯彻。它会不断地再次查询每二遍递归获得的子集,直到得到最后的结果。那使得它特别符合管理"树状结构"的数额依旧有"等级次序关系"的多少。


公用表表明式(CTE)的定义


    公用表明式的概念特轻松,只含有三片段:

  1.   公用表表达式的名字(在WITH之后)
  2.   所涉及的列名(可选)
  3.   一个SELECT语句(紧跟AS之后)

    在MSDN中的原型:

WITH expression_name [ ( column_name [,...n] ) ] 

AS 

( CTE_query_definition ) 

 

   根据是不是递归,能够将公用表(CTE)表明式分为递归公用表表达式和非递归公用表表明式.

 

在面前也写过 sql 语句的进行顺序 其实到  FROM Emp   时 就进展了节点第一次递归  当大家递归到第二遍的时候 那几个为履行的sql 语句实在是哪些的啊

2.2 递归CTE示例(3)

长期以来是公共交通路径图:

数据库 1

计算以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

这么会非常递归下去,因而我们要看清何时甘休递归。判定的方法是指标不容许出现在路径中,只要出现,表明路径会另行计算。

CREATE TABLE t1(id INT);

非递归公用表表达式(CTE)


   非递归公用表表达式(CTE)是查询结果独有叁次性重临三个结实集用于外界查询调用。并不在其定义的语句中调用其本人的CTE

   非递归公用表表明式(CTE)的施用办法和视图以及子查询一致

   譬喻一个简易的非递归公用表表明式:

   数据库 2

 

   当然,公用表表明式的好处之一是足以在接下去一条语句中多次援用:

 

   数据库 3

 

 

   前边作者一直重申“在接下去的一条语句中”,意味着只好接下去一条利用:

   数据库 4

 

   由于CTE只可以在接下去一条语句中央银行使,因而,当必要接下去的一条语句中援用多个CTE时,能够定义多少个,中间用逗号分隔:

   数据库 5

 

   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 

CTE是行业内部SQL的表征,属于表表达式的风姿洒脱种,MariaDB帮衬CTE,MySQL 8才起来援助CTE。

DROP TABLE t1,t2;

总结 


    CTE是风度翩翩种特别平淡的存在。CTE所带来最大的低价是代码可读性的晋级换代,那是优越代码的必得品质之意气风发。使用递归CTE能够特别轻便欢悦的用温婉凝练的诀窍达成复杂的查询。

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

2.1 语法

递归cte中含有八个或多少个定位点成员,三个或八个递归成员,最后一个定位点成员必需使用"union [all]"(mariadb中的递归CTE只帮衬union [all]会师算法)联合第4个递归成员。

以下是单个定位点成员、单个递归成员的递归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的询问,那么些查询称为"递归查询"。

INSERT INTO dept

递归公用表表明式(CTE)


    递归公用表表明式很像派生表(Derived Tables ),指的是在CTE内的言语中调用其本人的CTE.与派生表分歧的是,CTE能够在二次定义数十回扩充派生递归.对于递归的定义,是指二个函数或是进度一向恐怕直接的调用其本人,递归的简要概念图如下:

   数据库 6

    递归在C语言中落到实处的三个优质例证是斐波那契数列:

long fib(int n)   
{   
     if (n==0) return 0;
   if (n==1) return 1;   
     if (n>1) return fib(n-1) fib(n-2);
} 

  

   上面C语言代码能够见到,要结成递归函数,须求两局部。第豆蔻梢头局部是基础部分,重临固定值,约等于报告程序几时初叶递归。第二部分是循环部分,是函数或进程一向大概直接调用自己实行递归.

 

   对于递归公用表明式来说,达成原理也是均等的,相像要求在讲话中定义两局部:

  •    基本语句
  •    递归语句

   在SQL这两部分通过UNION ALL连接结果集举行再次来到:

   举个例子:在AdventureWork中,笔者想精通种种员工所处的层级,0是最高等

   数据库 7

  

 

 

 

   这么复杂的询问通过递归CTE变得那样文雅和简洁.那也是CTE最刚劲的地方.

   当然,越强盛的力量,就必要被节制.如若使用不当的话,递归CTE或然会身不由己最为递归。进而大批量消耗SQL Server的服务器资源.由此,SQL Server提供了OPTION选项,能够设定最大的递归次数:

   依然地方拾贰分语句,约束了递归次数:

   数据库 8

   所提示的音讯:

   数据库 9

 

   那几个最大递归次数往往是基于数量所表示的求实事情相关的,比如此处,假如公司层级最三唯有2层.

 

SELECT  * FROM  dbo.dt_users
               WHERE  id=2

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

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 |
 ---- ---------- -------- -------- 

该族谱表对应的组织图: 

数据库 10

若是要找族谱中有些人的父系,首先在定位点成员中获取要从哪个人开首找,举个例子上海教室中从"陈风度翩翩"起首找。那么陈风流罗曼蒂克这么些记录就是首先个递归成员的数据源,将以此数额源联接族谱表,找到陈意气风发的阿爹黄二,该结果将因而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;

衍生和变化结果如下:

首先实行定位点部分的言辞,获得定位点成员,即结果中的第大器晚成行结果集:

数据库 11

依附该定位点成员,早前施行递归语句:

数据库 12

递归时,遵照f.id=a.father的规范化举办挑选,获得id=2的结果,该结果通过union和事先的多少整合起来,作为下二遍递归的多寡源fuxi。

再张开第三遍递归:

数据库 13

其二遍递归:

数据库 14

是因为第一回递归后,id=6的father值为null,因而第八回递归的结果为空,于是递归在第九回之后结束。 

    parent_id INT,       -- 所属单位的号码

简介


     对于SELECT查询语句来讲,日常意况下,为了使T-SQL代码特别简明和可读,在贰个询问中引用其余的结果集都是通过视图并不是子查询来打开分解的.然则,视图是作为系统对象存在数据库中,那对于结果集仅仅必要在蕴藏进度恐怕客商自定义函数中运用三次的时候,使用视图就显得有一点浪费了.

    公用表表明式(Common Table Expression)是SQL SE奥迪Q3VE奥迪Q52007本子之后引进的贰个性格.CTE能够用作是二个一时的结果集,能够在接下去的叁个SELECT,INSERT,UPDATE,DELETE,ME福睿斯GE语句中被每每引用。使用公用表明式能够让语句尤其清晰简练.

     除却,依照微软对CTE好处的描述,能够归咎为四点:

  •      能够定义递归公用表表明式(CTE)
  •      当不须求将结果集作为视图被三个地方援用时,CTE能够使其进一步简明
  •     GROUP BY语句能够平昔功能于子查询所得的标量列
  •     能够在几个言语中数次引用公用表表明式(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;

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是暂且虚构表,独有及时援用它,它的定义才是有意义的。

数据库 15

 

下边语句是一个简短的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的定义语句中央银行使OENVISIONDEOdyssey BY子句是未有任何功效的。

在那地能够开采,CTE和派生表须要满意的多少个协同点:每一列供给有列名,富含总结列;列名必需唯大器晚成;不能够应用O陆风X8DER BY子句,除非动用了TOP关键字(规范SQL严谨遵照不可能采纳O大切诺基DER BY的规规矩矩,但MySQL/MariaDB中允许)。不止是CTE和派生表,别的表表明式(内联表值函数(sql server才扶植)、视图)也都要知足那几个原则。究其原因,表表达式的原形是表,固然它们是虚构表,也应当满意产生表的标准化。

大器晚成边,在关乎模型中,表对应的是关乎,表中的行对应的是关乎模型中的元组,表中的字段(或列)对应的是涉嫌中的属性。属性由三有的组成:属性的名称、属性的品种和属性值。由此要形成表,必必要力保属性的名号,即每一列皆闻名称,且唯生龙活虎。

风度翩翩边,关系模型是基于集合的,在集结中是不要求不改变的,由此无法在变成表的时候让数据按序排列,即不能够动用O福睿斯DER BY子句。之所以在使用了TOP后能够应用OEscortDE奥迪Q3 BY子句,是因为当时的OENVISIONDER BY只为TOP提供数据的逻辑提取服务,并不提供排序服务。比如利用O奥迪Q7DER 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;

d_3

CTE 也叫公用表表达式和派生表非常相近 先定义三个USACusts的CTE  

公用表表明式(Common Table Expression,CTE)和派生表相通,都以编造的表,可是比较于派生表,CTE具备局地优势和有助于的地方。

id

概念七个CTE

CTE有两体系型:非递归的CTE和递归CTE。

,

递归成员是一个援引CTE名称的查询 ,在首先次调用递归成员,上贰个结实集是由上一回递归成员调用重回的。 其实就和C# 方法写递归相同  再次回到上七个结果集 依次输出

2.2 递归CTE示例(2)

该CTE示例首要目标是亲自过问切换递归时的字段名称。

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

数据库 16

相应的表为:

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表联接进行第一遍递归,如下图:

数据库 17

再张开第二遍递归:

数据库 18

再开展第一回递归,但第贰次递归进程中,stopD找不到相应的记录,由此递归停止。 

SELECT 14,9,'公测' UNION ALL

递归CTE起码由多个查询定义,最少多少个询问作为定位点成员,叁个查询作为递归成员。

概念如下CTE dep,在CTE中,首先通过查询基表dept查询出内定的机构(即为 定点成员);然后经过对那几个查询结果的援用(即援引CTE本人),与基表dept做join(递归成员),查询出钦点部门的属下部门;由于递归成员会再三施行,直到询问的结果集为空。

 

结果:

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;

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

多个CTE用 , 隔断 通过with 内部存款和储蓄器 能够在外查询中反复援引

大家这边定义3个CTE,第三个(同上)查询出钦赐的单位会同所蕴藏的兼具各层级子部门;第1个CTE引用第叁个CTE的内容,同样通过递归查询每一个子部门(这里的机构由第贰个CTE显明);第四个CTE,仅仅为了做二个汇聚,;最终JOIN 1和3那八个CTE得到终极的结果。

递归CTE

AS

下一些 在详解  认真看很有趣

1

上一些的结果集 会累积成最终呈现的结果 下某个的结果集  正是下二次递归的 上部分结出集 依次拼接  正是以此递归最终的结果集 

)

  SELECT d.* FROM  Emp

SELECT d.* FROM   dbo.dt_users d

语法:

简易了然能够把它当作两有的

cte_name(递归成员).

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

    SELECT * FROM dept WHERE id=@sID

CTE内部方式 便是地点代码所表示的格局  其实还可能有少年老成种外界方式

WITH cte_name ( column_name [,...n] )

   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 

)

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

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

from Emp 源数据来源  d  在 on  d.agent_id = Emp.id 就是自连接 而 Emp.id 结果 来自哪里呢  正是上某个结实集 假使是率先次运营结果集正是上一些运转的结果   记住下部分操作结果集都以前段时间的上有个别结果集。

17          16          自裁办

with  ()  称为内部查询   与派生表相似,风度翩翩旦外界查询完成后,CTE就自行释放了

d_2


3

(2)         递归CTE,正是CTE能够引用笔者,来创设递归的CTE,达成递归查询(先前时代为落到实处递归查询必要选取有的时候表、游标等来促成)。

    -- 定位点成员

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

9           技术部                 4

示例

CREATE TABLE t2(id INT);

    SELECT * FROM t1

结果:

从dept表中收获部门编号为7的直接子部门的新闻:

7           技术部                 10

SELECT 11,8,'内部研究开发' UNION ALL

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

-----------    来自Table t2

(

-----------    来自CTE t2

--SELECT * FROM dep ORDER BY id

SELECT 17,16,'自裁办'

    SELECT * FROM dept WHERE id=@sID

(3) 递归CTE

SET @sID=7;

(2)覆盖基表的CTE

(2)         运维定位点成员,制造第3个调用或条件结果(宝马7系1),递归的级数为i

小编们这里将其进度简述如下:

本文由ca88发布,转载请注明来源

关键词: ca88网址 Sql Server MS SQL 参考 CTE