SQL查询以从不同的表中获取完整的层次结构路径 [英] SQL query to get full hierarchy path from different tables

问题描述

这与问题 SQL查询以获取完整的层次结构路径非常相关.

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

1. 项目
2. 阶段
3. 文件夹
4. 文档

我想选择所有文档及其完整路径(从项目级别到文件夹级别).阶段和文件夹可以包含子阶段和文件夹.

我对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'

示例

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和Folder and Document之间具有一对多的关系. /p>

然后，您的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来使其变得比需要更困难

This is very relevant to the question SQL query to get full hierarchy path.

The only difference is that my hierarchy contains different entity types from different tables, for example:

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

I want to select all documents along with their full paths (from poject level to folder level). Phases and folders can have subphases and folders.

I'm horrible at SQL and just can't figure out how to adapt the answer to the linked question to suit my needs.

Data model and example data

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

Ideal query results in this case:

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

解决方案

From what you have shown, I would assume you have 4 tables with a one to many relation between Project and Phase, Phase and Folder and Folder and Document.

Your SQL Statement then could be as simple as joining them all together

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

I really don't see a need yet to make it more difficult than need be by throwing in CTE's

这篇关于SQL查询以从不同的表中获取完整的层次结构路径的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！