T-SQL:CTE用法(十)

2019-08-04 03:43栏目:网站首页

CTE 也叫公用表表达式和派生表非常周围 先定义贰个USACusts的CTE  

公用表表明式(Common Table Expression,CTE)和派生表类似,都以杜撰的表,不过比较于派生表,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。

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是暂且虚拟表,只有立时引用它,它的定义才是有含义的。

图片 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的定义语句中利用O奥德赛DE奥迪Q5 BY子句是没有其余意义的。

在此间能够开掘,CTE和派生表须要满意的多少个共同点:每一列需要有列名,包涵计算列;列名必须独一;不可能选择OWranglerDER BY子句,除非选用了TOP关键字(标准SQL严谨坚守无法运用OENCOREDER BY的条条框框,但MySQL/MariaDB中允许)。不止是CTE和派生表,其余表表达式(内联表值函数(sql server才援救)、视图)也都要满足那么些标准。究其原因,表表达式的实质是表,尽管它们是设想表,也应当满足产生表的原则。

一边,在论及模型中,表对应的是事关,表中的行对应的是涉及模型中的元组,表中的字段(或列)对应的是关联合中学的属性。属性由三有的构成:属性的名号、属性的档期的顺序和属性值。因而要产生表,必必要保管属性的称呼,即每一列皆盛名称,且独一。

一边,关系模型是依赖集合的,在汇集中是不须求平稳的,因而不能够在多变表的时候让多少按序排列,即无法运用OWranglerDER BY子句。之所以在选拔了TOP后得以运用OXC90DESportage BY子句,是因为今年的ORubiconDER BY只为TOP提供数据的逻辑提取服务,并不提供排序服务。举个例子使用O奇骏DER 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;
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通过引用作者来兑现。它会随处地再度查询每壹遍递归获得的子集,直到得到终极的结果。那使得它极其适合管理"树状结构"的数量也许有"等级次序关系"的数目。

概念多个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 |
 ---- ---------- -------- -------- 

该族谱表对应的结构图: 

图片 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;

演化结果如下:

先是执行定位点部分的言语,获得定位点成员,即结果中的第一行结果集:

图片 3

听大人讲该定位点成员,起头推行递归语句:

图片 4

递归时,根据f.id=a.father的规格举办筛选,获得id=2的结果,该结果通过union和在此以前的数码整合起来,作为下叁次递归的数额源fuxi。

再进行第二回递归:

图片 5

其一次递归:

图片 6

鉴于第三次递归后,id=6的father值为null,由此第肆遍递归的结果为空,于是递归在第六回之后停止。 

八个CTE用 , 隔绝 通过with 内部存款和储蓄器 能够在外查询中数次援用

2.2 递归CTE示例(2)

该CTE示例主要目标是身体力行切换递归时的字段名称。

譬喻说,有多少个公共交通站点,它们之间的互通性如下图:

图片 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表联接举办第叁次递归,如下图:

图片 8

再拓展第二次递归:

图片 9

再开始展览第一次递归,但第贰遍递归进程中,stopD找不到对应的笔录,因此递归停止。 

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)

照例是公共交通路径图:

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

如此那般会Infiniti递归下去,因此大家要推断曾几何时停止递归。决断的艺术是目的差异意出现在路径中,只要现身,表达路径会再度总结。

能够供给在多个一样表结果做物理实例化  那样能够节省不知凡几查询时间 或然在有时表和表变量中固化内部查询结果

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

 

版权声明:本文由威尼斯人app发布于网站首页,转载请注明出处:T-SQL:CTE用法(十)