/* 最后一条记录的时间*/
SELECT MAX(created_at) ,yundian_id FROM yundian_message ym group by yundian_id;/* 云店分组*/
SELECT yundian_id FROM yundian_message ym group by yundian_id;/* MySQL数据库中如何查询分组后每组中的最后一条记录?*/
SELECT id, name, other_columns
FROM messages
WHERE id IN (
SELECT MAX(id)
FROM messages
GROUP BY name
);//提供了云店最新评论的数据
$sql = "SELECT a.*,ym.msg_content FROM yundian_info a left join
(select yundian_id,msg_content from yundian_message WHERE id IN (
SELECT MAX(id) FROM yundian_message ym group by yundian_id
)) ym on a.id =ym.yundian_id WHERE yunyu_id={$yunyu_id} and state =0 ";
问题描述
比如,在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的IN(...)子句,如下:
SELECT id, name, other_columns
FROM messages
WHERE id IN (
SELECT MAX(id)
FROM messages
GROUP BY name