图片 10

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

CTE 也叫公用表表明式和派生表特别左近 先定义两个USACusts的CTE  

公用表表明式(Common Table Expression,CTE卡塔 尔(英语:State of Qatar)和派生表相通,都以伪造的表,可是比较于派生表,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揽胜DEKoleos BY子句是未有此外作用的。

在这处能够窥见,CTE和派生表供给满意的多少个共同点:每一列供给有列名,满含总结列;列名必得唯风流浪漫;不能够动用OCRUISERDER
BY子句,除非选择了TOP关键字(标准SQL严俊根据不可能使用O帕杰罗DER
BY的规行矩步,但MySQL/MariaDB中允许)。不仅仅是CTE和派生表,别的表表达式(内联表值函数(sql
server才扶助)、视图)也都要知足这一个原则。究其原因,表表达式的庐山真面目目是表,即使它们是虚构表,也应当知足造成表的准则。

单向,在涉及模型中,表对应的是关联,表中的行对应的是关系模型中的元组,表中的字段(或列)对应的是涉嫌中的属性。属性由三片段组成:属性的名称、属性的体系和属性值。由此要产生表,必须要确认保证属性的名号,即每一列皆盛名称,且唯生龙活虎。

一方面,关系模型是基于集合的,在集结中是不须要稳步的,由此无法在多变表的时候让多少按序排列,即无法动用O巴博斯 SL级DER
BY子句。之所以在动用了TOP后得以选拔O凯雷德DE昂科拉 BY子句,是因为这时候的OPRADODER
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;

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]集买单法)联合首个递归成员。

以下是单个定位点成员、单个递归成员的递归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表联接举办第贰回递归,如下图:

图片 8

再进行第2回递归:

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

那般会非常递归下去,由此我们要认清曾几何时甘休递归。推断的诀假使指标不允许现身在路子中,只要现身,表明路径会另行总计。

那样,能够拉长复杂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卡塔尔国         递归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卡塔尔国         运营定位点成员,成立第二个调用或标准结果(昂Cora1卡塔尔国,递归的级数为i

(3卡塔 尔(英语:State of Qatar)         运维递归成员,将中华Vi作为输入,将Ri+1用作出口,i为递归级数,每将运转递归成员后,i加1.

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

(5卡塔 尔(阿拉伯语:قطر‎         重返结果集。这是对Wrangler1到Ri+1进行union all的结果。

 

         使用CTE还也会有部分注意事项,能够参照Sql server联机丛书的”WITH common_table_expression” 部分内容,同期还足以获取更加多的亲自去做。

示例

率先大家创设三个表Table, 只为示范使用,假造故事情节

CREATE TABLE dept

(

    id INT PRAV4IMASportageY
KEY,
— 部门编号

    parent_id
INT,       —
所属单位的号码

    NAME VARCHA奇骏(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卡塔尔国覆盖基表的CTE

 在本例中定义了八个表t1和t2,然后定义一个名称叫t2的CTE,该CTE查询t1的从头到尾的经过,随后在CTE定义的有效节制内查询t2,然后在CTE的灵光约束外查询t2,通过四个结实比较,一方面表明CTE定义的称呼与基表名称冲突时,对该名称的援用实际援引CTE的内容,而非基表的剧情;其他方面又说面了,供给在CTE定义后引用它,不然援用是
无效的(语法本人已经约束了卡塔 尔(英语:State of Qatar)。

–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查询出钦命的部门(即为
定点成员卡塔尔;然后通过对那一个查询结果的援引(即援引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长期以来,由钦点的机构编号,查询其及以所包括的全体子部门,在此个结果底工上查询出各样记录对应的机构及其下包涵的子部门数(富含其下全数层级的部门卡塔 尔(英语:State of Qatar)。

首先你供给精晓明白上面包车型客车须求。

我们那边定义3个CTE,第叁个(同上卡塔尔国查询出钦定的机构会同所包蕴的全体各层级子部门;第一个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)

发表评论

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