SQLite:循环 SELECT 语句，替换 WHERE 子句

Question

我有以下两个表:

CREATE TABLE messages (
  id integer UNIQUE NOT NULL,
  message text,
  recipient integer NOT NULL,
  sender integer NOT NULL,
  sent_at text NOT NULL,
  FOREIGN KEY (recipient) REFERENCES users (id),
  FOREIGN KEY (sender) REFERENCES users (id)
);
CREATE TABLE users (
  id integer UNIQUE NOT NULL,
  username text NOT NULL,
);

我需要一个非常具体的查询，如下所示:

SELECT *
FROM messages
WHERE sender = 123 OR recipient = 123
ORDER BY id desc
LIMIT 1

我需要遍历消息表，使用每个用户，并将他放在 WHERE 语句中。

-- TABLE 'users':
-- 123 = id of user1
-- 456 = id of user2
-- 789 = id of user3

是否可以在 SQLite 中进行迭代？

目标是，为用户表中的每个用户获取最新的“对话”。对于每个用户，无论该最新消息是由他发送还是接收，都应该显示涉及他的最新消息。

Answer 1

您可以使用相关子查询来获取每个用户 ID 的值:

SELECT id,
       username,
       (SELECT MAX(id)
        FROM messages
        WHERE sender    = users.id
           OR recipient = users.id
       ) AS last_message_id
FROM users

这也可以使用 GROUP BY。
首先将两个表连接在一起，然后为每个用户创建一个组:

SELECT users.id,
       MAX(messages.id)
FROM users
JOIN messages ON users.id = messages.sender OR
                 users.id = messages.recipient
GROUP BY users.id