ГДЕ столбец NOT LIKE не работает корректно с рекурсивным cte

Это код SQL Server.

Допустим, у вас есть таблица с тремя столбцами. Столбец 1 называется Monster, столбец 2 - Level, а столбец 3 - BodyType. Уровень показывает, насколько могущественен монстр, а BodyType - тип тела.

Моя схема:

CREATE TABLE YourTable
    ([Monster] nvarchar(max), [Level] int, [BodyType] nvarchar(max))
;

INSERT INTO YourTable
    ([Monster], [Level], [BodyType])
VALUES
    ('Small Beast', 300, 'Scaly'),
    ('Large Beast', 700, 'Slimy'),
    ('Small Dragon', 350, 'Fiery'),
    ('Large Dragon', 800, 'Slimy')
;

У меня есть команда sql, чтобы найти все возможные комбинации монстров. Он использует рекурсивный cte, потому что количество монстров в таблице может колебаться (поэтому я могу добавить больше монстров позже). Команда также захватывает общую сумму Уровня монстров, которые объединяются. Команда также выводит только комбинации монстров, которые подпадают под определенное значение общей суммы. В этом примере это общая сумма 1500. Все до этого момента работает как надо.

Моя команда sql:

;WITH cte AS (
SELECT  Monster, 
        [Level],
        BodyType,
        1 as l
FROM YourTable
UNION ALL


SELECT  c1.Monster+','+c2.Monster,
        c1.[Level]+c2.[Level],
        c1.BodyType+','+c2.BodyType,
        c1.l+1
FROM cte c1
CROSS JOIN YourTable c2
WHERE c1.Monster NOT LIKE '%'+c2.Monster+'%'
)


SELECT *
FROM cte
WHERE cte.Level < 1500
ORDER BY l
OPTION (MAXRECURSION 0)

И правильный вывод:

1   Small Beast 300 Scaly   1
2   Large Beast 700 Slimy   1
3   Small Dragon    350 Fiery   1
4   Large Dragon    800 Slimy   1
5   Large Dragon,Small Beast    1100    Slimy,Scaly 2
6   Large Dragon,Small Dragon   1150    Slimy,Fiery 2
7   Small Dragon,Small Beast    650 Fiery,Scaly 2
8   Small Dragon,Large Beast    1050    Fiery,Slimy 2
9   Small Dragon,Large Dragon   1150    Fiery,Slimy 2
10  Large Beast,Small Beast 1000    Slimy,Scaly 2
11  Large Beast,Small Dragon    1050    Slimy,Fiery 2
12  Small Beast,Large Beast 1000    Scaly,Slimy 2
13  Small Beast,Small Dragon    650 Scaly,Fiery 2
14  Small Beast,Large Dragon    1100    Scaly,Slimy 2
15  Small Beast,Large Dragon,Small Dragon   1450    Scaly,Slimy,Fiery   3
16  Small Beast,Small Dragon,Large Beast    1350    Scaly,Fiery,Slimy   3
17  Small Beast,Small Dragon,Large Dragon   1450    Scaly,Fiery,Slimy   3
18  Small Beast,Large Beast,Small Dragon    1350    Scaly,Slimy,Fiery   3
19  Large Beast,Small Dragon,Small Beast    1350    Slimy,Fiery,Scaly   3
20  Large Beast,Small Beast,Small Dragon    1350    Slimy,Scaly,Fiery   3
21  Small Dragon,Large Dragon,Small Beast   1450    Fiery,Slimy,Scaly   3
22  Small Dragon,Large Beast,Small Beast    1350    Fiery,Slimy,Scaly   3
23  Small Dragon,Small Beast,Large Beast    1350    Fiery,Scaly,Slimy   3
24  Small Dragon,Small Beast,Large Dragon   1450    Fiery,Scaly,Slimy   3
25  Large Dragon,Small Dragon,Small Beast   1450    Slimy,Fiery,Scaly   3
26  Large Dragon,Small Beast,Small Dragon   1450    Slimy,Scaly,Fiery   3

Проблема, с которой я сталкиваюсь, заключается в том, что я добавляю предложение Where, чтобы возвращать только монстров, которые не имеют определенного типа тела (столбец BodyType). Та часть кода сверху, когда модифицируется для достижения этой цели:

;WITH cte AS (
SELECT  Monster, 
        [Level],
        BodyType,
        1 as l
FROM YourTable
WHERE BodyType NOT LIKE 'Fiery' AND BodyType NOT LIKE 'Slimy'
UNION ALL

Вывод становится следующим, что неверно, поскольку он по-прежнему включает в себя типы телосложения Слимы и Огненные:

    Monster Level   BodyType    l
1   Small Beast 300 Scaly   1
2   Small Beast,Large Beast 1000    Scaly,Slimy 2
3   Small Beast,Small Dragon    650 Scaly,Fiery 2
4   Small Beast,Large Dragon    1100    Scaly,Slimy 2
5   Small Beast,Large Dragon,Small Dragon   1450    Scaly,Slimy,Fiery   3
6   Small Beast,Small Dragon,Large Beast    1350    Scaly,Fiery,Slimy   3
7   Small Beast,Small Dragon,Large Dragon   1450    Scaly,Fiery,Slimy   3
8   Small Beast,Large Beast,Small Dragon    1350    Scaly,Slimy,Fiery   3

Вывод, кажется, работает частично, так как Large Beast является Slimy, и он игнорировал его в первый раз, но я подозреваю, что он игнорирует предложение NOT LIKE при переходе через уровни BodyType, и поэтому он не игнорирует Large Beast при последующих находках.

Источник

3 ответа

Решение

Если я правильно понимаю, вы можете попробовать 2 подхода ниже:

1. Фильтр крови после recursive cte

;WITH cte AS (
SELECT  Monster, 
        [Level],
        BodyType,
        1 as l
FROM YourTable
UNION ALL

SELECT  c1.Monster+','+c2.Monster,
        c1.[Level]+c2.[Level],
        c1.BodyType+','+c2.BodyType,
        c1.l+1
FROM cte c1
CROSS JOIN YourTable c2
WHERE c1.Monster NOT LIKE '%'+c2.Monster+'%'
)
SELECT *
FROM cte
WHERE cte.Level < 1500
AND ',' + cte.BodyType ',' + NOT LIKE '%,Fiery,%' 
AND ',' + cte.BodyType ','  + NOT LIKE '%,Slimy,%'
ORDER BY l
OPTION (MAXRECURSION 0)

2. Используйте второй cte для фильтрации перед рекурсивным cte

;WITH temp AS
(
    SELECT * 
    FROM YourTable
    WHERE   BodyType != 'Fiery' 
            AND BodyType != 'Slimy'
)
,cte AS (
SELECT  Monster, 
        [Level],
        BodyType,
        1 as l
FROM temp
UNION ALL

SELECT  c1.Monster+','+c2.Monster,
        c1.[Level]+c2.[Level],
        c1.BodyType+','+c2.BodyType,
        c1.l+1
FROM cte c1
CROSS JOIN temp c2
WHERE c1.Monster NOT LIKE '%'+c2.Monster+'%'
)
SELECT *
FROM cte
WHERE cte.Level < 1500   
ORDER BY l
OPTION (MAXRECURSION 0)
Источник

Я думаю, что нашел решение. Выходные данные кажутся правильными:

SELECT  c1.Monster+','+c2.Monster,
        c1.[Level]+c2.[Level],
        c1.BodyType+','+c2.BodyType,
        c1.l+1
FROM cte c1
CROSS JOIN YourTable c2
WHERE (c1.Monster NOT LIKE '%'+c2.Monster+'%') AND (c1.BodyType NOT LIKE 'Scaly' AND c2.BodyType NOT LIKE 'Scaly') AND (c1.BodyType NOT LIKE 'Fiery' AND c2.BodyType NOT LIKE 'Fiery') 
)

Я вынул неработающее предложение Where и просто добавил NOT LIKE в предложение Where после CROSS JOIN из исходного кода.

Просто не уверен, что это не лучшая практика или что-то может сломаться, кто-то хочет присоединиться? Спасибо.

Изменить: Мое решение имеет одну проблему, где первая итерация будет включать игнорируемые "BodyTypes"

Источник

Тебе нужно AND в твоей логике нет OR:

WHERE BodyType NOT LIKE 'Fiery' AND BodyType NOT LIKE 'Slimy'

BodyType "Огненный" не похож на "Слимы". Итак, это соответствует второму условию. Обратите внимание, что если вы используете LIKEтогда хочешь AND,

Источник
Другие вопросы по тегам