仿QQ消息列表页SQL
博客专区 > qii 的博客 > 博客详情
仿QQ消息列表页SQL
qii 发表于1年前
仿QQ消息列表页SQL
  • 发表于 1年前
  • 阅读 140
  • 收藏 0
  • 点赞 0
  • 评论 0

腾讯云 技术升级10大核心产品年终让利>>>   

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

先看表结构(MySQL):

message_id	int		短消息索引id
message_parent_id	int		回复短消息message_id	
from_member_id	int		短消息发送人		
to_member_id	varchar	短消息接收人	
message_title	varchar	短消息标题
message_body	varchar	短消息内容
message_open	tinyint	短消息打开状态	
message_state	tinyint	短消息状态,0为正常状态,1为发送人删除状态,2为接收人删除状态
from_member_name	varchar	发信息人用户名	
to_member_name	varchar	接收人用户名

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

消息列表  ==>  最后查询显示的消息
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所在行记录

共有 人打赏支持
粉丝 14
博文 77
码字总数 23520
×
qii
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: