示例
我想从下表中选择具有相同组值的 5 个最新行。
--------------------------------------------
| id | group | timestamp |
--------------------------------------------
| 1 | circle | 1468287300 |
| 2 | square | 1468287816 |
| 3 | square | 1468287694 |
| 4 | circle | 1468287252 |
| 5 | circle | 1468287987 |
| 6 | circle | 1468287068 |
| 7 | square | 1468287149 |
| 8 | circle | 1468287422 |
--------------------------------------------
我认为这应该有效，请检查并让我知道
SELECT * FROM Table1 GROUP BY group ORDER BY timestamp DESC LIMIT 5;
试试这个：
SELECT t.`id`, t.`group`, t.`timestamp`
FROM (
SELECT
t1.*,
IF(@grp = `group`, @rowno := @rowno + 1, @rowno := 1) AS rowno,
@grp := `group`
FROM (
SELECT *
FROM yourtable
ORDER BY `group`, `timestamp` DESC
) t1
CROSS JOIN (SELECT @grp := null, @rowno := 0) t2
) t
WHERE rowno < 6