玛丽亚DB表表明式

玛丽亚DB表表明式

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的表征,属于表表达式的1种,玛丽亚DB帮助CTE,MySQL
八才初叶帮助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宝马7系 BY子句是未有别的效率的。

在此处能够窥见,CTE和派生表需求满意的多少个共同点:每一列供给有列名,包蕴总结列;列名必须唯一;无法运用O兰德酷威DER
BY子句,除非动用了TOP关键字(标准SQL严酷服从无法动用OXC60DER
BY的条条框框,但MySQL/玛丽亚DB中允许)。不仅仅是CTE和派生表,别的表表达式(内联表值函数(sql
server才帮忙)、视图)也都要满意这一个规则。究其原因,表表明式的精神是表,固然它们是虚拟表,也理应满意产生表的口径。

一面,在关系模型中,表对应的是关系,表中的行对应的是关乎模型中的元组,表中的字段(或列)对应的是涉嫌中的属性。属性由叁有的构成:属性的称谓、属性的档次和属性值。由此要造成表,需要求保管属性的名目,即每壹列都盛名称,且唯1。

壹边,关系模型是基于集合的,在聚集中是不要求不改变的,由此不能够在多变表的时候让多少按序排列,即不可能运用O瑞鹰DER
BY子句。之所以在选拔了TOP后方可运用OPAJERODE逍客 BY子句,是因为那一年的O昂CoraDER
BY只为TOP提供数据的逻辑提取服务,并不提供排序服务。例如使用O大切诺基DER
BY协助TOP选抽取前10行,可是那10行数据在形成表的时候不保险是种种的。

对照派生表,CTE有多少个亮点:

一.频仍引用:幸免重复书写。

二.再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可稍许革新那1瑕疵。

公用表表明式(CTE)具备1个最首要的亮点,那正是力所能及引用其自个儿,从而开创递归CTE。递归CTE是三个再次实行起来CTE以回到数据子集直到获取完整结果集的公用表表明式。

当有些查询引用递归CTE时,它即被称作递归查询。递归查询普通用于再次回到分层数据,例如:展现有些组织图中的雇员或货色清单方案(个中父级产品有二个或多少个零部件,而这些组件只怕还有子组件,只怕是别的父级产品的机件)中的数据。

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

也等于说,递归CTE通过引用笔者来兑现。它会没完没了地重复查询每三遍递归获得的子集,直到得到最终的结果。那使得它分外适合处理”树状结构”的数码还是有”层次关系”的数额。

概念三个CTE

2.1 语法

递归cte中涵盖2个或四个定位点成员,多个或多个递归成员,最后三个定位点成员必须选拔”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中第3试行的片段,也是递归成员开首递归时的数目来源于。

cte_usage_statement:称为”递归成员“,该语句中务必引用cte自个儿。它是递归cte中确确实实先河递归的地方,它首先从定位点成员处获得递归数据出自,然后和任何数据集结合先河递归,每递归3回都将递归咎果传递给下1个递归动作,不断重复地查询后,当最后查不出数据时才甘休递归。

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

只要要找族谱中某人的父系,首先在定位点成员中赢得要从什么人开头找,例如上海教室中从”陈一”伊始找。那么陈一那一个记录就是首先个递归成员的数据源,将这些数额源联接族谱表,找到陈一的老爹黄2,该结果将由此union子句结合到上2个”陈壹”中。再度对黄二递归,找到李四,再对李4递归找到赵⑥,对赵陆递归后找不到下三个数目,所以这一拨出的递归截止。

递归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,由此第1一次递归的结果为空,于是递归在第伍回今后停止。 

多个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表联接进行第3回递归,如下图:

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

第3获得源点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至少由两个查询定义,至少三个查询作为定位点成员,二个询问作为递归成员。

网站地图xml地图