如何将两条统计不同groupid的MySQL查询合并为单条查询

核心采用条件聚合实现单查询拿到两组统计结果，性能优于两次独立查询：

$combinedQ = $db->query('
    SELECT 
        DATE_FORMAT(regtime, "%M") AS luna,
        COUNT(CASE WHEN groupid = 6 THEN id END) AS devizieri,
        COUNT(CASE WHEN groupid = 7 THEN id END) AS beneficiar
    FROM edev_useri 
    WHERE 
        groupid IN (6,7) 
        AND YEAR(regtime) = YEAR(CURRENT_DATE) 
    GROUP BY DATE_FORMAT(regtime, "%M") 
    ORDER BY MONTH(regtime) ASC
');

方案说明 #

• 统计逻辑：COUNT统计时会忽略CASE WHEN未命中返回的NULL值，精准实现按groupid分别统计月度数量的效果，没有数据的月份对应统计值自动为0
• 排序优化：原查询按月份字符串排序可能出现顺序错乱，改为按MONTH(regtime)排序完全符合时间先后逻辑
• 结果遍历：每一行返回的字段包含月份、groupid=6的统计值devizieri、groupid=7的统计值beneficiar，直接适配图表展示的多序列数据要求，无需额外拼接两次查询的结果

如果你的原有业务逻辑需要保留两条查询的行结构，可以用UNION ALL方案：

$unionQ = $db->query('
    SELECT "devizieri" AS stat_type, DATE_FORMAT(regtime, "%M") AS luna, COUNT(id) AS stat_num 
    FROM edev_useri 
    WHERE groupid = 6 AND YEAR(regtime) = YEAR(CURRENT_DATE) 
    GROUP BY DATE_FORMAT(regtime, "%M")
    UNION ALL
    SELECT "beneficiar" AS stat_type, DATE_FORMAT(regtime, "%M") AS luna, COUNT(id) AS stat_num 
    FROM edev_useri 
    WHERE groupid = 7 AND YEAR(regtime) = YEAR(CURRENT_DATE) 
    GROUP BY DATE_FORMAT(regtime, "%M")
    ORDER BY MONTH(regtime) ASC, stat_type ASC
');

这个方案返回的每一行带stat_type字段区分统计项，你可以按类型筛选分别拿到原来两组查询的结果。

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