如何用SQL按月份汇总Quantity并实现行转列展示？

原始数据表（假设表名为inventory） #

ItemId  Item    RegisterDate    Quantity
1       Item1   02-01-2023          35
1       Item1   03-01-2023          40
2       Item2   02-01-2023          40
2       Item2   02-03-2023          15
2       Item2   02-04-2023          30
6       Item6   02-04-2023          35
6       Item6   25-04-2023          20

期望输出结果 #

ItemId  Item    Jan Feb Mar Apr
1       Item1   75  0   0   0
2       Item2   40  0   15  30
6       Item6   0   0   0   55

解决方案 #

方法一：使用CASE WHEN实现（跨数据库通用） #

该方法适用于MySQL、PostgreSQL、SQL Server等绝大多数关系型数据库，核心逻辑是按ItemId和Item分组，通过CASE WHEN匹配记录对应的月份，汇总对应月份的Quantity，无数据的月份返回0。

MySQL版本

需要先将字符串格式的日期转换为日期类型，再提取月份：

SELECT
    ItemId,
    Item,
    SUM(CASE WHEN MONTH(STR_TO_DATE(RegisterDate, '%d-%m-%Y')) = 1 THEN Quantity ELSE 0 END) AS Jan,
    SUM(CASE WHEN MONTH(STR_TO_DATE(RegisterDate, '%d-%m-%Y')) = 2 THEN Quantity ELSE 0 END) AS Feb,
    SUM(CASE WHEN MONTH(STR_TO_DATE(RegisterDate, '%d-%m-%Y')) = 3 THEN Quantity ELSE 0 END) AS Mar,
    SUM(CASE WHEN MONTH(STR_TO_DATE(RegisterDate, '%d-%m-%Y')) = 4 THEN Quantity ELSE 0 END) AS Apr
FROM inventory
GROUP BY ItemId, Item
ORDER BY ItemId;

SQL Server版本

使用CONVERT函数转换日期格式（105对应dd-mm-yyyy格式）：

SELECT
    ItemId,
    Item,
    SUM(CASE WHEN MONTH(CONVERT(date, RegisterDate, 105)) = 1 THEN Quantity ELSE 0 END) AS Jan,
    SUM(CASE WHEN MONTH(CONVERT(date, RegisterDate, 105)) = 2 THEN Quantity ELSE 0 END) AS Feb,
    SUM(CASE WHEN MONTH(CONVERT(date, RegisterDate, 105)) = 3 THEN Quantity ELSE 0 END) AS Mar,
    SUM(CASE WHEN MONTH(CONVERT(date, RegisterDate, 105)) = 4 THEN Quantity ELSE 0 END) AS Apr
FROM inventory
GROUP BY ItemId, Item
ORDER BY ItemId;

方法二：使用PIVOT函数（适用于SQL Server等支持PIVOT的数据库） #

如果数据库支持PIVOT语法，可以更简洁地完成行列转换，通过子查询提取月份编号后，用PIVOT汇总数据，最后用ISNULL将空值替换为0：

SELECT
    ItemId,
    Item,
    ISNULL([1], 0) AS Jan,
    ISNULL([2], 0) AS Feb,
    ISNULL([3], 0) AS Mar,
    ISNULL([4], 0) AS Apr
FROM (
    SELECT
        ItemId,
        Item,
        MONTH(CONVERT(date, RegisterDate, 105)) AS MonthNum,
        Quantity
    FROM inventory
) AS SourceTable
PIVOT (
    SUM(Quantity)
    FOR MonthNum IN ([1], [2], [3], [4])
) AS PivotTable
ORDER BY ItemId;

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