如何调整SQL查询将店铺每日订单数据转为日期列横向展示？

首先修正你原SQL中的笔误：WHERE子句里的order_created应该是created_at，否则会因字段不存在或逻辑错误导致结果异常。

要将纵向的（日期+店铺）分组结果转为店铺为行、日期为列的横向格式，主要有两种实现方式，取决于你的日期范围是否固定：

一、日期范围固定（如你指定的2023年10月）：使用条件聚合 #

这是通用所有SQL数据库的写法，通过CASE WHEN配合聚合函数（COUNT）来生成每个日期的列：

SELECT 
  shop_id AS 店铺ID,
  COUNT(CASE WHEN DATE(created_at) = '2023-10-01' THEN 1 END) AS '2023-10-01',
  COUNT(CASE WHEN DATE(created_at) = '2023-10-02' THEN 1 END) AS '2023-10-02',
  -- 依次添加10月剩余日期的CASE语句
  COUNT(CASE WHEN DATE(created_at) = '2023-10-31' THEN 1 END) AS '2023-10-31'
FROM orders
WHERE 
  created_at >= '2023-10-01' 
  AND created_at < '2023-11-01'
  AND shop_id IN (123, 567)
GROUP BY shop_id;

说明： #

• CASE WHEN会判断当前行的日期是否匹配指定日期，匹配则返回1，否则返回NULL；COUNT会忽略NULL值，从而统计出该店铺当日的订单数。
• 如果需要显示为01/10/2023这种格式的列名，只需修改AS后的别名，比如AS '01/10/2023'。

二、日期范围不固定：使用动态SQL或数据库专属PIVOT功能 #

如果日期范围经常变化，手动写CASE WHEN会很繁琐，这时可以用数据库专属的行转列功能：

1. MySQL/MariaDB：动态生成条件聚合语句 #

通过预处理语句动态拼接日期列：

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'COUNT(CASE WHEN DATE(created_at) = ''',
      DATE(created_at),
      ''' THEN 1 END) AS ''',
      DATE(created_at),
      ''''
    )
  ) INTO @sql
FROM orders
WHERE 
  created_at >= '2023-10-01' 
  AND created_at < '2023-11-01'
  AND shop_id IN (123, 567);

SET @sql = CONCAT('SELECT shop_id AS 店铺ID, ', @sql, ' FROM orders WHERE created_at >= ''2023-10-01'' AND created_at < ''2023-11-01'' AND shop_id IN (123, 567) GROUP BY shop_id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

2. SQL Server：使用PIVOT关键字 #

SELECT 
  shop_id AS 店铺ID,
  [2023-10-01], [2023-10-02], ..., [2023-10-31]
FROM (
  SELECT 
    shop_id,
    DATE(created_at) AS order_date,
    1 AS order_flag
  FROM orders
  WHERE 
    created_at >= '2023-10-01' 
    AND created_at < '2023-11-01'
    AND shop_id IN (123, 567)
) AS src
PIVOT (
  COUNT(order_flag)
  FOR order_date IN ([2023-10-01], [2023-10-02], ..., [2023-10-31])
) AS pvt;

如果要动态生成PIVOT的列，同样需要用动态SQL拼接IN子句中的日期列表。

3. PostgreSQL：使用crosstab扩展 #

需要先安装tablefunc扩展，然后执行：

-- 先启用扩展（仅需执行一次）
CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT * FROM crosstab(
  'SELECT shop_id, DATE(created_at), COUNT(*) 
   FROM orders 
   WHERE created_at >= ''2023-10-01'' AND created_at < ''2023-11-01'' AND shop_id IN (123, 567)
   GROUP BY shop_id, DATE(created_at)
   ORDER BY shop_id, DATE(created_at)',
  'SELECT DISTINCT DATE(created_at) FROM orders WHERE created_at >= ''2023-10-01'' AND created_at < ''2023-11-01'' ORDER BY DATE(created_at)'
) AS ct(
  店铺ID INT,
  "2023-10-01" INT,
  "2023-10-02" INT,
  -- 依次添加剩余日期的列定义
  "2023-10-31" INT
);

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