SQL中如何按列条件及指定日期范围拆分列并合并行？

针对日期为2022.01.12的数据，需要将my_table中同一item、同一时间点的in（入库）和out（出库）记录合并为一行。

现有my_table示例数据 #

期望结果 #

SQL解决方案 #

方案1：自连接（适用于每个时间点每个item都有对应出入库记录） #

通过表自连接，精准匹配同一item、同一时间点的出入库记录：

SELECT
    -- 提取日期部分，若date为datetime类型需根据数据库调整格式函数
    SUBSTRING(t_in.date, 1, 5) AS date,
    t_in.area AS area_in,
    t_in.move AS move_in,
    t_in.qty AS qty_in,
    t_out.area AS area_out,
    t_out.move AS move_out,
    t_out.qty AS qty_out,
    t_in.item
FROM my_table t_in
INNER JOIN my_table t_out
    ON t_in.item = t_out.item
    AND t_in.date = t_out.date
    AND t_in.move = 'in'
    AND t_out.move = 'out'
WHERE
    -- 筛选2022.01.12的数据，若date为日期类型可改为DATE(t_in.date) = '2022-01-12'
    t_in.date LIKE '01.12%'
ORDER BY t_in.date, t_in.item;

方案2：条件聚合（适用于存在单方向记录的场景） #

如果存在某个时间点只有入库或只有出库的情况，条件聚合能兼容这类场景：

SELECT
    SUBSTRING(date, 1, 5) AS date,
    MAX(CASE WHEN move = 'in' THEN area END) AS area_in,
    MAX(CASE WHEN move = 'in' THEN move END) AS move_in,
    SUM(CASE WHEN move = 'in' THEN qty END) AS qty_in,
    MAX(CASE WHEN move = 'out' THEN area END) AS area_out,
    MAX(CASE WHEN move = 'out' THEN move END) AS move_out,
    SUM(CASE WHEN move = 'out' THEN qty END) AS qty_out,
    item
FROM my_table
WHERE date LIKE '01.12%'
GROUP BY SUBSTRING(date, 1, 5), item, LEFT(date, 8) -- 按完整时间点+item分组
ORDER BY LEFT(date, 8), item;

注意事项 #

若date字段是datetime类型，提取日期/时间部分的函数需对应数据库调整：

• MySQL：DATE_FORMAT(date, '%d.%m')（提取日期）、DATE_FORMAT(date, '%d.%m %H:%i')（提取完整时间点）
• PostgreSQL：TO_CHAR(date, 'DD.MM')、TO_CHAR(date, 'DD.MM HH24:MI')
• SQL Server：FORMAT(date, 'dd.MM')、FORMAT(date, 'dd.MM HH:mm')

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