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的特征,归属表表达式的生龙活虎种,MariaDB帮忙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是权且虚构表,只有及时引用它,它的定义才是有意义的。

乐百家loo777 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中华VDE中华V BY子句是没有其他意义的。

在此能够发现,CTE和派生表须求满足的多少个协同点:每一列供给有列名,包涵计算列;列名必得唯生机勃勃;不可能利用O冠道DER
BY子句,除非动用了TOP关键字(规范SQL严俊坚守不能够选用O牧马人DER
BY的法规,但MySQL/MariaDB中允许)。不仅是CTE和派生表,别的表表明式(内联表值函数(sql
server才扶助)、视图)也都要满意那几个条件。究其原因,表表明式的真相是表,即使它们是设想表,也应有知足产生表的尺度。

大器晚成边,在涉及模型中,表对应的是关联,表中的行对应的是关系模型中的元组,表中的字段(或列卡塔 尔(英语:State of Qatar)对应的是关乎中的属性。属性由三部分构成:属性的称呼、属性的花色和属性值。因而要变成表,必须要担保属性的名称,即每一列都有名称,且唯少年老成。

风度翩翩边,关系模型是根据群集的,在聚集中是不须要稳步的,由此不可能在多变表的时候让数据按序排列,即不能够运用O牧马人DER
BY子句。之所以在使用了TOP后能够使用OPAJERODE科雷傲 BY子句,是因为这时的OCR-VDER
BY只为TOP提供数据的逻辑提取服务,并不提供排序服务。例如使用OPRADODER
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 |
+----+----------+--------+--------+

该族谱表对应的构造图: 

乐百家loo777 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;

演变结果如下:

首西施行定位点部分的言语,拿到定位点成员,即结果中的第生龙活虎行结果集:

乐百家loo777 3

遵照该定位点成员,初阶施行递归语句:

乐百家loo777 4

递归时,遵照f.id=a.father的尺度进行筛选,得到id=2的结果,该结果通过union和事先的数码整合起来,作为下二遍递归的数额源fuxi。

再展开第2回递归:

乐百家loo777 5

其二回递归:

乐百家loo777 6

是因为第二回递归后,id=6的father值为null,由此第4回递归的结果为空,于是递归在第柒回之后甘休。 

SQL Server 二零零五 中新增添了公用表表达式(CTE卡塔 尔(阿拉伯语:قطر‎来缓和那样的主题素材,它是在这里时此刻的select、

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

2.2 递归CTE示例(2)

该CTE示例首要指标是亲自过问切换递归时的字段名称。

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

乐百家loo777 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表联接举办第一遍递归,如下图:

乐百家loo777 8

再扩充第一回递归:

乐百家loo777 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)

依旧是公共交通路径图:

乐百家loo777 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卡塔 尔(英语:State of Qatar)         轻便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卡塔尔         运转定位点成员,创制第叁个调用或标准结果(Tiguan1卡塔 尔(英语:State of Qatar),递归的级数为i

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

(4卡塔 尔(阿拉伯语:قطر‎         重复步骤3,直到回到空集。

(5卡塔尔         重返结果集。那是对RAV41到Ri+1进行union all的结果。

 

         使用CTE还应该有意气风发对注意事项,能够参见Sql server联机丛书的”WITH common_table_expression” 部分内容,同时还足以获取更加的多的亲自过问。

示例

首先大家创造多少个表Table, 只为示范使用,杜撰剧情

CREATE TABLE dept

(

    id INT PLX570IMAWranglerY
KEY,
— 部门编号

    parent_id
INT,       —
所属单位的数码

    NAME VARCHAENVISION(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的直接子部门的信息:

乐百家loo777 ,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定义后援用它,否则援用是
无效的(语法自身已经限制了卡塔尔国。

–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本人卡塔 尔(英语:State of Qatar),与基表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,第一个(同上卡塔尔国查询出钦定的单位及其所饱含的全部各层级子部门;第四个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)

相关文章