[SQL] 나는 T-SQL에서 ID의 CSV 목록에있는 모든 값과 일치하는 행만 저를주는 쿼리를 구성 할 수있는 방법

나는 T-SQL에서 ID의 CSV 목록에있는 모든 값과 일치하는 행만 저를주는 쿼리를 구성 할 수있는 방법

더 나은 내 질문을 표현하는 나는 (내 실제 데이터보다 잘하면 훨씬 더 재미) 간단한 예제를 만들었습니다 :

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

내가 뭘보고 싶다 : 그는 상관 관계를 비행하고 최고 속도에 모두 기술 1과 3을 가진 단 하나이기 때문에 DECLARE @CSV_STRING VARCHAR (20) = '1,3'난 단지 "실버 서퍼"을 참조해야합니다.

나열된 모든 세 가지 기술을 갖도록 정의 아무도 없기 때문에 DECLARE @CSV_STRING VARCHAR (20) = '1,2,3'내 우주의 모든 영웅을 볼 수 있어야합니다.

내가 실종 오전 뭔가 간단이 있어야합니다. 나는 구조 쿼리를 여러 가지 방법으로 노력했다. 나는 문제의 프리젠 테이션을 복잡하게하지 여기 그것의 가장 간단한 형태를 제시 하였다.

참고 : 내가 전달 구분에 따라 분할 역할을하는 함수를 사용합니다.

해결법

1. ==============================

   1.int로 열을 반환하는 스플리터 아래의 기능을 사용합니다. 이 HAVING 절에 수를 확인하기 쉽습니다 그래서.

   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. ==============================

   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
   

from https://stackoverflow.com/questions/7492699/how-can-i-structure-a-query-to-give-me-only-the-rows-that-match-all-values-in-a by cc-by-sa and MIT license