MySQL如何只取根据某列连续重复行的第一条记录

前言

MySQL如何只取根据某列连续重复行的第一条记录，条件：某列、连续、验重

建表准备

DROP TABLE IF EXISTS `test`;
CREATE TABLE `test`  (
  `id` bigint NOT NULL,
  `time` datetime NULL DEFAULT NULL,
  `price` int NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

数据准备

方案

方案一：验重列组内分配排名&取排名第一个

SET @prev_value = NULL;
SET @rank = 0;

SELECT
  b.id,
  b.time,
  b.price 
FROM
  (
  SELECT
    a.id,
    a.time,
    a.price,
    @rank := IF ( @prev_value = a.price, @rank + 1, @rank := 0 ) AS order_num,
    @prev_value := a.price 
  FROM
    ( SELECT * FROM test ORDER BY id ) a 
  ) b 
WHERE
  b.order_num = 0;

• @prev_value 用于存储验重列前一行的值。
• @rank 用于给每个连续重复块内的每行分配一个排名。
  
  ps： 此处 SELECT * FROM test ORDER BY id 根据id排序，可根据实际情况调整也可删除，加上最后的 WHERE b.order_num = 0 后就是取第一条数据；

方案二：验重列分组序号&组内取第一个

SET @prev_value = NULL;
SET @group_id = 0;
    
SELECT
  a.id,
  a.time,
  a.price 
FROM
  test a
  INNER JOIN ( 
    SELECT 
      MIN( b.id ) AS min_id 
    FROM
    (
    SELECT
      d.id,
      d.time,
      d.price,
      @group_id := IF( @prev_value = d.price, @group_id, @group_id + 1 ) AS group_id,
      @prev_value := d.price 
    FROM
      ( SELECT * FROM test ORDER BY id ) d
    ) AS b 
  GROUP BY
  b.group_id 
  ) AS c ON a.id = c.min_id;

• @prev_value 用于存储验重列前一行的值。
• @group_id 用于给每个连续重复块分配一个组号。
  
  ps： 此处 SELECT * FROM test ORDER BY id 根据id排序，可根据实际情况调整也可删除，每组数据分配一个组号，然后再根据组号分组取第一条数据；