T-SQL中如何按多位置ID筛选员工（支持可选过滤）

场景说明 #

需要筛选出同时居住在指定多个位置的员工，且位置筛选为可选操作（当@locationIds为NULL时返回所有员工）。现有两张业务表：

表结构与测试数据 #

create table employees
(
    empId int,
    empname varchar(100),
    empSalary money
)

insert into employees 
values (1, 'John', 8000), (2, 'Sam', 9800), (3, 'Ray', 9500)

create table empLocations
(
    locId int,
    empId int
)

insert into empLocations 
values (1, 1), (2, 1), (3, 2), (4, 3), (5, 1)

需求目标 #

当指定@locationIds = '1,4'时，需返回同时居住在位置1和4的员工（示例中为John和Ray），且查询需遵循给定框架结构，禁止使用交叉连接。

待补充的查询框架 #

declare @locationIds varchar(100) = '1,4'

select * 
from employees 
where 
    (0 = case when (@locationIds is null) then 0 else 1 end) 
     or 
     ---<此处填写条件>----- 
    )

配套拆分字符串函数 #

CREATE FUNCTION [dbo].[SplitStringi]       
    (@str nvarchar(4000),       
     @separator char(1))      
RETURNS TABLE
AS      
   RETURN (      
       WITH tokens(p, a, b) AS 
       (
           SELECT
               1, 1,       
               CHARINDEX(@separator, @str)      
           UNION ALL
           SELECT
               p + 1, b + 1,       
               CHARINDEX(@separator, @str, b + 1)      
           FROM tokens      
           WHERE b > 0      
        )      
        SELECT
            p indx,      
            SUBSTRING(@str, a,       
                      CASE WHEN b > 0 THEN b-a ELSE 4000 END) AS strg      
        FROM tokens      
      )

解决方案 #

将框架中的待补充条件替换为以下逻辑，实现多位置同时匹配的筛选：

(
    SELECT COUNT(DISTINCT locId)
    FROM empLocations el
    WHERE el.empId = employees.empId
      AND el.locId IN (SELECT strg FROM [dbo].[SplitStringi](@locationIds, ','))
) = (SELECT COUNT(*) FROM [dbo].[SplitStringi](@locationIds, ','))

完整可执行查询 #

declare @locationIds varchar(100) = '1,4'

select * 
from employees 
where 
    (0 = case when (@locationIds is null) then 0 else 1 end) 
     or 
    (
        SELECT COUNT(DISTINCT locId)
        FROM empLocations el
        WHERE el.empId = employees.empId
          AND el.locId IN (SELECT strg FROM [dbo].[SplitStringi](@locationIds, ','))
    ) = (SELECT COUNT(*) FROM [dbo].[SplitStringi](@locationIds, ','))

逻辑说明 #

1. 第一个子查询：统计当前员工在指定位置列表中匹配到的不重复位置数量（用DISTINCT避免同一员工多次关联同一位置的重复计数）。
2. 第二个子查询：统计传入的位置列表总数量。
3. 当两者数值相等时，说明该员工的位置覆盖了所有指定的筛选条件，符合需求。
4. 当@locationIds为NULL时，第一个OR条件生效，直接返回所有员工，满足可选筛选的要求。

内容的提问来源于stack exchange，提问作者Surensiveaya