代码之家 › 专栏 › 技术社区 › BennoDual

不使用CTE选择分层数据

hierarchical-data tsql sql-server sql

BennoDual · 技术社区 · 6 年前

我有下表:

CREATE TABLE [dbo].[ProductHierarchy]
(
    [ProductHierarchyID] INT NOT NULL
          PRIMARY KEY CLUSTERED IDENTITY(1, 1),
    [ProductID] INT NOT NULL ,
    [ParentProductID] INT NULL
);

有了这些数据:

INSERT INTO [dbo].[ProductHierarchy] ([ProductID], [ParentProductID])
VALUES (1, NULL), (2, 1), (3, 1), (4, 2), (5, 4), (6, 4), (7, 4);

现在,我可以使用这样的CTE进行分层查询:

WITH [CTE_Products] ([ProductID], [ParentProductID], [ProductLevel]) AS 
(
    SELECT
        [ProductID],
        [ParentProductID],
        0 AS [ProductLevel]
    FROM 
        [dbo].[ProductHierarchy]
    WHERE 
        [ParentProductID] IS NULL

    UNION ALL

    SELECT  
        [pn].[ProductID],
        [pn].[ParentProductID],
        [p1].[ProductLevel] + 1
    FROM 
        [dbo].[ProductHierarchy] AS [pn]
    INNER JOIN 
        [CTE_Products] AS [p1] ON [p1].[ProductID] = [pn].[ParentProductID]
)
SELECT  
    [ProductID],
    [ParentProductID],
    [ProductLevel]
FROM 
    [CTE_Products]
ORDER BY 
    [ParentProductID];

但是,如果不使用CTE,我如何用一个SQL语句达到相同的目标呢?这可能吗?

2 回复 | 直到 6 年前

Lukasz Szozda 6 年前

CTE允许解决这种递归查询。如果事先知道深度级别,可以“展开”递归部分:

SELECT p0.ProductId, p0.ParentProductID, 0 AS ProductLevel
FROM [dbo].[ProductHierarchy] p0
WHERE p0.ParentProductId IS NULL
UNION ALL
SELECT p1.ProductId, p1.ParentProductID, 1 AS ProductLevel
FROM [dbo].[ProductHierarchy] p1
JOIN (SELECT p0.ProductId, p0.ParentProductID, 0 AS ProductLevel
      FROM [dbo].[ProductHierarchy] p0
      WHERE p0.ParentProductId IS NULL) p0
  ON p1.ParentProductId = p0.ProductId
UNION ALL
SELECT p2.ProductId, p2.ParentProductID, 2 AS ProductLevel
FROM [dbo].[ProductHierarchy] p2
JOIN (SELECT p1.ProductId, p1.ParentProductID, 1 AS ProductLevel
      FROM [dbo].[ProductHierarchy] p1
      JOIN (SELECT p0.ProductId, p0.ParentProductID, 0 AS ProductLevel
      FROM [dbo].[ProductHierarchy] p0
      WHERE p0.ParentProductId IS NULL) p0
        ON p1.ParentProductId = p0.ProductId) p1
  ON p2.ParentProductId = p1.ProductId
UNION ALL
SELECT p3.ProductId, p3.ParentProductID, 3 AS ProductLevel
FROM [dbo].[ProductHierarchy] p3
JOIN  (SELECT p2.ProductId, p2.ParentProductID, 2 AS ProductLevel
       FROM [dbo].[ProductHierarchy] p2
       JOIN (SELECT p1.ProductId, p1.ParentProductID, 1 AS ProductLevel
             FROM [dbo].[ProductHierarchy] p1
             JOIN (SELECT p0.ProductId, p0.ParentProductID, 0 AS ProductLevel
                    FROM [dbo].[ProductHierarchy] p0
                    WHERE p0.ParentProductId IS NULL) p0
              ON p1.ParentProductId = p0.ProductId) p1
        ON p2.ParentProductId = p1.ProductId) p2
  ON p3.ParentProductId = p2.ProductId;

DBFiddle Demo

Ronen Ariely 6 年前

很好的一天,

在我开始之前!如果这是您的要求,强烈建议重新考虑您的数据库结构。使用递归cte(或者可能是大多数其他递归解决方案)意味着您使用的是rdbms中不推荐使用的类循环解决方案(这些解决方案对于处理数据集最为优化)。如果您需要使用递归数据,那么您应该考虑例如使用hierarchyid数据类型(或者实现类似或更好的东西-是的,例如有更好的基于范围的层次结构算法,但这些算法在论坛的短消息中更复杂)。此外,您应该有一个很好的理由在不使用递归cte的情况下这样做,因为对于当前的结构,递归cte应该在大多数情况下提供最佳性能。

话虽如此,既然你要求这个解决方案,那么请检查这个查询是否能解决你的需求

-- DDL+DML
drop table if exists [ProductHierarchy];
CREATE TABLE [dbo].[ProductHierarchy]
(
    [ProductID] INT NOT NULL ,
    [ParentProductID] INT NULL
);

INSERT  [dbo].[ProductHierarchy]
        ( [ProductID], [ParentProductID] )
VALUES  ( 1, NULL ),
        ( 2, 1 ),
        ( 3, 1 ),
        ( 4, 2 ),
        ( 5, 4 ),
        ( 6, 4 ),
        ( 7, 4 ),
        ( 8, 2)
GO

-- Solution?
SELECT distinct [level], [ProductID] FROM (
    -- I recomend to check this inner quesry first
    -- in order to understand the logic
    select 
        A.ProductID as L1,
        B.ProductID as L2,
        C.ProductID as L3,
        D.ProductID as L4 
    from (
        select ProductID
        from [ProductHierarchy]
        where ParentProductID is null
    ) A
    -- For each level that we wany to get
    -- we will add another sub-query
    -- like bellow
    left join(
        SELECT [ProductID], [ParentProductID]
        FROM [ProductHierarchy]
    ) B 
        ON B.[ParentProductID] = A.[ProductID]
    left join(
        SELECT [ProductID], [ParentProductID]
        FROM [ProductHierarchy]
    ) C 
        ON C.[ParentProductID] = B.[ProductID]
    left join(
        SELECT [ProductID], [ParentProductID]
        FROM [ProductHierarchy]
    ) D 
        ON D.[ParentProductID] = C.[ProductID]
) T
UNPIVOT (
    ProductID for [Level] in (L1,L2,L3,L4)
) as a

注意:比较我的解决方案( 假设它符合你的需要 )对于Lukasz szozda,根据执行计划(我没有检查IO或时间),我的解决方案将仅使用22%,而Lukasz解决方案使用78%(在SQL Server 2017上测试)