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`

二、获取右表最新的多条记录

要查询右表中最新的多条记录，你可以使用子查询结合LEFT JOIN，确保左表的记录保留，并且只选择右表中最新的记录。

以下是一个示例SQL查询，假设右表名为right_table，它有一个时间戳字段created_at，且左表和右表通过left_table_id字段关联：

SELECT l.*, r.*
FROM left_table l
LEFT JOIN (
    SELECT 
        r.*
    FROM right_table r
    INNER JOIN (
        SELECT 
            left_table_id, 
            MAX(created_at) as latest_created_at
        FROM right_table
        GROUP BY left_table_id
    ) as latest_records ON r.left_table_id = latest_records.left_table_id
      AND r.created_at = latest_records.latest_created_at
) as r ON l.id = r.left_table_id;

这个查询中，内部的子查询首先为每个left_table_id找到最新的created_at时间戳。然后，外部的左连接查询将这些最新记录与左表进行匹配。这样，即使右表中不存在最新记录，左表的记录也会被保留，并且会在结果集中显示为NULL。

实际开发：

先根据groupby获取每个index_code日期最新的一条记录。

SELECT  index_code,  MAX(end_date) as latest_end_date FROM tb_item_coal_price_mine GROUP BY index_code

然后再与右表通过index_code和latest_end_date进行关联查询出右表日期最新的所有记录

SELECT  r.* FROM tb_item_coal_price_mine r
    INNER JOIN (
        SELECT  index_code,  MAX(end_date) as latest_end_date FROM tb_item_coal_price_mine GROUP BY index_code
    ) as latest_records ON r.index_code = latest_records.index_code AND r.end_date = latest_records.latest_end_date

最后再与左表关联查询

SELECT l.*, r1.* FROM pub_item_unit_info l
LEFT JOIN (
    SELECT  r.* FROM tb_item_coal_price_mine r
    INNER JOIN (
        SELECT  index_code,  MAX(end_date) as latest_end_date FROM tb_item_coal_price_mine GROUP BY index_code
    ) as latest_records ON r.index_code = latest_records.index_code AND r.end_date = latest_records.latest_end_date
) as r1 ON l.index_code = r1.index_code where l.index_name = '龙王沟煤矿';