博客

模仿csdn私信功能，实现左侧联系人列表功能的sql

难点在私信表收件人和发件人要调换之后分组，以及要和联系人的添加时间关联起来，同时要查询未读消息数量

先说表结构，一个私信内容表，一个联系人表

私信内容表：

联系人表：

SQL语句：

假设当前登录用户id为10000

# 查询对方用户id，最后练习时间，最后消息内容，消息类型（普通消息，图片等），用户昵称，用户头像，未读消息数量
SELECT r,MAX(information_date) info_time, information_content,information_type,u.nick_name,u.img, IFNULL(tmp.noread,0) noread  FROM 
(
(

	SELECT * FROM
		(
       # 先查询消息表发送者为本人的消息
		SELECT information_date,user_send AS s,user_recrption AS r, information_content, information_type FROM message_information WHERE user_send='10000' AND send_del='1'
		UNION
       # 调换接收者为本人的消息，命名别名注意调换，关联两个结果集
		SELECT information_date,user_recrption AS s,user_send AS r, information_content, information_type FROM message_information
       #注意union不可以分别使用order by，要关联之后再统一使用排序，这样分组之后消息内容会自动获取排序第一条的消息内容
		 WHERE user_recrption='10000' AND recrption_del='1' ORDER BY information_date DESC 
		) a
    # 按照对方用户id分组
	GROUP BY a.r
)
UNION 
# 查询联系人表，因为某些联系人刚添加进来还未发送过任何消息，union关联结果集，但是字段数量和名字要对应上，并入上面的消息结果集之后再一起统计最后时间
(SELECT create_date AS information_date, user_id AS s,contacts_id AS r, NULL AS information_content, NULL AS Information_type FROM message_contacts WHERE user_id='10000')
) b 
LEFT JOIN 
# 关联查询消息表接收者为当前用户的分组未读消息数量
(SELECT user_send, COUNT(1) noread FROM message_information WHERE user_recrption='10000' AND recrption_del='1' AND information_state='0' GROUP BY user_send)
tmp ON tmp.user_send = b.r
# 关联用户表查询用户的昵称和头像
LEFT JOIN info_user_message u ON u.user_id = b.r
# 再次根据对方用户id分组，因为消息表和联系人表都有数据会重复，并且要取最大的联系时间，最后根据最后的联系时间进行倒序排列
GROUP BY b.r ORDER BY info_time DESC