Как получить иерархический CTE в SQL Server для фильтрации с помощью родительской и дочерней логики

У меня есть неприятная проблема с иерархическим CTE и какой-то странной логикой, которую нам нужно решить, и я действительно надеюсь, что кто-то может помочь, указав, что я делаю неправильно, чтобы решить этот сценарий с помощью CTE.

Вот иерархические данные, с которыми мы имеем дело в этом примере: введите здесь описание изображения

Это проблемный SQL, за которым следует описание проблемы и операторы SQL для создания тестовой таблицы с данными:

    DECLARE @UserId nvarchar(50);
    SET @UserId = 'A';

    DECLARE @StatusType int;
    SET @StatusType = '2';

     ;WITH recursiveItems (Id, Depth)
     AS
     (
        SELECT Id, 0 AS Depth 
        FROM dbo.CteTest 
        WHERE UserId = @UserId 
                    --AND StatusType = @StatusType
                    -- This would also be incorrect for the issue
        AND ParentId IS NULL
        UNION ALL
        SELECT dbo.CteTest.Id, Depth + 1 
        FROM dbo.CteTest 
            INNER JOIN recursiveItems 
                ON dbo.CteTest.ParentId = recursiveItems.Id
        WHERE UserId = @UserId 
        AND StatusType = @StatusType
     )

    SELECT A.*, recursiveItems.Depth
    FROM recursiveItems
    INNER JOIN dbo.CteTest A WITH(NOLOCK) ON
        recursiveItems.Id = A.Id
        ORDER BY A.Id

Это не возвращает нужные данные. Данные, которые в настоящее время возвращаются, находятся в НЕПРАВИЛЬНОМ разделе изображения ниже. Строка с идентификатором 10 — это строка, которую мы хотим опустить.

По сути, логика должна заключаться в том, что любая родительская запись (запись с дочерними элементами), у которой тип статуса любого из ее дочерних элементов равен 2, должна быть возвращена вместе с ее дочерними элементами. В примере это строки с идентификаторами: 1, 5, 6, 7, 9.

В настоящее время CTE/SQL/Code возвращает ВСЕ родительские записи, несмотря ни на что,

Запись с идентификатором 1 должна быть возвращена, даже если ее тип статуса равен 1, поскольку по крайней мере один из ее дочерних элементов, их дочерние элементы, внуки и т. д. имеют тип статуса, равный 2.

Запись с идентификатором 10 не должна быть возвращена, поскольку она не имеет статуса, равного 2, или каких-либо дочерних элементов. Если запись имеет тип состояния 2, когда у нее нет дочерних записей, она также должна быть возвращена.

Пример нежелательных и желаемых результатов

Это DDL для создания тестовой таблицы, которая помогает показать проблему:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CteTest](
[Id] [int] IDENTITY(1,1) NOT NULL,
[StatusType] [int] NOT NULL,
[UserId] [nvarchar](50) NOT NULL,
[ParentId] [int] NULL,
 CONSTRAINT [PK_CteTest] PRIMARY KEY CLUSTERED 
(
[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,         ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Это исходные данные для таблицы, которые могут продемонстрировать проблему:

INSERT INTO [dbo].[CteTest]([StatusType],[UserId],[ParentId]) VALUES (1,'A',NULL)
INSERT INTO [dbo].[CteTest]([StatusType],[UserId],[ParentId]) VALUES (1,'B',NULL)
INSERT INTO [dbo].[CteTest]([StatusType],[UserId],[ParentId]) VALUES (2,'B',NULL)
INSERT INTO [dbo].[CteTest]([StatusType],[UserId],[ParentId]) VALUES (1,'A',1)
INSERT INTO [dbo].[CteTest]([StatusType],[UserId],[ParentId]) VALUES (2,'A',1)
INSERT INTO [dbo].[CteTest]([StatusType],[UserId],[ParentId]) VALUES (2,'A',5)
INSERT INTO [dbo].[CteTest]([StatusType],[UserId],[ParentId]) VALUES (2,'A',6)
INSERT INTO [dbo].[CteTest]([StatusType],[UserId],[ParentId]) VALUES (3,'A',6)
INSERT INTO [dbo].[CteTest]([StatusType],[UserId],[ParentId]) VALUES (2,'A',NULL)
INSERT INTO [dbo].[CteTest]([StatusType],[UserId],[ParentId]) VALUES (4,'A',NULL)
INSERT INTO [dbo].[CteTest]([StatusType],[UserId],[ParentId]) VALUES (3,'A',10)

sql sql-server tsql common-table-expression
person jon333    schedule 28.06.2012    source источник
comment
Хотелось бы, чтобы у меня было время, чтобы наметить, как я это сделаю, но в основном я бы добавил в ваш вывод столбец, который является корневым идентификатором. Затем я бы присоединил всю эту рекурсию к себе, основываясь на корневом идентификаторе. Вторая версия соединения будет отдельной и будет отфильтрована, чтобы включить строки, в которых корневой идентификатор не равен идентификатору, а статус = 2. Это может быть неэффективно, но это отправная точка.   -  person Triple Gilaman    schedule 28.06.2012

Ответы (1)


arrow_upward
4
arrow_downward

Проблема в том, что ваш базовый вариант включает все нулевые (безродительские) элементы, и нет возможности отфильтровать их позже.

Поскольку вы ищете только элементы с определенным statustype, вы можете захотеть провести рефакторинг CTE; Вместо того, чтобы базовый случай был корневыми значениями, вы можете использовать все элементы с заданным statustype, а затем рекурсивно найти родителей. В приведенном ниже решении у меня есть глубина - отрицательное число для расстояния от элемента со значением 2 в данном дереве (поэтому отрицательная высота вместо глубины.).

DECLARE @UserId nvarchar(50);
SET @UserId = 'A';

DECLARE @StatusType int;
SET @StatusType = '2';

WITH recursiveItems (Id, ParentID, Depth)
 AS
 (
    SELECT Id, ParentID, 0 AS Depth 
    FROM dbo.CteTest 
    WHERE UserId = @UserId AND StatusType = @StatusType
    UNION ALL
    SELECT dbo.CteTest.Id, CteTest.ParentID, Depth - 1 
    FROM dbo.CteTest 
        INNER JOIN recursiveItems 
            ON dbo.CteTest.Id = recursiveItems.ParentId
    WHERE UserId = @UserId 
 )
     SELECT A.Id, A.StatusType, A.UserId, A.ParentId, min(recursiveItems.Depth)
FROM recursiveItems
INNER JOIN dbo.CteTest A WITH(NOLOCK) ON
    recursiveItems.Id = A.Id
    group by A.Id, A.StatusType, A.UserId, A.ParentId
    ORDER BY A.Id
person David Manheim    schedule 29.06.2012
comment
Привет, Дэвид, большое спасибо. Есть ли способ сделать так, чтобы глубина не была отрицательной и была глубиной в иерархии (элементы без ParentID получают глубину 0). - person jon333; 08.07.2012