mysql 查询各个状态的最新的3条记录

先创建表

CREATE TABLE test(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    status INT(11),
    value VARCHAR(20)
);

写入部分数据

INSERT INTO test(status,value) VALUES
(1,'测试1'),
(2,'测试2'),
(3,'测试3'),
(1,'测试4'),
(2,'测试5'),
(3,'测试6'),
(2,'测试7'),
(2,'测试8'),
(4,'测试9'),
(1,'测试10');

 

1、既然是查询各个状态那么就需要用到分组（group by）来实现

select * from test group by status

此时发现查询出来的数据每个状态只有一条而且不是最新的
2、再添加排序（order by）和限制（limit）发现没有达到预期效果

select * from test group by status order by id DESC limit 3

3、想想筛选有关的还有一个having函数，这个函数可以处理筛选分组之后的数据

4、将test表和自身进行leftjoin，可以找到每条状态数据中id大于自身的数量，通过这个数量的筛选来进行最新的筛选，此时分组的条件也需要修改下

select a.id,a.status,a.value,count(b.id) from test as a LEFT JOIN test b on b.status = a.status and b.id > a.id group by a.id,a.status,a.value

这样就能查询出，每条数据相同状态下ID大于自身的数量，根据这个数量的筛选就能查询出最新的数据

5、使用having函数筛选分组后的数据

select a.id,a.status,a.value,count(b.id) as count from test as a LEFT JOIN test b on b.status = a.status and b.id > a.id group by a.id,a.status,a.value having count < 3

此时查询出来的数据就是每个状态中最新的三条记录
6、还可以再加上order by来对数据进行进一步的排序

select a.id,a.status,a.value,count(b.id) as count from test as a LEFT JOIN test b on b.status = a.status and b.id > a.id group by a.id,a.status,a.value having count < 3 ORDER BY a.status DESC, a.id DESC