mysql -按最后一条记录分组

Question

我的表有多个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;

Answer 2

"GROUP BY“的概念是获取类别。不选择记录。

SELECT DISTINCT n_no, datetime, category, status, etc 
FROM mytable 
WHERE category = 16 
AND status = 1 
AND datetime BETWEEN "2020-01-06 00:00:00" AND "2020-01-06 23:59:59" 
ORDER BY datetime DESC
LIMIT 1

通过这种方式，您可以获得每个n_no的最后日期的所有记录

Answer 3

你可以尝试下面这样的方法。

SELECT * FROM mytable WHERE pk_col in (SELECT max(pk_col) 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)

在这里，将pk_col替换为表的主列名称。

请找到示例here。