MySQL获取分组中的第一条数据和最后一条数据

mysql 8

WITH ranked_messages AS (SELECT m.*, ROW_NUMBER() OVER (PARTITION BY name ORDER BY id DESC) AS rnFROM messages AS m
)
SELECT * FROM ranked_messages WHERE rn = 1;

mysql 8 以前写法

需求：账号表有多条相同的staff_id的数据，按staff_id分组，取ID最大（也就是最新）的一行

方法一

容易出现性能瓶颈

select * from account where id in (select max(id) group by account
)

方法二

这里的关键是a.staff_id = b.staff_id,根据业务情况不同，实际是分组group by字段，然后通过左关联（一定是左关联）算出笛卡尔积，通过过滤条件 a.id < b.id和b.created_at IS NULL找到数据
反过来a.id > b.id是最小

SELECTa.id aid,b.id bid
FROMaccount aLEFT JOIN account b ON a.staff_id = b.staff_idAND a.id < b.id
WHEREb.created_at IS NULLAND a.staff_id = 1;

原理

不限制查询条件

SELECTa.id aid,b.id bid
FROMaccount aLEFT JOIN account b ON a.staff_id = b.staff_id and a.id < b.id 
WHEREa.staff_id = 123;

从上面结果可以看出a.id < b.id 可以关联出最大的数据是1219(没有任何数据比他大),关联值是null，所以上述基础再加一个条件 b.created_at IS NULL就可以实现需求要的数据

SELECTa.id aid,b.id bid
FROMaccount aLEFT JOIN account b ON a.staff_id = b.staff_idAND a.id > b.id
WHEREb.created_at IS NULL
and     a.staff_id = 10000034;

生产环境中方法二会比方法一性能高

方法三：存储过程
主要利用@rownum存储过程，操作前需要对数据进行维度排序，自行搜索，这里不介绍，因为写了这样的SQL难以在团队推广，阅读性和维护性更差

总结

已经使用方法二帮助上产环境解决SQL慢的问题，但是问题关键是不应该这样来查数据，极度消耗数据库性能，必要情况下需要冗余一张表来记录对应数据最新的id值