如何去除IN查询Sql中的冗余条件

debugcn 发表于 Dev

将

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

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
             )

将

我已经做了新的方式。不那么复杂并省略冗余代码。

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

SELECT Distinct e.EmployeeId FROM Employee e
CROSS JOIN dbo.fnSplitString(@Filter, ',') AS d
WHERE 
(e.code = 5 AND d.splitdata IN ('5', '16', '20', '23', '33', '49', '62', '90', '91', '92', '93', '94'))
OR (e.code = 46 AND d.splitdata IN ('22', '18'))
OR (e.code = 3 AND d.splitdata IN ('3', '28'))

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。