MariaDB表表达式

MariaDB表表达式

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的概念语句中央银行使ORAV4DEQashqai BY子句是未有其余功能的。

在这里能够窥见,CTE和派生表须要满意的多少个共同点:每一列供给有列名,满含总结列;列名必须唯一;不能够应用OPRADODER
BY子句,除非选用了TOP关键字(标准SQL严刻遵从无法利用O福睿斯DER
BY的准绳,但MySQL/玛丽亚DB中允许)。不仅是CTE和派生表,其余表表达式(内联表值函数(sql
server才支持)、视图)也都要满足那个原则。究其原因,表表明式的原形是表,就算它们是虚构表,也应当满意产生表的规格。

一面,在关系模型中,表对应的是关系,表中的行对应的是关系模型中的元组,表中的字段(或列)对应的是关乎中的属性。属性由三有个别构成:属性的称呼、属性的品类和属性值。因而要形成表,必要求保管属性的名称,即每一列皆著名称,且唯一。

一边,关系模型是基于集结的,在聚聚集是不供给不改变的,由此不能够在多变表的时候让多少按序排列,即不能够运用O奔驰M级DER
BY子句。之所以在选择了TOP后方可使用O奥迪Q7DE路虎极光 BY子句,是因为那年的OXC60DER
BY只为TOP提供数据的逻辑提取服务,并不提供排序服务。举例使用O宝马X3DER
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

那样会特别递归下去,因而大家要看清什么日期截止递归。判别的艺术是指标不一致意出现在路线中,只要出现,表达路径会再也计算。

能够须要在四个同样表结果做物理实例化  这样能够省去不知凡几询问时间
大概在有时表和表变量中固化内部查询结果

递归CTE

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

网站地图xml地图