如何高效查询audit表中id_audit=25608晚于25864的分组行

表结构与示例数据 #

id | id_audit | id_booking
--------------------------
8  |  12345  |  10000
7  |  34567  |  10000
6  |  25608  |  10000
5  |  25864  |  10000
4  |  12345  |  10001
3  |  34567  |  10001
2  |  25864  |  10001
1  |  25608  |  10001

需求说明 #

按id_booking分组，仅当分组内id_audit=25608的记录（id越大代表更新越晚）的id大于id_audit=25864的记录id时，返回该分组中id_audit=25608的行。预期返回结果：

id | id_audit | id_booking
--------------------------
6  |  25608  |  10000

高效查询语句 #

利用已创建的(id_booking, id_audit)索引，以下查询能避免全表扫描，提升执行效率：

SELECT a.id, a.id_audit, a.id_booking
FROM audit a
INNER JOIN (
    SELECT 
        id_booking,
        MAX(CASE WHEN id_audit = 25608 THEN id END) AS id_25608,
        MAX(CASE WHEN id_audit = 25864 THEN id END) AS id_25864
    FROM audit
    WHERE id_audit IN (25608, 25864)
    GROUP BY id_booking
    HAVING id_25608 > id_25864
) AS filter ON a.id_booking = filter.id_booking 
           AND a.id_audit = 25608 
           AND a.id = filter.id_25608;

效率说明 #

1. WHERE id_audit IN (25608, 25864)直接过滤出目标审计类型，结合索引快速定位数据，减少扫描范围；
2. 子查询通过GROUP BY id_booking计算每组内两个目标审计记录的最大id，仅处理必要数据；
3. 最终关联仅匹配符合条件的25608记录，避免不必要的行读取。

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