select * from
(
SELECT *,
ROW_NUMBER() OVER(PARTITION BY id ORDER BY create_time DESC) AS rowNum
FROM 表A as c
) d where d.rowNum<=2
这条sql的意思是根据id进行分区,并以create_time进行倒序排序,查出每个id对应的记录中创建时间(create_time)最新的2条记录。
假设这是表A
id | create_time |
1 | 2021-7-9 |
1 | 2021-7-8 |
1 | 2021-7-7 |
2 | 2021-7-9 |
2 | 2021-7-8 |
查询结果为
id | create_time |
1 | 2021-7-9 |
1 | 2021-7-8 |
2 | 2021-7-9 |
2 | 2021-7-8 |
第一次用,记录一下
第二次用不了了,发现这方法在mysql5上用不了。。
然后就换了个方法用
SELECT
( @i := CASE WHEN @id= idTHEN @i + 1 ELSE 1 END ) AS rownum,
p.*,
( @id := id )
FROM
表a p,([email protected] := 0) AS c
GROUP BY
id,
pk(主键名,因为不加会触发sql_mode=only_full_group_by报错)
HAVING
rownum <= 2
ORDER BY
id,
create_time DESC