MySQL如何编写查询按日期统计商品销量生成行列交叉结果表

表结构与需求说明 #

• Product表：商品基础信息表，包含id（商品主键ID）、name（商品名称）两个字段，样例数据共3条：id=1对应商品A，id=2对应商品B，id=3对应商品C
• Order表：订单交易表，包含id（订单主键ID）、user_id（购买用户ID）、product_id（关联商品ID）、date（交易日期）、quantity（购买数量）五个字段，样例数据覆盖2022-07-01至2022-07-05的7条交易记录
• 输出要求：结果行维度为商品名称，列维度为所有交易日期，单元格值为对应商品在对应日期的总销售数量，无销售记录的单元格填充0

固定日期范围查询（已知日期区间场景） #

如果统计的日期范围是确定的（比如本次样例的2022-07-01至2022-07-05），直接用条件聚合+左连接实现即可，SQL如下：

SELECT
  p.name AS Name,
  COALESCE(SUM(CASE WHEN o.date = '2022-07-01' THEN o.quantity END), 0) AS `2022-07-01`,
  COALESCE(SUM(CASE WHEN o.date = '2022-07-02' THEN o.quantity END), 0) AS `2022-07-02`,
  COALESCE(SUM(CASE WHEN o.date = '2022-07-03' THEN o.quantity END), 0) AS `2022-07-03`,
  COALESCE(SUM(CASE WHEN o.date = '2022-07-04' THEN o.quantity END), 0) AS `2022-07-04`,
  COALESCE(SUM(CASE WHEN o.date = '2022-07-05' THEN o.quantity END), 0) AS `2022-07-05`
FROM Product p
LEFT JOIN `Order` o ON p.id = o.product_id
GROUP BY p.id, p.name;

语法说明 #

• 对Order表加反引号是因为Order是MySQL保留关键字，不转义会触发语法错误
• 用LEFT JOIN关联订单表，保证从未产生过销量的商品也会出现在结果中，不会被过滤
• CASE WHEN只匹配对应日期的销量数值，非当前日期的记录返回NULL，SUM聚合时会自动忽略NULL值
• 外层套COALESCE函数，将聚合后为NULL的结果（即当日无销量）转换为0，符合填充要求

动态日期查询（日期不固定场景） #

如果统计的日期范围不固定，不想每次手动修改枚举的日期列，可以用预处理语句动态生成日期列，SQL如下：

-- 调整group_concat最大长度，避免日期过多时拼接内容被截断
SET SESSION group_concat_max_len = 10240;

-- 动态拼接所有日期对应的聚合逻辑
SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'COALESCE(SUM(CASE WHEN o.date = ''',
      date,
      ''' THEN o.quantity END),0) AS `',
      date, '`'
    ) ORDER BY date
  ) INTO @sql
FROM `Order`;
-- 若需要限定日期范围，可在上述SELECT语句后加WHERE条件过滤，例如 WHERE date BETWEEN '2022-07-01' AND '2022-07-31'

-- 拼接完整查询语句
SET @sql = CONCAT('SELECT p.name AS Name, ', @sql, ' 
                  FROM Product p
                  LEFT JOIN `Order` o ON p.id = o.product_id
                  GROUP BY p.id, p.name');

-- 执行动态生成的SQL
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

注意事项 #

• 动态SQL会自动读取Order表中存在的所有交易日期生成列，无需手动枚举
• 拼接日期列时加了ORDER BY date，保证输出的日期列按时间先后顺序排列
• 若日期数量较多，提前调大group_concat_max_len参数，避免拼接的SQL被截断导致执行报错

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