父子关系的SQL查询递归

问题描述：

如何仅使用CTE递归查询文件表中具有文件的文件夹？父子关系的SQL查询递归

[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在结果集返回。

烨一CTE是要走的路。你试过了吗？ – cadrell0 2012-02-09 15:05:54

CTE对我来说是一个新概念。我一直在尝试但没有成功。 – TroyS 2012-02-09 15:08:42

答

的方式略有不同，包括所有列只是在最后

;WITH q AS (
    SELECT ft.folder_id 
    FROM File_Table ft 
      INNER JOIN Folder_Table f ON f.folder_id = ft.folder_id 
    UNION ALL 
    SELECT f.parent_id 
    FROM Folder_Table f 
      INNER JOIN q ON q.folder_id = f.folder_id 
) 
SELECT DISTINCT f.folder_id 
     , f.parent_id 
     , f.folder_name 
     , f.is_active 
FROM q 
     INNER JOIN Folder_Table f ON f.folder_id = q.folder_id 
WHERE f.is_active = 1

测试脚本

;WITH Folder_Table (folder_id, parent_id, folder_name, is_active) AS (
    SELECT * FROM (VALUES 
    (1, 0, 'Folder1', 1) 
    , (2, 1, 'Folder2', 0) 
    , (3, 1, 'Folder3', 1) 
    , (4, 2, 'Folder4', 0) 
    , (5, 2, 'Folder5', 0) 
    , (6, 3, 'Folder6', 1) 
    , (7, 6, 'Folder7', 1) 
    , (8, 0, 'Folder8', 1) 
    , (9, 8, 'Folder9', 1) 
    , (10, 8, 'Folder10', 1) 
) a (b, c, d, e) 
) 
, File_Table (filed_id, folder_id, file_name) AS (
    SELECT * FROM (VALUES 
    (1, 4, 'File1') 
    , (2, 4, 'File2') 
    , (3, 5, 'File3') 
    , (4, 5, 'File4') 
    , (5, 9, 'File5') 
    , (6, 10, 'File6') 
) a (b, c, d) 
) 
, q AS (
    SELECT ft.folder_id 
    FROM File_Table ft 
      INNER JOIN Folder_Table f ON f.folder_id = ft.folder_id 
    UNION ALL 
    SELECT f.parent_id 
    FROM Folder_Table f 
      INNER JOIN q ON q.folder_id = f.folder_id 
) 
SELECT DISTINCT f.folder_id 
     , f.parent_id 
     , f.folder_name 
     , f.is_active 
FROM q 
     INNER JOIN Folder_Table f ON f.folder_id = q.folder_id 
WHERE f.is_active = 1

谢谢，这给了我在原始问题样本中寻找的确切结果。 – TroyS 2012-02-09 16:07:21

在另一个说明中，我还在foler表中将is_active列设置为0或1. 1 =有效。所以我想只显示那些is_active = 1。我用几种不同的方式将它添加到查询中，但无法使其工作。 – TroyS 2012-02-09 17:02:14

如果文件夹1处于活动状态，文件夹2处于非活动状态并且文件夹4处于活动状态，您会期望什么？ – 2012-02-09 18:04:46

答

它可能不是最完美的解决方案：

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

+1，我会改变的是格式化和使用OP使用的名称。 – 2012-02-09 15:48:48

父子关系的SQL查询递归

相关推荐