如何实现按年月分组统计店铺订单并将月度统计结果转为列展示

我来帮你搞定这个统计需求！你现在遇到的核心问题是需要把按月份分组的行数据转换成列（也就是常说的“行转列”操作），而你的原SQL只是按月份分组输出了多行记录，不符合你要的每行一个店铺、各月订单数为列的格式。另外先帮你指出原SQL里的几个小错误：

1. 关联条件错误：你写的shops.id = orders.id应该改成shops.id = orders.shop_id，毕竟Orders表里是用shop_id关联Shops的id的；
2. GROUP BY字段错误：你用了orders.wholesaler_id，但这个字段在你给出的表结构里根本不存在，应该按店铺的id和shop_name分组；
3. 输出结构不符合需求：原SQL是按店铺+月份分组生成多行，而你需要的是每个店铺一行，各月订单数作为单独列。

正确的静态SQL实现 #

如果你的统计月份是固定的（比如只需要2023年1-4月），可以用条件聚合来实现，也就是结合COUNT()和CASE WHEN来统计每个月份的订单数：

SELECT
    s.id AS shop_id,
    s.shop_name,
    -- 统计2023年1月订单数
    COUNT(CASE WHEN MONTH(o.created_at) = 1 AND YEAR(o.created_at) = 2023 THEN o.order_id END) AS order_count_january_23,
    -- 统计2023年2月订单数
    COUNT(CASE WHEN MONTH(o.created_at) = 2 AND YEAR(o.created_at) = 2023 THEN o.order_id END) AS order_count_february_23,
    -- 统计2023年3月订单数
    COUNT(CASE WHEN MONTH(o.created_at) = 3 AND YEAR(o.created_at) = 2023 THEN o.order_id END) AS order_count_march_23,
    -- 统计2023年4月订单数
    COUNT(CASE WHEN MONTH(o.created_at) = 4 AND YEAR(o.created_at) = 2023 THEN o.order_id END) AS order_count_april_23
FROM Shops s
-- 用LEFT JOIN保证没有订单的店铺也会被显示（订单数为0）
LEFT JOIN Orders o ON s.id = o.shop_id
GROUP BY s.id, s.shop_name
ORDER BY s.id;

代码解释 #

• LEFT JOIN：如果某个店铺没有任何订单，用LEFT JOIN会保留该店铺的记录，对应的月份订单数会显示为0；如果用INNER JOIN的话，无订单的店铺会被过滤掉，你可以根据需求选择。
• 条件聚合：每个CASE WHEN会判断订单的创建月份和年份，符合条件的返回order_id（非NULL值），COUNT()只会统计非NULL的数量，这样就得到了该店铺对应月份的订单数。
• GROUP BY：按店铺的id和shop_name分组，确保每个店铺只输出一行数据。

动态生成月份列（可选） #

如果你的统计月份不固定（比如需要自动统计所有存在订单的月份），那就要用到动态SQL，不同数据库的写法略有不同：

• MySQL可以用GROUP_CONCAT生成CASE语句，再拼接成完整SQL执行；
• PostgreSQL可以用crosstab函数或者动态拼接SQL；
• SQL Server可以用PIVOT运算符或者动态SQL。

不过如果是固定月份的统计，上面的静态SQL已经完全满足你的需求啦。

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