如何构造一个查询,只给出与T-SQL中的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

我想看看：
当DECLARE @CSV_STRING VARCHAR(20)=’1,3’时,我只能看到“Silver Surfer”,因为他是唯一一个与Flight和Super Speed有关的技能1和3的技能.

当DECLARE @CSV_STRING VARCHAR(20)=’1,2,我不会看到我的宇宙中的任何英雄,因为没有一个被定义为列出所有三个技能.

必须有一些简单的东西,我错过了.我已经尝试以多种不同的方式构造查询.我在这里提出了最简单的形式,不会使问题的表现复杂化.

注意：我使用一个基于传入的分隔符作为Split的函数.

解决方法

使用下面的分隔符函数返回一个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 = ',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中的CSV列表中的所有值匹配的行

解决方法

猜你在找的MsSQL相关文章