如何简化含IN子查询的CASE WHEN语句并提升SQL查询效率

表结构 #

UserGroups表 #

users表 #

原查询问题 #

原查询通过多次IN子查询判断用户组归属来生成class_group字段，频繁的子查询会导致数据库多次扫描UserGroups表，查询效率低下。

优化方案 #

方案1：聚合用户组归属后关联 #

先对UserGroups表按用户聚合，标记每个用户是否属于目标组，再和users表关联判断，仅需一次聚合扫描：

SELECT
    u.id,
    u.name,
    CASE
        WHEN ug.has_group1 = 1 AND ug.has_group2 = 0 THEN 1
        WHEN ug.has_group2 = 1 AND ug.has_group1 = 0 THEN 2
        WHEN ug.has_group1 = 1 AND ug.has_group2 = 1 THEN 1
        ELSE class.group -- 保留原逻辑的默认值，按需调整关联class表的逻辑
    END AS class_group
FROM users u
LEFT JOIN (
    SELECT
        user_id,
        MAX(CASE WHEN group_id = 1 THEN 1 ELSE 0 END) AS has_group1,
        MAX(CASE WHEN group_id = 2 THEN 1 ELSE 0 END) AS has_group2
    FROM UserGroups
    GROUP BY user_id
) ug ON u.id = ug.user_id
-- 按需添加原查询中的其他关联表，比如LEFT JOIN class ...

方案2：多次LEFT JOIN直接关联目标组 #

通过两次LEFT JOIN分别关联组1、组2的记录，利用JOIN结果的非空性判断归属，写法更直观：

SELECT
    u.id,
    u.name,
    CASE
        WHEN g1.user_id IS NOT NULL AND g2.user_id IS NULL THEN 1
        WHEN g2.user_id IS NOT NULL AND g1.user_id IS NULL THEN 2
        WHEN g1.user_id IS NOT NULL AND g2.user_id IS NOT NULL THEN 1
        ELSE class.group -- 保留原逻辑的默认值，按需调整关联class表的逻辑
    END AS class_group
FROM users u
LEFT JOIN UserGroups g1 ON u.id = g1.user_id AND g1.group_id = 1
LEFT JOIN UserGroups g2 ON u.id = g2.user_id AND g2.group_id = 2
-- 按需添加原查询中的其他关联表，比如LEFT JOIN class ...

性能提示 #

如果在UserGroups表上创建(user_id, group_id)联合索引，以上两种方案的查询效率会进一步提升，数据库能快速定位到用户对应的组记录。

内容的提问来源于stack exchange，提问作者Juan Elias da Cunha