html仿qq消息列表,仿QQ消息列表页SQL

要做一个聊天功能，其中，有个列表页，跟QQ消息的一样。

先看表结构(MySQL)：

message_idint短消息索引id

message_parent_idint回复短消息message_id

from_member_idint短消息发送人

to_member_idvarchar短消息接收人

message_titlevarchar短消息标题

message_bodyvarchar短消息内容

message_opentinyint短消息打开状态

message_statetinyint短消息状态，0为正常状态，1为发送人删除状态，2为接收人删除状态

from_member_namevarchar发信息人用户名

to_member_namevarchar接收人用户名

先看需求：(箭头方向代表消息的发送方向)

消息列表 ==> 最后查询显示的消息

a->b ==> a->b

a->b,b->a ==> b->a

a->b,b->a,a->c ==> b->a,a->c

a->b,b->a,a->c,c->a ==> b->a,c->a

第一个想法：a发出的消息+a收到的消息-重复的消息

问题：a发出去的消息，不一定是最后的消息，并且，不好排除重复的消息。

第二个想法：a接收的或者发出去的消息，按照对方来分组，并去最后一条消息

问题：对方可能是发出者或者接收者，group by不能实现。

第三个想法：在第二个想法的基础上，实现一个2人之间唯一的字段，用来分组。取用户id，组成字段,ab,ac，这样，就可以实现分组，后面的问题就解决了。

如果按照from to来组合，那么可能出现的组合就有2种，ab,ba，这两种是等价的。但对于group by来说，则是两种组合。

这里需要创造一个唯一的组合：

CASE WHEN m1.from_member_id = 1 THEN CONCAT( m1.from_member_id, m1.to_member_id ) ELSE CONCAT( m1.to_member_id, m1.from_member_id ) END id

这里1为当前用户的id。

完整SQL如下：

SELECT

m2.*, CASE

WHEN m2.from_member_id = 1 THEN

m2.to_member_name

ELSE

m2.from_member_name

END show_name,

(

SELECT

member_avatar

FROM

az_member m3

WHERE

m3.member_id = (

CASE

WHEN m2.to_member_id = 1 THEN

m2.from_member_id

ELSE

m2.to_member_id

END

)

) member_avatar

FROM

az_message m2

WHERE

m2.message_id IN (

SELECT

max(zt.message_id)

FROM

(

SELECT

m1.*, CASE

WHEN m1.from_member_id = 1 THEN

CONCAT(

m1.from_member_id,

m1.to_member_id

)

ELSE

CONCAT(

m1.to_member_id,

m1.from_member_id

)

END id

FROM

az_message m1

WHERE

(

m1.from_member_id = 1

OR m1.to_member_id = 1

)

AND m1.message_type = 5

AND m1.message_open = 0

) zt

GROUP BY

id

)

过程总结：

1.创造一个唯一的字段。

2.按照唯一字段分组，并取最大id

3.取最大id所在行记录