MySQL LEFT JOIN 右表有多条数据但只取最新的一条

原 SQL：

-- 简单说明：t1 表是单据表，t2 表是审核记录表
-- t2.type = 2 表示复审、t2.object_id 存的就是单据 id，t2.result = 1 表示审核通过
-- 需求：查询单据信息的同时查出该单据复审通过的时间
SELECT t1.id          AS id,
       t1.bill_no     AS billNo,
       t1.submit_time AS submitTime,
       t2.audit_time  AS reviewAuditTime
FROM order_bill_info AS t1
         LEFT JOIN sys_audit_record AS t2
                   ON t2.object_id = t1.id
                       AND t2.type = 2
                       AND t2.result = 1
                       AND t2.is_deleted = 0
WHERE t1.is_deleted = 0
  AND t1.bill_no = 'DC202308030026'
ORDER BY submitTime DESC

一开始负责人说单据审核通过的记录只会有一条，不存在多条审核通过的记录，那么上面的 SQL 是完全 OK 的。但是实际测试一轮下来发现我这边查询结果里有重复数据，去数据库看了数据后发现审核通过的记录有多条，找人一问才知道，单据审核被驳回后还可以重新走审核流程，并且以前的审核通过记录还保留着的，导致我这边关联查询审核信息时就出现重复数据了，沟通一番后，最终让我暂时先取最新的一条审核记录，于是就只有在关联条件上再增加一个关联子查询，这个子查询的作用就是找到满足关联条件的数据中“最新”的数据。

改写后的 SQL：

SELECT t1.id          AS id,
       t1.bill_no     AS billNo,
       t1.submit_time AS submitTime,
       t2.audit_time  AS reviewAuditTime
FROM order_bill_info AS t1
         LEFT JOIN sys_audit_record AS t2
                   ON t2.object_id = t1.id
                       AND t2.type = 2
                       AND t2.result = 1
                       AND t2.is_deleted = 0
-- 上面都是正常的关联条件，后面这个 AND 条件就是为了筛选出“最新”的一条数据
                       AND t2.id =
                           (SELECT MAX(id)
                            FROM sys_audit_record
                            WHERE type = 2
                              AND result = 1
                              AND object_id = t1.id
                              AND is_deleted = 0)
WHERE t1.is_deleted = 0
  AND t1.bill_no = 'DC202308030026'
ORDER BY submitTime DESC