Oracle SQL查询需求：筛选无S活动的最新M活动记录

问题背景 #

现有Oracle数据表结构及示例数据如下：

需要查询满足以下条件的记录：

• 仅包含Activity = 'M'的记录
• 对应的ID从未有过Activity = 'S'的记录（无论日期）
• 每个符合条件的ID只保留最新日期的记录

期望输出：

• 2222 on 2023/04/11
• 5555 on 2023/04/11

可行解决方案 #

方案一：NOT EXISTS + 窗口函数（推荐大数据量场景） #

WITH ranked_m_records AS (
    SELECT 
        ID,
        Date,
        -- 按ID分组，日期倒序排名，最新记录排第1
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Date DESC) AS record_rank
    FROM your_table_name
    WHERE Activity = 'M'
)
SELECT ID || ' on ' || Date AS result
FROM ranked_m_records
WHERE record_rank = 1
-- 排除存在S记录的ID
AND NOT EXISTS (
    SELECT 1 
    FROM your_table_name t2
    WHERE t2.ID = ranked_m_records.ID
    AND t2.Activity = 'S'
);

逻辑说明

1. 用ROW_NUMBER()窗口函数给每个ID的M活动记录按日期倒序排名，确保每个ID的最新记录排名为1
2. 通过NOT EXISTS子查询过滤掉所有有过S活动的ID
3. 最后筛选出排名为1的记录，格式化为需求的输出样式

方案二：GROUP BY + NOT IN（简洁写法） #

SELECT 
    ID || ' on ' || MAX(Date) AS result
FROM your_table_name
WHERE Activity = 'M'
-- 排除所有有过S活动的ID
AND ID NOT IN (
    SELECT DISTINCT ID 
    FROM your_table_name
    WHERE Activity = 'S'
)
GROUP BY ID;

逻辑说明

1. 子查询先提取所有存在S活动的ID列表
2. 主查询筛选出M活动且不在上述ID列表中的记录
3. 按ID分组，取每个ID的最大日期（即最新记录），再格式化为需求样式

注意：如果ID字段可能存在NULL值，NOT IN会导致异常结果，此时优先选择方案一的NOT EXISTS写法。

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