如何在MySQL查询中新增2023、2024年终端活跃数统计列？

问题描述 #

现有MySQL查询语句：

Select DATE_FORMAT(fecha, '%M')as Month, count( distinctrow terminal ) as NT2022 FROM journal where operacion = 'venta' and year(fecha)='2022' GROUP by DATE_FORMAT(fecha, '%m')

该查询返回月份和2022年各月的活跃终端数量，但存在两个问题：

1. 缺少2022年无数据的1月；
2. 需要新增2023年、2024年的活跃终端数量列，同时确保全年12个月份都显示。

当前查询结果：

期望查询结果：

解决方案 #

核心思路是先构建包含全年12个月份的基础数据集，再通过左连接结合条件聚合，分别统计2022、2023、2024年各月的活跃终端数。

最终查询语句如下：

SELECT
    months.month_name AS Month,
    COUNT(DISTINCT CASE WHEN YEAR(j.fecha) = 2022 THEN j.terminal END) AS NT2022,
    COUNT(DISTINCT CASE WHEN YEAR(j.fecha) = 2023 THEN j.terminal END) AS NT2023,
    COUNT(DISTINCT CASE WHEN YEAR(j.fecha) = 2024 THEN j.terminal END) AS NT2024
FROM (
    SELECT 1 AS month_num, 'January' AS month_name UNION ALL
    SELECT 2, 'February' UNION ALL
    SELECT 3, 'March' UNION ALL
    SELECT 4, 'April' UNION ALL
    SELECT 5, 'May' UNION ALL
    SELECT 6, 'June' UNION ALL
    SELECT 7, 'July' UNION ALL
    SELECT 8, 'August' UNION ALL
    SELECT 9, 'September' UNION ALL
    SELECT 10, 'October' UNION ALL
    SELECT 11, 'November' UNION ALL
    SELECT 12, 'December'
) AS months
LEFT JOIN journal j
    ON MONTH(j.fecha) = months.month_num
    AND j.operacion = 'venta'
    AND YEAR(j.fecha) IN (2022, 2023, 2024)
GROUP BY months.month_num, months.month_name
ORDER BY months.month_num;

说明 #

1. 月份基础表：通过UNION ALL生成12个月份的编号和英文名称，确保全年每个月份都能显示；
2. 左连接：将月份表与journal表关联，匹配对应月份和目标年份的销售记录；
3. 条件聚合：使用CASE WHEN结合COUNT(DISTINCT)，分别统计各年份的活跃终端数，无数据时自动返回0；
4. 排序：按月份编号排序，保证结果按1-12月顺序展示。

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