mysql一张表有属性id，a，b，time，id为uuid，当a和b多条记录相同时，取时间最早的那个

如果相同的记录（例如，a 和 b 的组合） 不是连续的，且希望对于每一组相同的 a 和 b 值，选择时间最早的记录，而不依赖它们是否在连续的行中，那么可以通过以下方式来实现：

解决方案

  1. 定义连续性： 连续的记录通常是基于 time 排序的，因此可以通过 ORDER BY time 来保证记录的顺序。
  2. 使用变量：需要一个办法来 标识哪些记录是连续的。可以通过使用 @变量 来对比当前记录与前一记录的 a 和 b 值是否相同。如果相同，则它们是连续的；如果不同，则认为它们是新的一组。
  3. 选取每组最早的记录：对于每组连续的 a 和 b 值，选取 time 最早的一条记录。

SQL 查询

假设表名为 records，字段为 id (UUID), a, b, time。

SELECT 
    a, 
    b, 
    MIN(time) AS min_time
FROM (
    SELECT 
        id, 
        a, 
        b, 
        time,
        @group_id := IF(@prev_a = a AND @prev_b = b, @group_id, @group_id + 1) AS group_id,
        @prev_a := a,
        @prev_b := b
    FROM records, (SELECT @prev_a := NULL, @prev_b := NULL, @group_id := 0) AS init
    ORDER BY time
) AS grouped
GROUP BY a, b, group_id

解释：

  1. 变量初始化：
    @prev_a := NULL, @prev_b := NULL, @group_id := 0 初始化了两个变量 @prev_a 和 @prev_b 用于存储前一条记录的 a 和 b 值，@group_id 用于标识连续组。
  2. 连续性判断：
    @group_id := IF(@prev_a = a AND @prev_b = b, @group_id, @group_id + 1)
    如果当前记录的 a 和 b 与前一记录相同（即连续），则 @group_id 保持不变；否则，@group_id 增加 1，表示新的一组开始。
  3. 时间最早记录选择：
    在子查询中，使用 MIN(time) 来选择每组中时间最早的记录。

示例数据

假设 records 表如下所示：

idabtime
uuid1122024-11-05 10:00:00
uuid2122024-11-05 09:00:00
uuid3122024-11-05 11:00:00
uuid4132024-11-05 08:00:00
uuid5132024-11-05 12:00:00
uuid6222024-11-05 08:30:00

假设要统计 a 和 b 连续的记录，最终只返回最早的时间记录（按时间排序）。

执行上述 SQL 查询后，返回的结果将会是：

idabtime
uuid2122024-11-05 09:00:00
uuid4132024-11-05 08:00:00
uuid6222024-11-05 08:30:00

MyBatis-Plus 实现

在 MyBatis-Plus 中，可以将上面的查询语句作为原生 SQL 查询来实现。以下是如何在 Mapper 接口中定义自定义查询。

Mapper 接口

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.model.Record;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface RecordMapper extends BaseMapper<Record> {

    @Select("SELECT a, b, MIN(time) AS min_time " +
            "FROM ( " +
            "    SELECT id, a, b, time, " +
            "           @group_id := IF(@prev_a = a AND @prev_b = b, @group_id, @group_id + 1) AS group_id, " +
            "           @prev_a := a, " +
            "           @prev_b := b " +
            "    FROM records, (SELECT @prev_a := NULL, @prev_b := NULL, @group_id := 0) AS init " +
            "    ORDER BY time " +
            ") AS grouped " +
            "GROUP BY a, b, group_id ")
    List<Record> getEarliestRecordsForConsecutiveGroups();
}

调用 Mapper 方法

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class RecordService {

    @Autowired
    private RecordMapper recordMapper;

    public List<Record> getEarliestRecordsForConsecutiveGroups() {
        return recordMapper.getEarliestRecordsForConsecutiveGroups();
    }
}

总结：

  • 使用 @group_id 变量来标识连续记录组，确保只有在 a 和 b 连续的情况下才合并记录。
  • 对于每组连续的记录，使用 MIN(time) 获取最早的记录。
  • 这种方法适用于在不连续的记录中，只选择那些连续的记录进行统计。
最后修改：2024 年 11 月 05 日
© 允许规范转载
如果觉得我的文章对你有用，请随意赞赏