高级SQL查询需求：查询工单resolve事件及各工单最新事件记录

业务表结构与样例数据 #

现有存储工单事件的业务表，结构及样例数据如下：

Ticket Id   EventId     EventDate    EventDescription
1             1        2021-01-06        create
1             2        2021-01-07        resolve
2             3        2021-01-06        create
3             4        2021-01-15        create
1             5        2021-01-09        close
1             6        2021-01-12        Re-open
2             7        2021-01-10        Assign
2             8        2021-01-22        resolve

查询需求 #

• 若工单存在EventDescription为resolve的事件，需展示该条resolve事件记录
• 所有工单均需展示其最新的一条事件记录（按EventDate倒序取第一条）
• 若某条记录同时满足上述两个条件，仅展示一次即可

实现思路 #

• 分别筛选出所有resolve事件、每个工单的最新事件两个数据集
• 合并两个数据集后去重，避免重复展示同时满足两个条件的记录

通用SQL实现（支持MySQL8.0+、PostgreSQL、Oracle、SQL Server） #

SELECT DISTINCT `Ticket Id`, EventId, EventDate, EventDescription
FROM (
    SELECT 
        `Ticket Id`, 
        EventId, 
        EventDate, 
        EventDescription,
        -- 标记每个工单的事件排序，rn=1即为最新事件
        ROW_NUMBER() OVER (PARTITION BY `Ticket Id` ORDER BY EventDate DESC) AS rn,
        -- 标记是否为resolve事件
        CASE WHEN EventDescription = 'resolve' THEN 1 ELSE 0 END AS is_resolve
    FROM ticket_event_table -- 此处替换为实际表名
) t
WHERE t.rn = 1 OR t.is_resolve = 1
ORDER BY `Ticket Id`, EventDate;

如果使用不支持窗口函数的低版本MySQL，可使用关联子查询实现：

SELECT DISTINCT t.`Ticket Id`, t.EventId, t.EventDate, t.EventDescription
FROM ticket_event_table t -- 此处替换为实际表名
WHERE t.EventDescription = 'resolve'
OR EXISTS (
    SELECT 1
    FROM ticket_event_table t2
    GROUP BY t2.`Ticket Id`
    HAVING t2.`Ticket Id` = t.`Ticket Id` AND MAX(t2.EventDate) = t.EventDate
)
ORDER BY t.`Ticket Id`, t.EventDate;

查询结果 #

执行以上SQL后输出结果与期望一致：

Ticket Id   EventId     EventDate    EventDescription
1             2        2021-01-07        resolve
1             6        2021-01-12        Re-open
2             8        2021-01-22        resolve
3             4        2021-01-15        create

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