mySql查询一对多，取多的排序的第一条数据

码海兴辰

已于 2022-10-29 21:14:47 修改

阅读量2.7k

点赞数 1

分类专栏： JAVA架构文章标签： mysql 1024程序员节

于 2022-06-09 09:06:37 首次发布

本文链接：https://blog.csdn.net/LRXmrlirixing/article/details/125181258

版权

JAVA架构专栏收录该内容

139 篇文章 6 订阅

订阅专栏

1.在我们的日常开发中，常遇到一对多的表，我们想查询主表数据，然后查询多表的某个条件的信息。

方法一：查询多表，jion主表查询，这个常规的查询

方法二：我们想查主表，顺便查询多表的数据

二：列如我们查询主表，并查询多表最大id的数据，sq如下：

      SELECT
        be.create_time,
        be.update_time,
        be.creater,
        be.updater,
        be.serial_number,
        be.longitude,
        be.latitude,
        be.battery_status,
        be.rescue_num,
        be.create_time,
        be.equipment_state,
        be.warranty_start_time,
        be.warranty_end_time,
        be.es_start_time,
        be.es_end_time,
        be.address,
        be.id,
        be.network_status,
        be.new_serial_number,
        be.asset_number,
        be.installation_details,
        be.counterpart_name,
        be.counterpart_telephone,
        be.counterpart_mail,
        be.sub_level_counterpart_name,
        be.sub_level_counterpart_telephone,
        be.sub_level_counterpart_mail,
        be.battery_start_time,
        be.battery_end_time,
        be.electronic_fence_offset,
        be.province,
        be.city,
        be.county,
        be.install_longitude,
        be.install_latitude,
        be.retire_type,
        oi.file_create_time,
        oi.file_status,
        oi.checkFileId,
        so.`name` orgName
        FROM
        business_equipment be
        LEFT JOIN security_organization so ON be.org_id = so.id
        LEFT JOIN (
    SELECT bescf.id  checkFileId,bescf.equipment_id,bescf.file_create_time,bescf.file_status FROM business_equipment_self_check_file bescf
    LEFT JOIN (
        SELECT MAX(id) AS id, equipment_id FROM business_equipment_self_check_file GROUP BY equipment_id
    ) AS b ON bescf.equipment_id = b.equipment_id
WHERE bescf.id = b.id
) AS oi ON oi.equipment_id = be.id
WHERE
 be.valid = 1
ORDER BY be.create_time DESC

如图效果：