SQL合并多查询 按角色与操作类型交叉统计操作用户数

核心逻辑是先通过交叉连接生成所有角色、操作类型的全量组合（避免无匹配数据时缺行缺列），再左关联操作日志表，通过条件聚合完成行转列统计，单条SQL即可输出所有单元格结果，无匹配数据自动返回0。

可直接运行的SQL代码 #

SELECT
    r.libelle AS ROLE,
    IFNULL(COUNT(DISTINCT CASE WHEN tr.libelle = 'Executif' THEN a.id_user END), 0) AS Executif,
    IFNULL(COUNT(DISTINCT CASE WHEN tr.libelle = 'home' THEN a.id_user END), 0) AS `home`,
    IFNULL(COUNT(DISTINCT CASE WHEN tr.libelle = 'System' THEN a.id_user END), 0) AS System
FROM t_role r
-- 交叉连接生成所有角色+操作类型的组合，保证无数据的组合也会出现在结果中
CROSS JOIN t_type_role tr
-- 左连操作表，只关联匹配当前角色和操作类型的记录
LEFT JOIN t_action a
    ON a.id_role = r.id_role
    AND a.id_type_role = tr.id_type_role
-- 如果需要过滤有效角色/有效操作类型，可以放开下面两个条件，注意如果实际表状态字段为测试插入语句里的statut（拼写差异），对应调整字段名即可
-- WHERE r.status = 1
-- AND tr.status = 1
GROUP BY r.id_role, r.libelle
-- 该排序是为了和示例输出的行顺序保持一致，不需要固定顺序可直接删除
ORDER BY FIELD(r.libelle, 'Administrator', 'Operator', 'User');

关键说明 #

• 用CROSS JOIN关联角色表和操作类型表，会生成两个表所有记录的笛卡尔积，哪怕某角色没有任何某类型的操作记录，对应行也不会丢失，和期望输出的行范围完全一致。
• 用LEFT JOIN关联操作表，关联条件直接写在ON子句中，不会过滤掉无匹配操作的角色+类型组合。
• 用CASE WHEN做条件判断，只统计对应操作类型下的用户ID，COUNT(DISTINCT)自动对同一用户的多条操作记录去重，符合去重统计用户数的要求。
• IFNULL函数把无匹配数据时的NULL结果转为0，和输出要求一致。
• 以上SQL对提供的测试数据执行后，会完全返回期望结果：Administrator行System列值为2，其余单元格全为0。

如果后续操作类型会动态增减，不想每次改SQL加列，可以基于数据库的预处理语句实现动态行转列，不需要手动维护列名。

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