mysql多表关联一对多查询，取最新关联对应的数据

天客慢

已于 2025-04-02 14:47:57 修改

阅读量201

点赞数

CC 4.0 BY-SA版权

文章标签： mysql 数据库

于 2025-04-01 15:34:22 首次发布

本文链接：https://blog.csdn.net/weixin_45898111/article/details/146914335

业务查询sql ，这个是在mybatis xml 中的

 SELECT
            sr.id,
            sr.code,
            sr.remark ,
            sr.create_by,
            sr.create_time,
            sr.state,
            (
                SELECT COUNT(a.storage_record_id)
                FROM sjy_mes_storage_record_detail a  
                WHERE a.deleted = 0
                  AND a.storage_record_id = sr.id
            ) AS number, -- 统计关联表的所有数据
            po_subquery.code as productionOrderCode,
            po_subquery.name as lineWarehouseName,
            po_subquery.product_id
        FROM
            sjy_mes_storage_record sr --这个是主表信息 可以替换自己想要的
                inner JOIN ( -- 这个有个问题如果是用 left join 查询相当于扫描全表，会造成性能下降改成inner join 只查对应交集得数据,这样就不会有 但是数据多也是会 
                SELECT wopc.product_code, po.id, po.code, po.product_id, po.line_warehouse_id, lw.name
                FROM sjy_mes_work_order_product_code wopc
                         LEFT JOIN sjy_mes_work_order wo ON wopc.work_order_id = wo.id AND wo.deleted = 0
                         LEFT JOIN sjy_mes_production_order po ON wo.production_order_id = po.id AND po.deleted = 0
                         LEFT JOIN sjy_mes_line_warehouse lw ON po.line_warehouse_id = lw.id AND lw.deleted = 0
                WHERE wopc.deleted = 0
            ) po_subquery ON ( SELECT srd.product_code -- 根据关联表获取最新数据的信息和需要的数据做关联（比如你需要什么数据就和什么数据关联，前提是必须是按主表查询，主表中能找到唯一的数据）
                               FROM sjy_mes_storage_record_detail srd
                               WHERE srd.deleted = 0
                                 AND srd.storage_record_id = sr.id
                               ORDER BY srd.id DESC
                               LIMIT 1) = po_subquery.product_code
        WHERE
            sr.deleted = 0
        <if test="storageRecordProductVO.state != null and storageRecordProductVO.state != ''">
            and sr.state = #{storageRecordProductVO.state}
        </if>
        <if test="storageRecordProductVO.code != null and storageRecordProductVO.code != ''">
            and sr.code like concat('%',#{storageRecordProductVO.code},'%')
        </if>
        ORDER BY
            sr.id DESC