gpt4 book ai didi

mysql - 如何使用 Mysql 按特定顺序从两个不同的表中选择消息?

转载 作者:行者123 更新时间:2023-11-29 06:13:12 25 4
gpt4 key购买 nike

一共有五个数据表:

theUser (*id*, name)
theGroup (*id*, name)
Membership (*group_id*, *user_id*)
gpMsge (*id*, sender, receiver, content, time) # message within group
idMsge (*id*, sender, receiver, content, time) # message between user and user

theUsertheGroupMembership 之间的关系已经从这个question 得到了解释。 .现在我想提取对话(gpMsgeidMsge)并显示相应发件人的姓名(theUsertheGroup) 按照该对话中新闻集消息的顺序。事实上,业务逻辑与 Whatsapp 完全相同。在第一部分中,发件人姓名(如果对话是 p2p 则为个人用户名,如果是组内则为组名)按该对话中最新消息的时间顺序显示。在每个发件人姓名下方,该对话中应该有五个最新消息。第二部分显示所有没有对话的组(此部分无需指定顺序)。

我现在的困难是对话的顺序,因为 gpMsgeidMsge 是孤立的。如何对每个表中的 time 值进行排序,并在表中获取 theUsertheGroup 的名称?提前致谢!

最佳答案

你可以使用“UNION”来比较两个不同表之间的时间

此查询将列出两个表中的最后 5 个对话。

SELECT id,sender,receiver,content,time
FROM gpMsge
UNION
SELECT id,sender,receiver,content,time
FROM idMsge
ORDER BY time DESC
LIMIT 5

更新

完整的查询将是这样的::

 SELECT US.name AS sender,GR.name AS receiver,content,time 
FROM gpMsge AS GM
LEFT JOIN theUser AS US ON GM.sender=US.id
LEFT JOIN theGroup AS GR ON GM.receiver=GR.id
UNION
SELECT US.name AS sender,UR.name AS receiver,content,time
FROM idMsge AS UM
LEFT JOIN theUser AS US ON UM.sender=US.id
LEFT JOIN theUser AS UR ON UM.receiver=UR.id
ORDER BY time DESC
LIMIT 5;

see demo

如果您想选择一个特定的发件人或收件人,您必须添加一个 WHERE 子句:

此查询将选择发送给 ID 号为 2 的接收者的所有对话:发送给该用户的直接消息或发送给以该用户为成员的组的消息。

对于你的最后一个问题,你可以添加一个额外的字段来检查消息是从组内还是组外的用户发送的,这可以是一些文本或一个整数......

SELECT  US.name AS sender,GR.name AS receiver,content,
"This is a message in a group" AS sent_from,time
FROM gpMsge AS GM
LEFT JOIN theUser AS US ON GM.sender=US.id
LEFT JOIN theGroup AS GR ON GM.receiver=GR.id
WHERE receiver IN (
SELECT group_id FROM Membership WHERE user_id=2
)

UNION

SELECT US.name AS sender,UR.name AS receiver,content,
"This is a message from a user" AS sent_from, time
FROM idMsge AS UM
LEFT JOIN theUser AS US ON UM.sender=US.id
LEFT JOIN theUser AS UR ON UM.receiver=UR.id
WHERE receiver=2

ORDER BY time DESC
LIMIT 5;

DEMO

获取所有具有特定用户作为成员且尚未收到消息的组:

SELECT id,name 
FROM Membership AS M
JOIN theGroup AS G ON M.group_id=G.id
WHERE user_id=2
AND group_id NOT IN (SELECT receiver FROM gpMsge);

SEE DEMO

关于mysql - 如何使用 Mysql 按特定顺序从两个不同的表中选择消息?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/37321270/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com