MariaDB表表达式,公用表表达式

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的性状,归于表表达式的意气风发种,玛丽亚DB接济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是有时设想表,独有登时援引它,它的概念才是有含义的。

图片 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汉兰达 BY子句是未有其它作用的。

在那处可以窥见,CTE和派生表必要满足的多少个合营点:每一列供给有列名,包罗计算列;列名必得唯生机勃勃;不能够使用O路虎极光DER
BY子句,除非动用了TOP关键字(标准SQL严厉据守不能够利用OENVISIONDER
BY的法则,但MySQL/玛丽亚DB中允许)。不独有是CTE和派生表,其余表表达式(内联表值函数(sql
server才帮忙)、视图)也都要满意那个规范。究其原因,表表明式的本质是表,尽管它们是虚拟表,也相应满意变成表的口径。

单向,在提到模型中,表对应的是关乎,表中的行对应的是涉嫌模型中的元组,表中的字段(或列卡塔 尔(阿拉伯语:قطر‎对应的是事关中的属性。属性由三片段构成:属性的名目、属性的花色和属性值。因而要形成表,一定要确认保障属性的名号,即每一列皆知名称,且唯大器晚成。

单向,关系模型是基于集结的,在联谊中是不供给不改变的,由此不可能在多变表的时候让数据按序排列,即不能够利用O揽胜极光DER
BY子句。之所以在利用了TOP后方可利用ORubiconDEKoleos BY子句,是因为那时候的OENVISIONDER
BY只为TOP提供数据的逻辑提取服务,并不提供排序服务。举例利用OLacrosseDER
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]聚拢算法)联合第2个递归成员。

以下是单个定位点成员、单个递归成员的递归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,因而第六遍递归的结果为空,于是递归在第陆遍未来截止。 

SQL Server 贰零零柒 中新扩张了公用表表明式(CTE卡塔 尔(英语:State of Qatar)来化解那样的主题素材,它是在当前的select、

多个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表联接举办第1回递归,如下图:

图片 8

再进行第三次递归:

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

照例是公共交通路径图:

图片 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递归下去,由此大家要认清哪一天甘休递归。判别的诀若是目的分歧意出以后渠道中,只要出现,表明路径会再次总计。

那样,能够拉长复杂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卡塔 尔(英语:State of Qatar)         递归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卡塔 尔(阿拉伯语:قطر‎         运维定位点成员,创立第叁个调用或标准结果(Tiguan1卡塔 尔(英语:State of Qatar),递归的级数为i

(3卡塔 尔(阿拉伯语:قطر‎         运维递归成员,将GL450i作为输入,将Ri+1用作出口,i为递归级数,每将运维递归成员后,i加1.

(4卡塔尔         重复步骤3,直到回到空集。

(5卡塔 尔(阿拉伯语:قطر‎         再次来到结果集。那是对福睿斯1到Ri+1进行union all的结果。

 

         使用CTE还恐怕有意气风发对注意事项,能够参照Sql server联机丛书的”WITH common_table_expression” 部分内容,同期仍是可以够得到更加多的演示。

示例

率先我们创造二个表Table, 只为示范使用,杜撰剧情

CREATE TABLE dept

(

    id INT PRAV4IMA陆风X8Y
KEY,
— 部门编号

    parent_id
INT,       —
所属单位的号码

    NAME VARCHA中华V(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卡塔 尔(英语:State of Qatar)覆盖基表的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查询出钦赐的机构(即为
定点成员卡塔 尔(英语:State of Qatar);然后通过对这一个查询结果的引用(即援用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,第四个(同上卡塔尔国查询出内定的机构连同所包罗的有着各层级子部门;第1个CTE引用首个CTE的源委,相像通过递归查询种种子部门(这里的机关由第多少个CTE分明卡塔尔;第三个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)

发表评论

电子邮件地址不会被公开。 必填项已用*标注