gpt4 book ai didi

mysql - 构建 SQL 查询来连接三个表以获取消息传递信息

转载 作者:行者123 更新时间:2023-11-29 14:25:56 24 4
gpt4 key购买 nike

我有以下三个消息系统表:

`messaging_messagethread`
- id
- subject
- initiator_id # who creates the thread
- recipient_id

`messaging_message`
- id
- thread_id
- content
- timestamp
- sender_id

`messaging_messagestatus` # a status will be created for each recipient of a message
- id
- message_id
- recipient_id
- status

给定一个用户,我需要构建一个查询来获取以下信息:

  • 显示线程 ID(不同),
  • 该线程中最新消息的
  • 内容时间戳
  • 删除包含最新消息status='deleted'的所有话题。

这是我到目前为止所拥有的:

SELECT DISTINCT thread.id as thread_id, timestamp.timestamp 
FROM messaging_messagethread thread
INNER JOIN
(SELECT thread_id, MAX(timestamp) as timestamp
FROM messaging_message GROUP BY thread_id) timestamp
ON thread.id = timestamp.thread_id
WHERE initiator_id = 4 OR thread.recipient_id = 4 ORDER BY timestamp.timestamp DESC

这给了我按最近时间戳排序的不同线程 ID。 (我的三点中的第一点)。我将如何构建整个查询?

最佳答案

您可以使用相关子查询来获取特定线程的最新消息。试试这个:

SELECT 
a.id,
b.content,
b.timestamp
FROM
messaging_messagethread a
INNER JOIN
messaging_message b ON a.id = b.thread_id
WHERE
b.timestamp =
(
SELECT MAX(timestamp)
FROM messaging_message
WHERE thread_id = a.id
)
AND b.id NOT IN
(
SELECT message_id
FROM messaging_messagestatus
WHERE status = 'deleted'
)
AND 4 IN (a.initiator_id, a.recipient_id)
ORDER BY
b.timestamp DESC

如果我理解正确的话,我相信这就是你想要的。

关于mysql - 构建 SQL 查询来连接三个表以获取消息传递信息,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/10958245/

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