如何根据WHERE子句条件按不同列数执行SELECT与GROUP BY？

问题场景 #

我的WHERE子句包含多个用OR分隔的查询分组，例如：

WHERE (group_='g1' AND unit='u1' AND department='d1')
   OR (group_='g25' AND unit='u54' AND department='d70' AND team='t88')

或者：

WHERE (group_='g1' AND unit='u1' AND department='d1')
   OR (group_='g25' AND unit='u54' AND department='d70' AND team='t88')
   OR (group_='g3' AND unit='u12')

每个分组涉及的列数不同，部分列可能为空。我需要按照每个查询分组对应的字段执行SELECT和GROUP BY，统计对应分组下的活跃、非活跃用户数量。

错误尝试 #

我编写了以下SQL，但无法得到预期结果：

SELECT
    CASE
        WHEN team IS NULL
        THEN group_, unit, department
      
        WHEN team IS NULL AND department IS NULL
        THEN group_, unit

        WHEN team IS NULL AND department IS NULL AND unit IS NULL
        THEN group_

        ELSE
          group_, unit, department, team
    END,
    COUNT(CASE WHEN status='active' then 1 END) AS active_count,
    COUNT(CASE WHEN status='inactive' then 1 END) AS inactive_count

FROM my_table
WHERE (group_='g1' AND unit='u1' AND department='d1')
   OR (group_='g25' AND unit='u54' AND department='d70' AND team='t88')
   OR (group_='g3' AND unit='u6')
GROUP BY
    CASE
        WHEN team IS NULL
        THEN group_, unit, department
      
        WHEN team IS NULL AND department IS NULL
        THEN group_, unit

        WHEN team IS NULL AND department IS NULL AND unit IS NULL
        THEN group_

        ELSE
          group_, unit, department, team
    END
ORDER BY group_

示例数据 #

my_table中的原始数据如下：

期望结果 #

按照WHERE子句中的每个查询分组统计，得到如下结果：

解决方案 #

核心思路是给每个WHERE分组打标记，再按标记和分组对应字段聚合，同时用分组条件填充结果中的NULL值：

SELECT
    -- 根据分组标记返回对应字段，未涉及的列返回NULL
    CASE grp
        WHEN 1 THEN group_
        WHEN 2 THEN group_
        WHEN 3 THEN group_
    END AS `group`,
    CASE grp
        WHEN 1 THEN unit
        WHEN 2 THEN unit
        WHEN 3 THEN unit
    END AS unit,
    CASE grp
        WHEN 1 THEN department
        WHEN 2 THEN department
        WHEN 3 THEN NULL
    END AS department,
    CASE grp
        WHEN 1 THEN NULL
        WHEN 2 THEN team
        WHEN 3 THEN NULL
    END AS team,
    COUNT(CASE WHEN status='active' THEN 1 END) AS active_count,
    COUNT(CASE WHEN status='inactive' THEN 1 END) AS inactive_count
FROM (
    -- 子查询为每个匹配行分配分组标记
    SELECT *,
        CASE
            WHEN group_='g1' AND unit='u1' AND department='d1' THEN 1
            WHEN group_='g25' AND unit='u54' AND department='d70' AND team='t88' THEN 2
            WHEN group_='g3' AND unit='u6' THEN 3
        END AS grp
    FROM my_table
    WHERE (group_='g1' AND unit='u1' AND department='d1')
       OR (group_='g25' AND unit='u54' AND department='d70' AND team='t88')
       OR (group_='g3' AND unit='u6')
) t
GROUP BY grp, `group`, unit, department, team
ORDER BY `group`;

思路说明 #

1. 子查询打标记：用CASE语句给每个匹配WHERE条件的行分配唯一分组ID（grp），明确该行所属的查询分组。
2. 字段映射：根据分组ID，决定每个字段返回原数据还是NULL（对应分组未涉及的列）。
3. 分组聚合：按分组ID和对应字段分组，确保每个查询分组作为独立的统计项，最终得到符合预期的结果。

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