按照字段分组后查询各自最新的一条数据

最新推荐文章于 2024-10-10 10:51:03 发布

原创最新推荐文章于 2024-10-10 10:51:03 发布 · 2.1k 阅读

1 ·

CC 4.0 BY-SA版权

数据库专栏收录该内容

18 篇文章

订阅专栏

本文介绍如何使用SQL对数据进行分组，并查询每个分组中最新的记录。通过内连接与子查询实现，适用于需要获取按特定字段分组后的最新数据场景。

一、按照字段分组后查询各自最新的一条数据

SELECT
	id,
       `road_num`,
        FORMAT(spacing_percent,2),
        FORMAT(time_percent,2)
        FROM `transport_record_today` r
        INNER JOIN
        (SELECT task_num AS tn, road_num AS rn, MAX(id) nid FROM `transport_record_today` GROUP BY tn, rn) nr  
        ON r.id = nr.nid AND r.task_num = nr.tn

mysql对查询保留两位小数

FORMAT(字段,2)

二、按照字段分组，查该字段各自最新一条数据（查询条件为list集合）

<SELECT id = "queryEachNew" parameterType = "java.util.List" resultType = "com.xxx.xxx.entity.Record" > 
SELECT 
  * 
FROM
  `vehicle_record_today` v 
  INNER JOIN 
    (SELECT 
      task_num AS tn,
      MAX(id) nid 
    FROM
      `vehicle_record_today` 
    GROUP BY tn) n 
    ON v.id = nid 
    AND v.`task_num` = tn 
    AND task_num IN 
    < foreach collection = "list" INDEX = "index" item = "list" OPEN = "(" SEPARATOR = "," CLOSE = ")" > 
    #{list}
    < / foreach >
</SELECT>

<foreach>这个标签是用来循环传入的集合的，collection="list"这个参数中有list，map两种

Dao层代码：List<Record> queryEachNew(List<Long> list);