Wie kann ich eine Abfrage strukturieren, um mir nur die Zeilen zu geben, die ALLE Werte in einer CSV-list von IDs in T-SQL entsprechen

Ich habe ein einfaches Beispiel erstellt (hoffentlich viel mehr Spaß als meine wirklichen data), um meine Frage besser auszudrücken:

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 

Beispiel für schlechte Abfrage (keine gewünschten Ergebnisse):

 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 

Was ich gerne sehen möchte:
Wenn DECLARE @CSV_STRING VARCHAR(20) = '1,3' sollte ich nur "Silver Surfer" sehen, da er der einzige mit beiden Fähigkeiten 1 und 3 ist, die mit Flight und Super Speed ​​korrelieren.

Wenn DECLARE @CSV_STRING VARCHAR(20) = '1,2,3' in meinem Universum DECLARE @CSV_STRING VARCHAR(20) = '1,2,3' ich keine definierte, um alle drei Fähigkeiten aufzuzählen,

Es muss etwas einfaches sein, dass ich fehlt Ich habe versucht, die Abfrage viele verschiedene Möglichkeiten zu strukturieren. Ich habe hier die einfachste Form vorgestellt, um die Darstellung des Problems nicht zu komplizieren.

Anmerkung: Ich benutze eine function, die als Split auf der Grundlage von Trennzeichen passiert.

Verwenden Sie die untenstehende Splitterfunktion, die eine int Spalte zurückgibt. So ist es einfach, die Zählung in der HAVING-Klausel zu überprüfen.

 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 

Dies funktioniert, denken Sie daran, ein zusätzliches Komma am Ende zu geben.

 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)) ) ( 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)) ) 

Dies ist in zwei Teile geteilt, der Filter, und der Rest der Abfrage so ist es einfach zu erweitern

 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