如何修改现有SQL查询，实现同一ID同时有1601/1602时仅保留1602

测试数据表结构与数据 #

CREATE TABLE A11 
(
    ID VARCHAR(7),
    EventID VARCHAR(5),
    index_1 INT,
    Memo VARCHAR(30)
);

INSERT INTO A11 (ID, EventID, index_1, Memo)
VALUES ('DAP', '1602', 2, 'h@gmail.com'),
       ('DAP', '1602', 78, 'female'),
       ('DAP', '1602', 79, 'not female'),
       ('179', '1601', 5, 's@gmail.com'),
       ('179', '1602', 9, 's@gmail.com'),
       ('GHJ', '1601', 89, 'male');

全表查询结果：

ID  EventID index_1 Memo
--------------------------------
DAP 1602    2       h@gmail.com
DAP 1602    78      female
DAP 1602    79      not female
179 1601    5       s@gmail.com
179 1602    9       s@gmail.com
GHJ 1601    89      male

需求与现有查询问题 #

核心需求：

• 当EventID='1602'时，至少保留一条含@和一条不含@的Memo记录
• 若同一ID同时存在EventID='1601'和EventID='1602'的记录，仅保留EventID='1602'的记录

现有查询语句：

SELECT 
    * 
FROM  
    (SELECT 
         ID, EventID, index_1, Memo,
         ROW_NUMBER() OVER (PARTITION BY 
                                ID, EventID, 
                                CASE WHEN Memo NOT LIKE '%@%' 
                                          AND EventID = '1602' 
                                         THEN 'F' 
                                         ELSE 'Y' 
                                END 
                            ORDER BY index_1 DESC) AS SortId
     FROM 
         A11
     WHERE 
         EventID IN ('1601', '1602')
    ) g 
WHERE 
    g.SortId = 1

该查询的问题在于：ID='179'同时保留了EventID='1601'和EventID='1602'的记录，不符合第二个需求。

基于现有代码的修改方案 #

可以通过新增一个窗口函数标记每个ID是否存在EventID='1602'的记录，再结合原有逻辑过滤即可：

SELECT 
    ID, EventID, index_1, Memo, SortId
FROM  
    (SELECT 
         ID, EventID, index_1, Memo,
         ROW_NUMBER() OVER (PARTITION BY 
                                ID, EventID, 
                                CASE WHEN Memo NOT LIKE '%@%' 
                                          AND EventID = '1602' 
                                         THEN 'F' 
                                         ELSE 'Y' 
                                END 
                            ORDER BY index_1 DESC) AS SortId,
         -- 新增：标记当前ID是否存在EventID=1602的记录
         MAX(CASE WHEN EventID = '1602' THEN 1 ELSE 0 END) OVER (PARTITION BY ID) AS has_1602
     FROM 
         A11
     WHERE 
         EventID IN ('1601', '1602')
    ) g 
WHERE 
    g.SortId = 1
    -- 新增过滤条件：如果ID有1602记录，则只保留1602的行；否则保留1601的行
    AND (g.has_1602 = 0 OR g.EventID = '1602')

期望输出结果 #

ID  EventID index_1 Memo        SortId
--------------------------------------
179 1602    9       s@gmail.com 1
DAP 1602    79      not female  1
DAP 1602    2       h@gmail.com 1
GHJ 1601    89      male        1

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