SQL查询从不同的表获取完整的层次结构路径

这与问题非常相关SQL查询获取完整的层次结构路径 https://stackoverflow.com/q/3727543/93202.

唯一的区别是我的层次结构包含来自不同表的不同实体类型，例如：

1. Project
2. Phase
3. Folder
4. Document

我想选择所有文档及其完整路径（从项目级别到文件夹级别）。阶段和文件夹可以有子阶段和文件夹。

我对 SQL 很糟糕，只是不知道如何调整链接问题的答案以满足我的需求。

数据模型和示例数据

DECLARE @Project TABLE (ID INTEGER, Name VARCHAR(32))
DECLARE @Phase TABLE (ID INTEGER, Project_ID INTEGER, Parent_Phase_ID INTEGER, Name VARCHAR(32))
DECLARE @Folder TABLE (ID INTEGER, Phase_ID INTEGER, Parent_Folder_ID INTEGER, Name VARCHAR(32))
DECLARE @Document TABLE (ID INTEGER, Folder_ID INTEGER, Name VARCHAR(32))

INSERT INTO @Project VALUES (1, 'MyProject')

INSERT INTO @Phase
  SELECT 1, 1, 0, 'MyPhase1'
  UNION ALL SELECT 2, 1, 1, 'MyPhase1_1'
  UNION ALL SELECT 3, 1, 0, 'MyPhase2'

INSERT INTO @Folder
  SELECT 1, 1, 0, 'MyFolder'
  UNION ALL SELECT 2, 1, 1, 'MySubFolder'
  UNION ALL SELECT 3, 1, 0, 'AnotherFolder'

INSERT INTO @Document
  SELECT 1, 2, 'MyDocument'
  UNION ALL SELECT 2, 3, 'AnotherDocument'

Example

MyProject1 ................ Project
- MyPhase1 ................ Phase
  - MyPhase1_1 ............ Phase
    - MyFolder ............ Folder
      - MySubfolder ....... Folder
        - MyDocument ...... Document
    - AnotherFolder ....... Folder
      - AnotherDocument ... Document
- MyPhase2 ................ Phase

这种情况下理想的查询结果：

Document        | Path
MyDocument      | MyProject1/MyPhase1/MyPhase1_1/MyFolder/MySubfolder  
AnotherDocument | MyProject1/MyPhase1/MyPhase1_1/AnotherFolder

根据您所显示的内容，我假设您有 4 个表，它们之间存在一对多关系Project and Phase, Phase and Folder and Folder and Document.

那么您的 SQL 语句就可以像将它们连接在一起一样简单

SELECT *
FROM   Projects p
       INNER JOIN Phases ph ON ph.ProjectID = p.ProjectID
       INNER JOIN Folders f ON f.PhaseID = ph.PhaseID
       INNER JOIN Documents d ON d.FolderID = f.FolderID

我真的不认为有必要通过加入 CTE 来使其变得比需要的更困难