具有多对多关系的两个表的CTE递归查询

debugcn 发表于 Dev

明信片乔

我有三个表，我需要一个查询才能正确返回结果。

第一个表是包含父类别和子类别的类别表。该表允许所有级别的子表，包括子类别的子表。

第二个表是可以属于类别的文件表。这与类别表有很多关系。这些文件也可以属于包含子类别的类别。

第三个表创建文件和类别之间的多对多关系。

第一个表是Categories：

CREATE TABLE Categories (
  category_id INT NOT NULL PRIMARY KEY,
  category_name varchar(20) NOT NULL,
  parent_id INT NOT NULL
);

第二张表是 Files

CREATE TABLE Files (
  file_id INT NOT NULL PRIMARY KEY,
  file_name varchar(20) NOT NULL
);

第三个表将文件链接到类别。文件可以属于任何类别，并且可以属于多个类别

CREATE TABLE Category_File (
  category_id INT NOT NULL,
  file_id INT NOT NULL
);

这些表填充如下：

INSERT INTO Categories (category_id, category_name, parent_id) VALUES
(1, 'Cat1', 0),(2, 'Cat2', 0),(3, 'Cat3', 1),
(4, 'Cat4', 2),(5, 'Cat5', 1),(6, 'Cat6', 0),
(7, 'Cat7', 5),(8, 'Cat8', 4),(9, 'Cat9', 7);

INSERT INTO Files (file_id, file_name) VALUES
(1, 'File1'),(2, 'File2'),(3, 'File3'),
(4, 'File4'),(5, 'File5'),(6, 'File6');

INSERT INTO Category_File (category_id, file_id) VALUES
(3, 1),(4, 2),(5, 3),
(9, 6),(7, 2),(5, 4),
(8, 4),(6, 1),(3, 5);

我需要返回以显示按名称排序的类别，然后显示按名称排序的文件。因此，查询具有所有列的所有记录将导致

Result Set 1
[
category_id,    category_name,  file_id,    file_name   parent_id
1               Cat1            Null        Null        0
3               Cat3            Null        Null        1
3               Cat3            1           File1       Null
3               Cat3            5           File5       Null
5               Cat5            Null        Null        1   
5               Cat5            3           File3       Null    
5               Cat5            4           File4       Null
7               Cat7            Null        Null        5
7               Cat7            2           File2       Null
9               Cat9            Null        Null        7
9               Cat9            6           File6       Null
2               Cat2            Null        Null        0
4               Cat4            Null        Null        2
4               Cat4            2           File2       Null
8               Cat8            Null        Null        4
8               Cat8            4           File4       Null
6               Cat6            Null        Null        0
6               Cat6            1           File1       Null
]

此查询应该工作。它基于@Oleg给出的答案，但有一些简化，并添加了一个level属性以确保正确的排序。

我将其设为社区Wiki，因为我不想因为别人的工作而受到赞誉...

;with cte as
(
    select c.category_id, c.category_name, c.parent_id, cast(category_id as varchar(max)) as lvl
    from categories c where parent_id = 0 
    union all
    select ch.category_id, ch.category_name, ch.parent_id, c.lvl + ',' + cast(ch.category_id as varchar(max)) as lvl 
    from cte c join categories ch on ch.parent_id = c.category_id
)

select category_id, category_name, file_id, file_name, parent_id 
from (
    select c.category_id, c.category_name, null as file_id, null as file_name, c.parent_id, lvl
    from cte c  
    union all       
    select c.category_id, c.category_name, f.file_id, f.file_name, null, lvl
    from cte c
    join category_file cf on cf.category_id = c.category_id
    join files f on f.file_id = cf.file_id 
) a 
order by lvl, category_name, file_name

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。