应用递归左联接

Question

我正在使用SQL Server 2012，我有以下问题：

我有这个表(类别(：

IDCategory| CategoryDesc     | Father
1         | R1               | 0
2         | R1 - ST          | 1
3         | R1 - CT          | 1
4         | R1 - ST - SA     | 2
5         | R1 - ST - CA 10  | 2
6         | R1 - ST - CA 20  | 2
7         | R1 - CT - CA     | 3
8         | R1 - CT - SA     | 3
9         | R2               | 0
10        | R2 ST            | 9
.         
.         
until R9         

和这个(类别定义(：

IDCategory| First| Last
1         | 0    | 300
9         | 301  | 600
.         
.         
.         

我正在使用以下查询，因为我知道只有3个级别：

SELECT
cat3.IDCategory,
cat.CategoryDesc AS title1,
cat2.CategoryDesc AS title2,
cat3.CategoryDesc AS title3,
catdef.First,
catdef.Last
FROM Category as cat
LEFT JOIN Category AS cat2 ON cat2.Father=cat.IDCategory
LEFT JOIN Category AS cat3 ON cat3.Father=cat2.IDCategory
INNER JOIN CategoryDefinition as catdef on cat.IDCategory = catdef.IDCategory
WHERE cat3.IDCategory = 7

查询结果：

IDCategory| title1 | title2  |title3        |First|Last
    7     | R1     | R1 - CT | R1 - CT - CA |  0  | 300

但是我该如何制作此递归？如果将来可能会出现新的级别(因此我不必为出现的每个新级别添加一个新的左JON(。

谢谢！

Answer 1

我至少可以帮助邻接列表的回收CTE并构建一条实现的路径：

为此表：

create table category (IDCategory int primary key,CategoryDesc varchar(32),Father int)
insert into category values 
 (1,'R1',0)
,(2,'ST',1)
,(3,'CT',1)
,(4,'SA',3)
,(5,'SA 10',2)
,(6,'SA 20',2)
,(7,'CA',2)
,(8,'SA',2)
,(9,'R2',0)
,(10,'ST',9)

使用递归CTE：

;with cte as (
-- anchor elements: where Father = 0
  select 
      IDCategory
    , categoryDesc
    , Father
    , parentName = convert(varchar(32),null)
    , path = convert(varchar(128),categoryDesc)
  from category
  where Father = 0
  -- recursion begins here
  union all 
  select 
      c.IDCategory 
    , c.categoryDesc
    , c.Father
    , parentName = p.categoryDesc
    , path = convert(varchar(128),p.path+' - '+c.categoryDesc)
  from category c
    inner join cte as p on c.Father= p.IDCategory
)
-- we select all the results 
select cte.*
from cte 
order by idCategory

返回：

+------------+--------------+--------+------------+-----------------+
| IDCategory | categoryDesc | Father | parentName |      path       |
+------------+--------------+--------+------------+-----------------+
|          1 | R1           |      0 | NULL       | R1              |
|          2 | ST           |      1 | R1         | R1 - ST         |
|          3 | CT           |      1 | R1         | R1 - CT         |
|          4 | SA           |      3 | CT         | R1 - CT - SA    |
|          5 | SA 10        |      2 | ST         | R1 - ST - SA 10 |
|          6 | SA 20        |      2 | ST         | R1 - ST - SA 20 |
|          7 | CA           |      2 | ST         | R1 - ST - CA    |
|          8 | SA           |      2 | ST         | R1 - ST - SA    |
|          9 | R2           |      0 | NULL       | R2              |
|         10 | ST           |      9 | R2         | R2 - ST         |
+------------+--------------+--------+------------+-----------------+

---

将连接添加到递归CTE的锚点：

;with cte as (
-- anchor elements: where Father = 0
  select 
      c.IDCategory
    , c.categoryDesc
    , c.Father
    , parentName = convert(varchar(32),null)
    , path = convert(varchar(128),c.categoryDesc)
    , cd.First
    , cd.Last
  from category c
    inner join CategoryDefinition cd
      on c.IdCategory=cd.IdCategory
  where Father = 0
  -- recursion begins here
  union all 
  select 
      c.IDCategory 
    , c.categoryDesc
    , c.Father
    , parentName = p.categoryDesc
    , path = convert(varchar(128),p.path+' - '+c.categoryDesc)
    , p.First
    , p.Last
  from category c
    inner join cte as p on c.Father= p.IDCategory
)
select cte.*
from cte 
--where IdCategory = 7
order by idCategory

rextester演示：http：//rextester.com/posvp81190

返回：

+------------+--------------+--------+------------+-----------------+-------+------+
| IDCategory | categoryDesc | Father | parentName |      path       | First | Last |
+------------+--------------+--------+------------+-----------------+-------+------+
|          1 | R1           |      0 | NULL       | R1              |     0 |  300 |
|          2 | ST           |      1 | R1         | R1 - ST         |     0 |  300 |
|          3 | CT           |      1 | R1         | R1 - CT         |     0 |  300 |
|          4 | SA           |      3 | CT         | R1 - CT - SA    |     0 |  300 |
|          5 | SA 10        |      2 | ST         | R1 - ST - SA 10 |     0 |  300 |
|          6 | SA 20        |      2 | ST         | R1 - ST - SA 20 |     0 |  300 |
|          7 | CA           |      2 | ST         | R1 - ST - CA    |     0 |  300 |
|          8 | SA           |      2 | ST         | R1 - ST - SA    |     0 |  300 |
|          9 | R2           |      0 | NULL       | R2              |   301 |  600 |
|         10 | ST           |      9 | R2         | R2 - ST         |   301 |  600 |
+------------+--------------+--------+------------+-----------------+-------+------+