查询子表最新的一条数据并与主表关联

1、子表按照时间排序选择最近的一条与主表关联

select scp.*,ar.audit_unit from scheme_compile_plan scp left join audit_record ar on scp.id = ar.biz_id where ar.biz_type = #{bizType} order by ar.audit_finish_time desc limit 1)

 

2、子表通过group by查询出最大时间并与子表关联查询出时间最大的一条记录，然后与主表关联查询

 select scp.*,ar.audit_unit from scheme_compile_plan scp left join (select ara.* from audit_record ara left join
        (SELECT *,MAX(audit_finish_time) as max from audit_record group by biz_type,biz_id) as b on ara.biz_id = b.biz_id and ara.biz_type=b.biz_type where ara.audit_finish_time = b.max) ar
        on scp.id = ar.biz_id and ar.biz_type = #{bizType} order by ar.audit_finish_time desc