简单聊天的MySQL数据库模式一对一
简单聊天的MySQL数据库模式一对一
提问于 2017-12-15 04:51:49
我试着用以下模式进行简单的聊天：
mes_id int(10) unsigned Auto Increment
mes_useid_receiver int(10) unsigned
mes_useid_sender int(10) unsigned
mes_date int(10) unsigned
mes_message text
mes_read tinyint(3) unsigned [0]
mes_visible_for_who int(10) unsigned NULL NULL = both; 0 = none; ID user问题是，我如何才能得到接收到的消息和发送的消息的联系人列表？
，我正在尝试这样的东西：
SELECT use_name, MAX(mes_date) AS date
FROM message JOIN user ON mes_useid_receiver=use_id
WHERE uti_id!=1
AND (mes_useid_receiver=1 OR mes_useid_sender=1)
AND (mes_visible_for_who IS NULL OR mes_visible_for_who=1)
GROUP BY use_id
ORDER BY date DESC但是有了这个查询，我就可以得到发送消息给user_id = 1，的联系人，我也想得到user_id = 1发送的消息的联系人。
编辑：
有了工会，我几乎可以得到我想要的：
SELECT use_id, use_name, mes_read FROM message
JOIN user ON mes_useid_receiver=use_id
WHERE use_id!=1
AND (mes_useid_receiver=1 OR mes_useid_sender=1)
AND (mes_visible_for_who IS NULL OR mes_visible_for_who=1) GROUP BY use_id
UNION
SELECT use_id, use_name, mes_read FROM message
JOIN user ON mes_useid_sender=use_id
WHERE use_id!=1
AND (mes_useid_receiver=1 OR mes_useid_sender=1)
AND (mes_visible_for_who IS NULL OR mes_visible_for_who=1) GROUP BY use_id我需要mes_date来排序列表，但是如果我选择mes_date，我将得到重复的行。
谢谢你的帮助.
回答 1
Stack Overflow用户
回答已采纳
发布于 2017-12-15 05:36:33
如果您要使用这个根，那么我的建议如下所示：
SELECT * FROM(
(SELECT use_id, use_name, mes_read ,mes_date FROM message
JOIN user ON mes_useid_receiver=use_id
WHERE use_id!=1
AND (mes_useid_receiver=1 OR mes_useid_sender=1)
AND (mes_visible_for_who IS NULL OR mes_visible_for_who=1)) GROUP BY use_id
UNION
(SELECT use_id, use_name, mes_read ,mes_date FROM message
JOIN user ON mes_useid_sender=use_id
WHERE use_id!=1
AND (mes_useid_receiver=1 OR mes_useid_sender=1)
AND (mes_visible_for_who IS NULL OR mes_visible_for_who=1)) GROUP BY use_id
) as d Order By mes_date desc另外，看看你是否可以避免在每个选择中分组(你会觉得性能不佳)
问候
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/47825981
复制相关文章
相似问题
腾讯云开发者
