Oracle SQL计算日期平均间隔月数及排序筛选报错求助

原代码存在的问题 #

• 日期间隔函数使用错误：Oracle中计算两个日期的间隔月数需用MONTHS_BETWEEN()函数，而非直接将两个日期传入AVG()。
• 语法混乱：原HAVING子句中CONVERT、ROUND的调用存在括号不匹配、函数用法错误（Oracle无CONVERT(DECIMAL...)写法）的问题。
• HAVING逻辑错误：应筛选聚合后的平均间隔月数，而非对每行数据的转换结果取平均后判断。
• 视图定义不允许直接ORDER BY：Oracle标准视图中不能包含ORDER BY，排序需在查询视图时执行。

修正后的视图创建语句 #

CREATE VIEW AVGMNTHSBETWEEN 
AS 
    SELECT 
        VENDOR_NAME,
        -- 先计算单条发票的间隔月数，取平均后保留1位小数
        ROUND(AVG(MONTHS_BETWEEN(INVOICE_DUE_DATE, INVOICE_DATE)), 1) AS MONTHS_BETWN
    FROM 
        VENDORS 
    INNER JOIN 
        INVOICES ON VENDORS.VENDOR_ID = INVOICES.VENDOR_ID
    GROUP BY 
        VENDOR_NAME
    -- 筛选平均间隔月数≥1.5的供应商
    HAVING 
        AVG(MONTHS_BETWEEN(INVOICE_DUE_DATE, INVOICE_DATE)) >= 1.5;

排序查询视图 #

如果需要按平均间隔月数降序排列结果，查询视图时添加ORDER BY：

SELECT * FROM AVGMNTHSBETWEEN ORDER BY MONTHS_BETWN DESC;

关键说明 #

• MONTHS_BETWEEN(end_date, start_date)：参数顺序为结束日期在前、开始日期在后，若颠倒会返回负数结果。
• ROUND(avg_value, 1)：对聚合后的平均值进行四舍五入，保留1位小数。
• HAVING子句直接基于聚合后的平均值筛选，确保逻辑正确。

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