mysql - 按最后一条记录分组

Question

这个问题已经有答案了:

Retrieving the last record in each group - MySQL (33 个回答)

已关闭 3 年前。

我的表有多条记录n_no列，当我 GROUP BY ，它返回该组的第一条记录，如何获取最后一条记录？

SELECT * FROM mytable WHERE category = 16 AND status = 1 AND datetime >= "2020-01-06 00:00:00" AND datetime <= "2020-01-06 23:59:59" GROUP BY n_no ORDER BY datetime DESC LIMIT 0,30

谢谢。

[通过实践解决]

SELECT * FROM mytable WHERE (SELECT MAX(datetime) from mytable GROUP BY n_no) AND category = 16 AND status = 1 AND datetime >= "2020-01-06 00:00:00" AND datetime <= "2020-01-06 23:59:59" GROUP BY n_no ORDER BY datetime DESC LIMIT 0,30

Answer 1

假设您使用的是 MySQL 8+，那么 ROW_NUMBER() 可以在这里工作:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY n_no ORDER BY datetime DESC) rn
    FROM mytable
    WHERE category = 16 AND status = 1 AND
          datetime >= '2020-01-06' AND datetime < '2020-01-07'
)

SELECT *
FROM cte
WHERE rn = 1;