Oracle中按指定条件筛选并获取最新更新数据的SQL查询问题

需求说明 #

筛选表TRANSACTIONAL.TBL_EVENT_NOTIFICATION_DETAILS中符合以下要求的记录：

• RECIEVER_ROLE_ID 等于 'B'
• ACTION_STATUS 等于 1
• 同时满足：RECIEVER_ID为0或593728771，IS_AVAILABLE=1，STATE_ID=24，DISTRICT_ID=491，NOTIFICATION_ID=4426
• 对每个RECIEVER_ID，仅保留UPDATED_AT时间最新的条目

原尝试SQL #

SELECT
        "NOTIFICATION_DETAILS_ID",
        "RECIEVER_ROLE_ID",
        "RECIEVER_ID",
        "STATE_ID",
        "DISTRICT_ID",
        "ACTION_STATUS",
        "IS_AVAILABLE",
        "CREATED_AT",
        "UPDATED_AT",
        "CREATED_YEAR",
        "NOTIFICATION_ID",
        ROW_NUMBER() OVER (PARTITION BY "RECIEVER_ID" ORDER BY "UPDATED_AT" DESC) as R
    FROM
        "TRANSACTIONAL"."TBL_EVENT_NOTIFICATION_DETAILS"
    WHERE
        (("RECIEVER_ID" =0)
            OR ("RECIEVER_ID" =593728771))
        AND ("RECIEVER_ROLE_ID" ='B')
        AND ("IS_AVAILABLE" =1)
        AND "STATE_ID" =24
        AND "DISTRICT_ID" =491
        AND NOTIFICATION_ID =4426

修正后的SQL #

SELECT *
FROM (
    SELECT
        "NOTIFICATION_DETAILS_ID",
        "RECIEVER_ROLE_ID",
        "RECIEVER_ID",
        "STATE_ID",
        "DISTRICT_ID",
        "ACTION_STATUS",
        "IS_AVAILABLE",
        "CREATED_AT",
        "UPDATED_AT",
        "CREATED_YEAR",
        "NOTIFICATION_ID",
        ROW_NUMBER() OVER (PARTITION BY "RECIEVER_ID" ORDER BY "UPDATED_AT" DESC) AS R
    FROM
        "TRANSACTIONAL"."TBL_EVENT_NOTIFICATION_DETAILS"
    WHERE
        "RECIEVER_ID" IN (0, 593728771)
        AND "RECIEVER_ROLE_ID" = 'B'
        AND "IS_AVAILABLE" = 1
        AND "STATE_ID" = 24
        AND "DISTRICT_ID" = 491
        AND "NOTIFICATION_ID" = 4426
        AND "ACTION_STATUS" = 1
) AS subquery
WHERE R = 1

关键修改点 #

• 添加ACTION_STATUS = 1条件，匹配需求中的核心筛选要求
• 将原查询嵌套为子查询，在外层通过R = 1过滤，确保每个RECIEVER_ID只返回UPDATED_AT最新的记录
• 将RECIEVER_ID的OR判断简化为IN语句，优化代码可读性

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