Как я могу структурировать запрос, чтобы дать мне только строки, которые соответствуют всем значениям в списке идентификаторов CSV в T-SQL

Я создал простой пример (надеюсь, гораздо больше удовольствия, чем мои фактические данные), чтобы лучше выразить свой вопрос:

CREATE TABLE SUPER_HERO
(   ID INT,
    NAME VARCHAR(50)
)

INSERT INTO SUPER_HERO VALUES (1, 'Storm')
INSERT INTO SUPER_HERO VALUES (2, 'Silver Surfer')
INSERT INTO SUPER_HERO VALUES (3, 'Spider Man')

CREATE TABLE SKILL
(   ID INT,
    NAME VARCHAR(50)
)

INSERT INTO SKILL VALUES (1, 'Flight')
INSERT INTO SKILL VALUES (2, 'Weather Control')
INSERT INTO SKILL VALUES (3, 'Super Speed')

CREATE TABLE SUPER_HERO_SKILL
(   SUPER_HERO_ID INT,
    SKILL_ID INT
)

INSERT INTO SUPER_HERO_SKILL VALUES (1, 1) --Storm has Flight
INSERT INTO SUPER_HERO_SKILL VALUES (1, 2) --Storm has Weather Control
INSERT INTO SUPER_HERO_SKILL VALUES (2, 1) --Silver Surfer has Flight
INSERT INTO SUPER_HERO_SKILL VALUES (2, 3) --Silver Surfer has Super Speed
INSERT INTO SUPER_HERO_SKILL VALUES (3, 3) --Spider Man has Super Speed

Пример плохого запроса (не показывая желаемые результаты):

DECLARE @DELIMITER CHAR = ','
DECLARE @CSV_STRING VARCHAR(20) = '1,3'

SELECT 
    SUPER_HERO_NAME =   SUPER_HERO.NAME,
    SKILL_NAME      =   SKILL.NAME
FROM 
    SUPER_HERO
    JOIN SUPER_HERO_SKILL ON  SUPER_HERO_SKILL.SUPER_HERO_ID = SUPER_HERO.ID
    JOIN SKILL ON SUPER_HERO_SKILL.SKILL_ID = SKILL.ID
    JOIN dbo.Split(@CSV_STRING, @DELIMITER) SPLIT  ON SPLIT.ITEMS = SKILL.ID

Что я хотел бы видеть:
Когда DECLARE @CSV_STRING VARCHAR(20) = '1,3' я должен видеть только "Silver Surfer", так как он единственный, у кого есть навыки 1 и 3, которые коррелируют с Flight and Super Speed.

Когда DECLARE @CSV_STRING VARCHAR(20) = '1,2,3', я не должен видеть героев в моей юниверсе, так как нет ни одного определенного, чтобы иметь все перечисленные навыки.

Должно быть что-то простое, что мне не хватает. Я попытался структурировать запрос по-разному. Я представил здесь простейшую форму, чтобы не усложнять представление проблемы.

Примечание. Я использую функцию, которая действует как Split на основе переданного разделителя.

Ответ 1

Используйте следующую функцию сплиттера, которая возвращает столбец int. Так что легко проверить счет в предложении HAVING.

CREATE FUNCTION [dbo].[DelimitedParamParser]( @DelimitedIds VARCHAR(MAX), @Delimiter CHAR(1)) 
RETURNS @IdsTable 
TABLE ( Id INT ) 
AS BEGIN

DECLARE @Length INT,
        @Index INT,
        @NextIndex INT

SET @Length = DATALENGTH(@DelimitedIds)
SET @Index = 0
SET @NextIndex = 0


WHILE (@Length > @Index )
BEGIN
    SET @NextIndex = CHARINDEX(@Delimiter, @DelimitedIds, @Index)
    IF (@NextIndex = 0 ) SET @NextIndex = @Length + 2
        INSERT @IdsTable SELECT SUBSTRING( @DelimitedIds, @Index, @NextIndex - @Index )
    SET @index = @nextindex + 1
END
 RETURN
END

Это работает, имейте в виду, чтобы дать дополнительную запятую в конце.

DECLARE @DELIMITER CHAR = ','
DECLARE @CSV_STRING VARCHAR(20) = '1,3,'

SELECT Distinct SUPER_HERO.NAME, SKILL.NAME
FROM 
    SUPER_HERO
    INNER JOIN SUPER_HERO_SKILL ON  SUPER_HERO_SKILL.SUPER_HERO_ID = SUPER_HERO.ID
    INNER JOIN SKILL ON SUPER_HERO_SKILL.SKILL_ID = SKILL.ID
    WHERE SUPER_HERO.ID IN
    (
    SELECT SUPER_HERO_SKILL.SUPER_HERO_ID   
    FROM 
        SUPER_HERO
        INNER JOIN SUPER_HERO_SKILL ON  SUPER_HERO_SKILL.SUPER_HERO_ID = SUPER_HERO.ID
        INNER JOIN SKILL ON SUPER_HERO_SKILL.SKILL_ID = SKILL.ID
        INNER JOIN DelimitedParamParser(@CSV_STRING, @DELIMITER) SPLIT  ON SPLIT.ID = SUPER_HERO_SKILL.SKILL_ID
    GROUP BY SUPER_HERO_SKILL.SUPER_HERO_ID
    HAVING COUNT(DISTINCT(SUPER_HERO_SKILL.SKILL_ID)) = (SELECT COUNT(DISTINCT(Id)) FROM DelimitedParamParser(@CSV_STRING, @DELIMITER))
    )

Ответ 2

Это разделено на две части, фильтр и остальную часть запроса, поэтому легко расширить

DECLARE @DELIMITER CHAR = ','
DECLARE @CSV_STRING VARCHAR(20) = '1,3'

SELECT @TOTREQ = COUNT(DISTINCT ITEMS) FROM dbo.Split(@CSV_STRING, @DELIMITER)

SELECT 
     SUPER_HERO_NAME =   SUPER_HERO.NAME
FROM 
    SUPER_HERO INNER JOIN
    (SELECT SUPER_HERO_SKILL.SUPER_HERO_ID
     FROM SUPER_HERO_SKILL     
     LEFT JOIN dbo.Split(@CSV_STRING, @DELIMITER) SPLIT ON SUPER_HERO_SKILL.SKILL_ID = SPLIT.ITEMS
     GROUP BY SUPER_HERO_SKILL.SUPER_HERO_ID
     HAVING COUNT(SPLIT.ITEMS) = @TOTREQ      -- This ensure no mising super-powers
        AND COUNT(*) = @TOTREQ                -- This ensure no extra super-powers  (can be omited of course)
     ) AS FILTER ON     SUPER_HERO.ID = FILTER.SUPER_HERO_ID