mysql left join 查询右表的最新记录

left join 关联查询右表多条记录只保留最新的一条，可以通过max+group by实现

sql如下：

SELECT
    t1.*,
    t4.maxEndDate ,
    t4.Q_STANDARD,
    t4.COAL_CLAS
FROM
    pub_item_unit_info t1
    LEFT JOIN (
    SELECT
        t3.id,
        t3.INDEX_CODE,
        t3.maxEndDate,
        t3.Q_STANDARD,
        t3.COAL_CLAS
    FROM
        (
        SELECT
            t2.id AS id,
            t2.INDEX_CODE,
            max( t2.END_DATE ) AS maxEndDate ,
            t2.Q_STANDARD,
            t2.COAL_CLAS
        FROM
            tb_item_coal_price_mine t2 
        GROUP BY
            t2.INDEX_CODE 
        ) t3
    ) t4 ON t1.index_code = t4.INDEX_CODE
where t1.index_code='10021506220105'

说明：t1表与t2表通过index_code进行关联，t2表取end_date最新的一条记录，注意group by后面必须是index_code（即t1表与t2表关联的字段），否则无法查询出右表最新的一条记录，而是多条记录。

网上找到的sql如下：

SELECT
    `t1`.`id` AS `id`,
    `t1`.`keytasks` AS `keytasks`,
    `t1`.`content` AS `content`,
    `t1`.`company` AS `company`,
    `t1`.`achievegoal` AS `achievegoal`,
    `t1`.`setime` AS `setime`,
    `t4`.`createtime` AS `createtime` 
FROM
    `paastest`.`task_supervision` `t1`
    LEFT JOIN (
    SELECT
        `t3`.`id` AS `id`,
        `t3`.`supervisionid` AS `supervisionid`,
        `t3`.`createtime` AS `createtime` 
    FROM
        (
        SELECT
            `t2`.`id` AS `id`,
            `t2`.`supervisionid` AS `supervisionid`,
            max( `t2`.`createtime` ) AS `createtime` 
        FROM
            `paastest`.`task_supervision_child` `t2` 
        GROUP BY
            `t2`.`supervisionid` 
        ) `t3` 
    ) t4 ON `t1`.`id` = `t4`.`supervisionid`