SQL一对多关联查询：如何筛选符合事件时间规则的item记录

现有两张表的结构与示例数据如下：

CREATE TABLE item (code string);
CREATE TABLE item_event (id int, item_code string, event int, date smalldatetime);

表数据示例 #

item 表 #

item_event 表 #

筛选规则 #

需要筛选满足以下任一条件的item：

1. 不存在event为456的关联item_event记录
2. 同时存在event为890和456的关联记录时，456记录的date值晚于890记录的date值

上述示例数据的预期返回结果为：item2、item4。

你可以先按item_code分组聚合，分别提取每个item的456事件和890事件的对应日期，再判断过滤条件即可，参考SQL如下：

SELECT i.code
FROM item i
LEFT JOIN (
    SELECT 
        item_code,
        MAX(CASE WHEN event = 456 THEN date END) AS dt_456,
        MAX(CASE WHEN event = 890 THEN date END) AS dt_890
    FROM item_event
    GROUP BY item_code
) e ON i.code = e.item_code
WHERE 
    -- 条件1：没有456的事件
    e.dt_456 IS NULL 
    -- 条件2：同时存在456和890事件，且456的日期晚于890
    OR (e.dt_456 IS NOT NULL AND e.dt_890 IS NOT NULL AND e.dt_456 > e.dt_890)

逻辑说明 #

• 内层子查询先对每个item聚合，分别拿到event=456和event=890的对应日期，没有对应事件的话字段值为NULL，用MAX兼容同个item同类型事件有多条的场景
• 外层WHERE条件直接匹配筛选规则：
  • 首先判断dt_456 IS NULL，对应第一个筛选规则
  • 剩下的场景判断是否同时存在两类事件，且456的日期晚于890的日期，对应第二个筛选规则
• 用LEFT JOIN可以保留item表里没有任何event记录的item，如果不需要这种场景换成INNER JOIN即可

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