sql-server - 如何在** IN **查询中删除冗余条件

Question

我有这种查询。但是我需要优化此查询，以便如何使用相同的拆分功能忽略冗余条件。

DECLARE @Filter nvarchar(20)
SELECT @Filter ='5,22,3'

SELECT * FROM    Employee e
             WHERE e.code IN 
             (
             CASE WHEN((SELECT count(*) FROM dbo.FNSPLITSTRING(SUBSTRING(@Filter,1,LEN(@Filter)-1), ',') d 
                                  WHERE d.splitdata IN (5, 16, 20, 23, 33, 49, 62, 90, 91, 92, 93, 94))>0) THEN 5 ELSE 0 END 
             ,CASE WHEN((SELECT count(*) FROM dbo.FNSPLITSTRING(SUBSTRING(@Filter,1,LEN(@Filter)-1), ',') d 
                                  WHERE d.splitdata IN (22, 18))>0) THEN 46 ELSE 0 END
             ,CASE WHEN((SELECT count(*) FROM dbo.FNSPLITSTRING(SUBSTRING(@Filter,1,LEN(@Filter)-1), ',') d 
                                  WHERE d.splitdata IN (3, 28))>0) THEN 3 ELSE 0 END
             )

Answer 1

正如@Damien_The_Unbeliever所说，避免将字符串拆分为值表。您无需多次拆分相同的字符串。相反，您可以使用临时表变量。

DECLARE @SplitStrings TABLE
(
    splitdata int
)

INSERT @SplitStrings
SELECT splitdata FROM dbo.FNSPLITSTRING(SUBSTRING(@Filter,1,LEN(@Filter)-1), ',') d

DECLARE @EmployeeCodes TABLE
(
    splitdata INT,
    code int
)

INSERT @EmployeeCodes (splitdata, code)
VALUES (5, 5), (16, 5), (20, 5), (23, 5), (33, 5), (49, 5), (62, 5), (90, 5), (91, 5), (92, 5), (93, 5), (94, 5), 
       (22, 46), (18, 46),
       (3, 3), (28, 3)


SELECT e.* 
FROM Employee e
JOIN @EmployeeCodes ec
ON e.code = ec.code
JOIN @SplitStrings ss
ON ec.splitdata = ss.splitdata

我希望这是您正在寻找的方向。

注意：假设您不需要 0作为员工代码。