英文:
query for select chat and last message
问题
以下是翻译好的部分：
我在数据库中有两个表。一个表用于聊天，另一个表用于消息。每个聊天都有一个ID，每条消息也都有它的聊天ID。我使用了一个联接查询来选择特定用户的所有聊天和最后一条消息，但我无法为消息表设置限制。
这是我的查询：
SELECT id, name, host, client, closed, chats.attach, response, message, messages.time
FROM chats
INNER JOIN messages ON chat_id = id
WHERE chats.client = ? OR chats.host = ?
ORDER BY messages.time DESC
我希望有一个查询，返回用户的聊天记录和最后一条消息。
英文:
I have two tables in the database. One of tables is for chats, and another one is for messages. Every chat has an id and also every message has its chat id. I used a join query to select all chats of specific user and last message, but I can't set limit for messages table.
this is my query:
SELECT id, name, host, client, closed, chats.attach, response, message, messages.time
FROM chats
INNER JOIN messages ON chat_id = id
WHERE chats.client = ? OR chats.host = ?
ORDER BY messages.time DESC
I want a query that returns a user chats with the last message.
答案1
得分: 1
以下是已翻译的内容：
从以下查询中，查询将检索消息和聊天记录。使用子查询返回每个聊天的最新消息，并将子查询与聊天详细信息合并。可以通过使用作为过滤器的 WHERE 子句来查看结果。
SELECT chats.id, chats.name, chats.host, chats.client, chats.closed, chats.attach, messages.response, messages.message, messages.time
FROM chats
INNER JOIN (
SELECT chat_id, response, message, time
FROM messages
WHERE chat_id IN (
SELECT id
FROM chats
WHERE client = ? OR host = ?
)
ORDER BY time DESC
LIMIT 1
) AS messages ON chats.id = messages.chat_id
WHERE chats.client = ? OR chats.host = ?
英文:
From the below query the query will retrieve messages and chats. With subquery the most recent message for each chat is returned and also the subquery is combined with chat details. The result can be seen with where clause which is used as a filter.
SELECT chats.id, chats.name, chats.host, chats.client, chats.closed, chats.attach, messages.response, messages.message, messages.time
FROM chats
INNER JOIN (
SELECT chat_id, response, message, time
FROM messages
WHERE chat_id IN (
SELECT id
FROM chats
WHERE client = ? OR host = ?
)
ORDER BY time DESC
LIMIT 1
) AS messages ON chats.id = messages.chat_id
WHERE chats.client = ? OR chats.host = ?
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论