mysql group by 如何解决聊天分组 取最新记录

Question

数据结构idsendIdreceiverIdpostDate1122015-04-0612:12:012132015-04-0612:13:013212015-04-0612:14:014232015-04-0612:15:015322015-04-0612:16:016412015-04-0612:17:017142015-04-0612:18:018242015-04-0612:19:01意思就是按照（1-2，2-1）（1-3，3-1）（1-4，4-1）（2-4,4-2)这样分组，并且取分组最新的数据，为了实现聊天记录的获取。

Answer 1

假定你表名为message
select*frommessagewhere(selectcount(*)frommessageasmwherem.sendId=message.sendIdandm.postDate>=message.postDate)<2
                            

Answer 2

下面有两种方法:假设你的表叫t_biz_sign,'最新列'字段叫create_time,需要分组的字段叫foreign_idSELECTsign.*FROMt_biz_signsignWHERENOTexists(SELECT1
FROMt_biz_sign
WHEREforeign_id=sign.foreign_id
ANDsign.create_timeORDERBYcreate_timeDESC;
SELECT*FROMt_biz_signsignWHERE(SELECTcount(*)
FROMt_biz_signASm
WHEREm.foreign_id=sign.foreign_idANDm.create_time>=sign.create_time)<2;