Oracle 11g按月份分组统计采购类型小计遇ORA-00904错误求助

错误原因 #

Oracle 11g 不支持MONTH()函数，报错ORA-00904: "MONTH": invalid identifier正是因为这个。提取日期的月份需要用EXTRACT(MONTH FROM PurchaseDate)（返回数字月份，如1代表1月）或者TO_CHAR(PurchaseDate, 'MM')（返回两位字符串月份，如'01'）。

修改后的查询语句 #

SELECT 
    CASE 
        WHEN EXTRACT(MONTH FROM PurchaseDate) IS NULL THEN '总计'
        ELSE TO_CHAR(EXTRACT(MONTH FROM PurchaseDate), 'FM00')
    END AS PurchaseMonth,
    CASE 
        WHEN EXTRACT(MONTH FROM PurchaseDate) IS NULL THEN '全部采购总额'
        WHEN PurchaseType IS NULL THEN '当月采购总额'
        ELSE PurchaseType
    END AS PurchaseType,
    SUM(PurchaseAmt) AS SummorizedPurchaseAmt
FROM PurchaseItem
GROUP BY ROLLUP(EXTRACT(MONTH FROM PurchaseDate), PurchaseType)
ORDER BY 
    CASE WHEN EXTRACT(MONTH FROM PurchaseDate) IS NULL THEN 13 ELSE EXTRACT(MONTH FROM PurchaseDate) END,
    CASE WHEN PurchaseType IS NULL THEN 1 ELSE 0 END;

关键修改说明 #

1. 替换月份提取逻辑：用EXTRACT(MONTH FROM PurchaseDate)替代MONTH()，确保Oracle能识别。如果需要按「年月」维度统计（比如区分不同年份的同月数据），可以改用TO_CHAR(PurchaseDate, 'YYYY-MM')，同时同步调整GROUP BY和SELECT中的对应字段。
2. 优化小计/总计标签：
   • 当月份字段为NULL时，代表ROLLUP生成的全局总计，标签显示「全部采购总额」
   • 当PurchaseType为NULL但月份存在时，代表当月所有采购的小计，标签显示「当月采购总额」
3. 排序优化：通过ORDER BY让结果按月份升序排列，月度小计排在该月各类型数据之后，最后显示全局总计，更符合阅读习惯。

可选：按「年月」维度统计的版本 #

如果需要区分不同年份的同月数据，可使用以下查询：

SELECT 
    CASE 
        WHEN TO_CHAR(PurchaseDate, 'YYYY-MM') IS NULL THEN '总计'
        ELSE TO_CHAR(PurchaseDate, 'YYYY-MM')
    END AS PurchaseMonth,
    CASE 
        WHEN TO_CHAR(PurchaseDate, 'YYYY-MM') IS NULL THEN '全部采购总额'
        WHEN PurchaseType IS NULL THEN '当月采购总额'
        ELSE PurchaseType
    END AS PurchaseType,
    SUM(PurchaseAmt) AS SummorizedPurchaseAmt
FROM PurchaseItem
GROUP BY ROLLUP(TO_CHAR(PurchaseDate, 'YYYY-MM'), PurchaseType)
ORDER BY 
    CASE WHEN TO_CHAR(PurchaseDate, 'YYYY-MM') IS NULL THEN '9999-99' ELSE TO_CHAR(PurchaseDate, 'YYYY-MM') END,
    CASE WHEN PurchaseType IS NULL THEN 1 ELSE 0 END;

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