父子关系的 SQL 查询递归

Question

如何使用 CTE 递归仅查询包含文件表中的文件的文件夹？

[Folder_Table]

folder_id |parent_id |folder_name
1         |0         |Folder1
2         |1         |Folder2
3         |1         |Folder3
4         |2         |Folder4
5         |2         |Folder5
6         |3         |Folder6
7         |6         |Folder7
8         |0         |Folder8
9         |8         |Folder9
10        |8         |Folder10

[File_Table]

file_id   |folder_id |file_name
1         |4         |File1
2         |4         |File2
3         |5         |File3
4         |5         |File4
5         |9         |File5
6         |10        |File6

_______________________________________
Result (for all folders)

[+] Folder1
    [+] Folder2
        [+] Folder4
              File1
              File2
        [+] Folder5
              File3
              File4
    [+] Folder3
        [+] Folder6
            [+] Folder7
[+] Folder8
    [+] Folder9
          File5
    [+] Filder10
          File6
_______________________________________

我只想从文件夹表中检索在链末尾有文件的行。所以在这种情况下查询应该给我:

folder_id |parent_id |folder_name
1         |0         |Folder1
2         |1         |Folder2
4         |2         |Folder4
5         |2         |Folder5
8         |0         |Folder8
9         |8         |Folder9
10        |8         |Folder10

由于Folder7不包含任何文件，所以我不希望Folder7、Folder6或Folder3在结果集中返回。

Answer 1

这可能不是最优雅的解决方案:

WITH cte(folder_id, parent_id, name)
AS
(
    select [folder].folder_id, parent_id, name 
    from [folder] 
        join [file] on [folder].[folder_id] = [file].[folder_id]
    union all
    select [folder].[folder_id], [folder].parent_id, [folder].name 
    from cte 
        join [folder] on cte.parent_id = folder.folder_id

)
SELECT distinct * FROM cte