我的测试表结构如下:
+-----------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| from_id | int(11) | YES | | NULL | |
| to_id | int(11) | YES | | NULL | |
| content | varchar(500) | YES | | NULL | |
| conversation_id | varchar(50) | YES | | NULL | |
| create_date | datetime | YES | | NULL | |
| has_read | tinyint(4) | YES | | NULL | |
+-----------------+--------------+------+-----+---------+----------------+
首先说网上很容易搜索到的版本,先排序,在根据排序的结果集进行分组。但是我的MySQL就是不行,查询出来的结果还是按照时间顺序排序。
select *
from (select *
from message
where from_id=25
order by create_date desc) s
group by conversation_id;
最后给出我的解决方案
使用外连接获得最大的时间,同时保留一些分组信息,在连接时通过这些分组信息保证连接数据的一致性。
select a.*
from message as a
right join (select from_id, to_id, max(create_date) as create_date
from message
where to_id=#{toId}
group by conversation_id) as b
on a.from_id=b.from_id and
a.to_id=b.from_id and
a.create_date=b.create_date
order by create_date desc;
最后的排序语句是为了让每个分组也按时间逆序。