SQL筛选异常分配角色用户及查询语句优化咨询

原有语句的问题 #

你原来的写法逻辑无法满足需求：同一行的introleid不可能同时属于子角色集合和父角色集合，两个集合的ID没有重叠，所以NOT IN条件相当于完全未生效，最终只会查出所有持有目标子角色的用户，没有过滤掉其中已经持有父角色的正常用户。

正确优化方案 #

方案1：使用NOT EXISTS实现（可读性最高，执行效率也较好） #

SELECT DISTINCT url.intuserid
FROM user_role_link url
WHERE 
  -- 过滤：用户持有目标子角色
  url.introleid IN (256, 308, 313, 314, 484, 485)
  AND url.bitLive = 1
  AND url.bitDeleted = 0
  -- 核心过滤：该用户不存在任何一个目标父角色
  AND NOT EXISTS (
    SELECT 1 
    FROM user_role_link url2
    WHERE 
      url2.intuserid = url.intuserid
      AND url2.introleid IN (225, 228, 229, 230, 231, 232, 233, 236, 237, 239, 240, 241, 242)
      AND url2.bitLive = 1
      AND url2.bitDeleted = 0
  )

方案2：使用分组聚合判断（适合需要同时统计用户持有的角色数的场景） #

SELECT url.intuserid
FROM user_role_link url
WHERE url.bitLive = 1 AND url.bitDeleted = 0
GROUP BY url.intuserid
HAVING 
  -- 至少持有一个目标子角色
  SUM(CASE WHEN url.introleid IN (256, 308, 313, 314, 484, 485) THEN 1 ELSE 0 END) > 0
  -- 完全没有持有目标父角色
  AND SUM(CASE WHEN url.introleid IN (225, 228, 229, 230, 231, 232, 233, 236, 237, 239, 240, 241, 242) THEN 1 ELSE 0 END) = 0

可选扩展：关联角色表展示角色信息 #

如果需要同时展示用户持有的异常子角色名称，可以关联dbo.user_role表：

SELECT DISTINCT url.intuserid, ur.DescriptionRole AS 异常角色名称
FROM user_role_link url
JOIN dbo.user_role ur ON url.introleid = ur.roleid
WHERE 
  url.introleid IN (256, 308, 313, 314, 484, 485)
  AND url.bitLive = 1
  AND url.bitDeleted = 0
  AND NOT EXISTS (
    SELECT 1 
    FROM user_role_link url2
    WHERE 
      url2.intuserid = url.intuserid
      AND url2.introleid IN (225, 228, 229, 230, 231, 232, 233, 236, 237, 239, 240, 241, 242)
      AND url2.bitLive = 1
      AND url2.bitDeleted = 0
  )

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