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