sql - MSSQL递归选择所有相关行

Question

我有一个定义/字段关系的层次结构，分布在 3 个表中:

CREATE TABLE ProductDefinition
(
  ProductDefinitionId int,
  Name nvarchar(10),
)

CREATE TABLE ProductDefinitionRelation
(
  ProductDefinitionId int,
  ParentProductDefinitionId int
)

CREATE TABLE ProductDefinitionField
(
  ProductDefinitionFieldId int,
  ProductDefinitionId int,
  Name nvarchar(10)
)

想法是，这允许 CMS 的编辑器跨对象共享公共(public)属性，从而节省管理员管理时间。

该结构来自第三方，因此无法更改，但我现在需要选择所有父属性(n 深)，因此如果您有 Grand Parent > Parent > Child 层次结构，则 Child 将具有定义在祖 parent / parent 。

我已经非常接近了，但是当 Child 没有任何属性时，问题就来了，它被 CTE 中的 INNER JOIN 过滤掉了。

有谁知道我如何在这个例子中避免这个 Child2 仍然需要拥有祖 parent / parent 的所有 Prop ？

我在这里设置了一个 fiddle :http://www.sqlfiddle.com/#!6/08dc3/4但我的代码如下:

INSERT INTO ProductDefinition
    VALUES (1,'G. Parent'),(2,'Parent'),(3,'Child1'),(4,'Child2'),(5,'Child3');

INSERT INTO ProductDefinitionField
VALUES
  (1,1,'G. Field'),
  (2,2,'P. Field'),
  (3,3,'C. Field'),
  (4,5,'C. Field');

INSERT INTO ProductDefinitionRelation
VALUES (2,1),(3,2),(4,2),(5,2);

WITH Fields
AS
    (
        SELECT
            pd.ProductDefinitionId
          , pd.Name AS [ProductDefinitionName]
          , pdf.ProductDefinitionFieldId
          , pdf.Name AS [ProductDefinitionFieldName]
        FROM
            ProductDefinition pd
                LEFT JOIN ProductDefinitionRelation pdr ON pd.ProductDefinitionId = pdr.ProductDefinitionId
                LEFT JOIN ProductDefinitionField pdf ON pd.ProductDefinitionId = pdf.ProductDefinitionId
        WHERE
            pdr.ProductDefinitionId IS NULL

    UNION ALL

        SELECT
            pd.ProductDefinitionId
          , pd.Name AS [ProductDefinitionName]
          , pdf.ProductDefinitionFieldId
          , pdf.Name AS [ProductDefinitionFieldName]
        FROM
            Fields f
                JOIN ProductDefinitionRelation pdr ON f.ProductDefinitionId = pdr.ParentProductDefinitionId
                JOIN ProductDefinition pd ON pdr.ProductDefinitionId = pd.ProductDefinitionId
                JOIN ProductDefinitionField pdf ON pd.ProductDefinitionId = pdf.ProductDefinitionId

    UNION ALL

        SELECT
            pd.ProductDefinitionId
            , pd.Name AS [ProductDefinitionName]
            , f.ProductDefinitionFieldId
            , f.ProductDefinitionFieldName AS [ProductDefinitionFieldName]
        FROM
            Fields f
                JOIN ProductDefinitionRelation pdr ON f.ProductDefinitionId = pdr.ParentProductDefinitionId
                JOIN ProductDefinition pd ON pdr.ProductDefinitionId = pd.ProductDefinitionId
                JOIN ProductDefinitionField pdf ON pd.ProductDefinitionId = pdf.ProductDefinitionId
)
SELECT DISTINCT
    *
FROM
    Fields
ORDER BY
    ProductDefinitionName

Answer 1

WITH FIELDS AS
    (
        SELECT
            pd.ProductDefinitionId
          , pd.ProductDefinitionId [ChildDefinitionID]  
          , pd.Name AS [ProductDefinitionName]
          , pdf.ProductDefinitionFieldId
          , pdf.Name AS [ProductDefinitionFieldName]
        FROM
            ProductDefinition pd
            LEFT JOIN ProductDefinitionField pdf ON pd.ProductDefinitionId = pdf.ProductDefinitionId

    UNION ALL

        SELECT
            f.ProductDefinitionId
          , pd.ProductDefinitionId [ChildDefinitionID]
          , f.ProductDefinitionName AS [ProductDefinitionName]
          , pdf.ProductDefinitionFieldId
          , pdf.Name AS [ProductDefinitionFieldName]
        FROM
            Fields f
                JOIN ProductDefinitionRelation pdr ON f.ChildDefinitionID = pdr.ProductDefinitionId  
                JOIN ProductDefinition pd ON pdr.ParentProductDefinitionId = pd.ProductDefinitionId                              
                JOIN ProductDefinitionField pdf ON pd.ProductDefinitionId = pdf.ProductDefinitionId    
)
SELECT DISTINCT
    *
FROM
    Fields
ORDER BY
    ProductDefinitionName

http://www.sqlfiddle.com/#!6/89721b/11

想法:
使用两个字段来跟踪递归:

• ProductDefinitionId 保存字段最终属于的id
• ChildDefinitionID 保存 child 的 id，在递归期间测试父关系；最初这是 child 的 ID