如何构建查询以仅提供与 T-SQL 中 ID 的 CSV 列表中的所有值匹配的行 [英] How can I structure a query to give me only the rows that match ALL values in a CSV list of IDs in T-SQL

问题描述

我创建了一个简单的例子(希望比我的实际数据更有趣)来更好地表达我的问题:

I've created a simple example (hopefully much more fun than my actual data) to better express my question:

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

错误查询示例(未显示所需结果):

Example of bad query (not showing desired results):

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 的人.

What I would like to see:
When DECLARE @CSV_STRING VARCHAR(20) = '1,3' I should only see "Silver Surfer" since he is the only one with both skills 1 and 3 which correlate to Flight and Super Speed.

当 DECLARE @CSV_STRING VARCHAR(20) = '1,2,3' 我不应该在我的宇宙中看到 任何 英雄，因为没有定义为拥有所有列出了三个技能.

When DECLARE @CSV_STRING VARCHAR(20) = '1,2,3' I should not see any heroes in my universe since there are none defined to have all three skills listed.

一定是我遗漏了一些简单的东西.我尝试过以多种不同的方式构建查询.我在这里展示了它的最简单形式，以免使问题的表述复杂化.

There must be something simple that I am missing. I have tried structuring the query many different ways. I have presented the simplest form of it here as not to complicate the presentation of problem.

注意:我使用了一个基于 拆分的函数在传入的分隔符上.

Note: I use a function that acts as a Split based on delimiter passed in.

推荐答案

使用以下返回 int 列的拆分器函数.所以很容易检查 HAVING 子句中的计数.

Use the below splitter function which returns an int column. So it's easy to check the count in the HAVING clause.

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

这有效，请记住在末尾多加一个逗号.

This works, keep in mind to give an extra comma at the 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))
    )

这篇关于如何构建查询以仅提供与 T-SQL 中 ID 的 CSV 列表中的所有值匹配的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！