原文地址:https://codedefault.com/s/how-can-i-retrieve-the-last-record-in-each-group-mysql
问题描述
比如,在MySQL
数据库中,有数据表messages
和数据记录,如下:
Id Name Other_Columns
-------------------------
1 A A_data_1
2 A A_data_2
3 A A_data_3
4 B B_data_1
5 B B_data_2
6 C C_data_1
如果执行MySQL
分组查询语句,如下:
select * from messages group by name
则会返回所有按name
分组的第一条数据,如下:
1 A A_data_1
4 B B_data_1
6 C C_data_1
那么,如何查询按name
分组后返回每组最后一条数据的MySQL
语句呢,返回的结果如下:
3 A A_data_3
5 B B_data_2
6 C C_data_1
方案一
在MySQL
8.0版本以前,可以使用如下的语句:
WITH ranked_messages AS (
SELECT m.*, ROW_NUMBER() OVER (PARTITION BY name ORDER BY id DESC) AS rn
FROM messages AS m
)
SELECT * FROM ranked_messages WHERE rn = 1;
在MySQL
8.0版本中,MySQL
新增了对窗口函数(Window Functions)的支持,我们可以使用窗口函数来简化SQL
查询语句,并且不需要使用GROUP BY
子句,如下:
WITH ranked_messages AS (
SELECT m.*, ROW_NUMBER() OVER (PARTITION BY name ORDER BY id DESC) AS rn
FROM messages AS m
)
SELECT * FROM ranked_messages WHERE rn = 1;
方案二
使用MySQL
的IN(...)
子句,如下:
SELECT id, name, other_columns
FROM messages
WHERE id IN (
SELECT MAX(id)
FROM messages
GROUP BY name
);
方案三
使用MySQL
的GROUP_CONCAT
和SUBSTRING_INDEX
来查询,如下:
SELECT
`Id`,
`Name`,
SUBSTRING_INDEX(
GROUP_CONCAT(
`Other_Columns`
ORDER BY `Id` DESC
SEPARATOR '||'
),
'||',
1
) Other_Columns
FROM
messages
GROUP BY `Name`
实践代码:
1 SELECT * from( 2 SELECT 3 ( @i := CASE WHEN @pre_parent_code = sem_shop_wangwang THEN @i + 1 ELSE 1 END ) rownum,t_sem_logs.*, 4 ( @pre_parent_code := sem_shop_wangwang ) 5 FROM 6 t_sem_logs, 7 ( SELECT @i := 0, @pre_parent_code := '' ) AS b 8 where sem_edit_time <= '2018-07-13' 9 GROUP BY 10 sem_shop_wangwang,id 11 ORDER BY sem_shop_wangwang desc,DATE_FORMAT(sem_edit_time,'%Y-%m-%d %H:%i:%s') DESC,sem_agree_state ASC 12 ) a where rownum = 1 GROUP BY sem_agree_state order by DATE_FORMAT(sem_edit_time,'%Y-%m-%d %H:%m:%s') DESC; 13