Oracle SQL查询：同一交易标识同月不同起止日期记录及报错处理

错误原因 #

ORA-00904: "MONTH": invalid identifier 是因为Oracle没有MONTH()内置函数，你写的是MySQL的语法。Oracle提取月份需要用EXTRACT(MONTH FROM 日期列)或者TO_CHAR(日期列, 'MM')，更严谨的年月判断可以用TRUNC(日期列, 'MM')（截断到当月第一天）。

需求匹配的SQL修正 #

你的需求是：找出transaction_identifier相同、记录属于同一月份（更建议按年月区分）、仅delivery_start_date和delivery_end_date存在差异的记录。原SQL逻辑存在偏差，以下是修正后的两种版本：

版本1：按月份（不区分年份）查询 #

SELECT *
FROM unique_delivery_locations
WHERE transaction_identifier IN (
    SELECT transaction_identifier
    FROM unique_delivery_locations
    -- 确保单条记录的起止日期在同一个月份
    WHERE EXTRACT(MONTH FROM delivery_start_date) = EXTRACT(MONTH FROM delivery_end_date)
    GROUP BY transaction_identifier, EXTRACT(MONTH FROM delivery_start_date)
    -- 同一交易+月份下，存在至少2组不同的起止日期组合
    HAVING COUNT(DISTINCT (delivery_start_date, delivery_end_date)) > 1
)
-- 过滤出起止日期在同月的记录
AND EXTRACT(MONTH FROM delivery_start_date) = EXTRACT(MONTH FROM delivery_end_date);

版本2：按年月（更严谨，避免跨年度同月混淆）查询 #

SELECT *
FROM unique_delivery_locations
WHERE transaction_identifier IN (
    SELECT transaction_identifier
    FROM unique_delivery_locations
    -- 确保单条记录的起止日期在同一年月
    WHERE TRUNC(delivery_start_date, 'MM') = TRUNC(delivery_end_date, 'MM')
    GROUP BY transaction_identifier, TRUNC(delivery_start_date, 'MM')
    -- 同一交易+年月下，存在至少2组不同的起止日期组合
    HAVING COUNT(DISTINCT (delivery_start_date, delivery_end_date)) > 1
)
-- 过滤出起止日期在同一年月的记录
AND TRUNC(delivery_start_date, 'MM') = TRUNC(delivery_end_date, 'MM');

关键逻辑说明 #

• TRUNC(日期, 'MM')：将日期截断到当月第一天，能精准判断两条记录是否属于同一年月，比单独提取月份更可靠。
• COUNT(DISTINCT (delivery_start_date, delivery_end_date)) > 1：用来识别同一交易+年月分组下，存在不同的起止日期组合，满足你“仅这两个字段有差异”的核心要求。

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