Firebird物品使用季度报表SQL行转列需求及问题求助

现有Firebird查询可获取过去3个月的物品月度使用数据，但结果按物品+月份逐行展示，需将每个月份的使用量转为单独列，实现每个物品一行展示三个月数据的格式。此前尝试的子查询因返回多行导致报错，以下是解决方法：

原查询问题分析 #

之前注释的子查询错误在于：未关联主查询的ITEM字段，且GROUP BY L.ITEM会返回所有物品的对应月份数据，属于多行结果，无法直接作为SELECT列表中的标量值使用。

修改后的SQL代码 #

SELECT
    L.ITEM,
    I.NOME AS ITEM_NAME,
    I.UNI_CON,
    I.CONVER,
    I.UNI_COMP,
    MAX(I.EST_MAX) AS EST_MAXIMO,
    MAX(I.CUSTO) AS PRECO,
    MAX(I.EST_MIN) AS EST_MINIMO,
    MAX(I.ESTOQUE) AS ESTOQUE,
    -- 过去1个月的使用量
    SUM(CASE 
        WHEN C.MES = EXTRACT(MONTH FROM DATEADD(-1 MONTH TO CURRENT_DATE)) 
             AND C.ANO = EXTRACT(YEAR FROM DATEADD(-1 MONTH TO CURRENT_DATE))
        THEN L.QTDE ELSE 0 
    END) AS USAGE_LAST_MONTH,
    -- 过去2个月的使用量
    SUM(CASE 
        WHEN C.MES = EXTRACT(MONTH FROM DATEADD(-2 MONTH TO CURRENT_DATE)) 
             AND C.ANO = EXTRACT(YEAR FROM DATEADD(-2 MONTH TO CURRENT_DATE))
        THEN L.QTDE ELSE 0 
    END) AS USAGE_LAST_2MONTH,
    -- 过去3个月的使用量
    SUM(CASE 
        WHEN C.MES = EXTRACT(MONTH FROM DATEADD(-3 MONTH TO CURRENT_DATE)) 
             AND C.ANO = EXTRACT(YEAR FROM DATEADD(-3 MONTH TO CURRENT_DATE))
        THEN L.QTDE ELSE 0 
    END) AS USAGE_LAST_3MONTH
FROM GECADSAI C 
INNER JOIN GELANSAI L ON C.ANO = L.ANO AND C.MES = L.MES AND C.DOC = L.DOC
LEFT JOIN GEITENS I ON L.ITEM = I.COD
WHERE   
    (
        (C.MES = EXTRACT(MONTH FROM DATEADD(-1 MONTH TO CURRENT_DATE)) AND C.ANO = EXTRACT(YEAR FROM DATEADD(-1 MONTH TO CURRENT_DATE)))
        OR (C.MES = EXTRACT(MONTH FROM DATEADD(-2 MONTH TO CURRENT_DATE)) AND C.ANO = EXTRACT(YEAR FROM DATEADD(-2 MONTH TO CURRENT_DATE)))
        OR (C.MES = EXTRACT(MONTH FROM DATEADD(-3 MONTH TO CURRENT_DATE)) AND C.ANO = EXTRACT(YEAR FROM DATEADD(-3 MONTH TO CURRENT_DATE)))
    )
    AND C.CDC NOT BETWEEN 9901 AND 9999
    AND I.REF = 1
    AND L.CONSOL = 'T'
    AND C.CONSOL = 'T'
GROUP BY L.ITEM, I.NOME, I.UNI_CON, I.CONVER, I.UNI_COMP

关键说明 #

1. 条件聚合行转列：使用SUM(CASE WHEN ... THEN L.QTDE ELSE 0 END)，针对每个月份的条件筛选出对应数据并求和，将多行数据转为列。
2. 动态年份适配：替换原硬编码的2022/2023，改用EXTRACT(YEAR FROM DATEADD(...))自动获取对应月份的年份，避免跨年时手动修改。
3. GROUP BY调整：移除原分组中的C.ANO和C.MES，确保每个物品仅生成一行结果。

结果对比 #

当前查询结果（行式） #

期望查询结果（列式） #

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