我创建了一个简单的例子(希望比我的实际数据更有趣),以更好地表达我的问题：
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,我不会看到我的宇宙中的任何英雄,因为没有一个被定义为列出所有三个技能.
必须有一些简单的东西,我错过了.我已经尝试以多种不同的方式构造查询.我在这里提出了最简单的形式,不会使问题的表现复杂化.
解决方法
使用下面的分隔符函数返回一个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))
)
