php - 在 SMS 应用程序中获取接收者的最后一条消息 - SQL 请求

Question

我正在制作一个用于发送短信的小型网络应用程序。我必须向用户显示他们上次发送的短信(例如 Android、iOS 中的短信应用...)

我的三张表:

---------   -----------   -----------
| users |   |  sms    |   | indexes |
---------   -----------   -----------
   id           id            id
   ..         user_id       user_id
              receiver      phone_n
             created_at      name

我已经发出了一个 SQL 请求，以获取按索引表的名称/电话号码分组的最后发送的短信:

SELECT S.receiver, S.message, I.name, temp.last_sent, I.id
FROM sms S
INNER JOIN (
    SELECT receiver, user_id, MAX(created_at) AS last_sent
        FROM sms
        WHERE user_id = ?
        GROUP BY receiver, user_id
    ) temp
ON S.receiver = temp.receiver
AND S.user_id = temp.user_id
AND S.created_at = temp.last_sent
LEFT OUTER JOIN indexes I ON S.receiver = I.phone_number WHERE I.user_id = ? 
ORDER BY temp.last_sent DESC

此 SQL 请求有效。我通过接收者名称获取用户发送给它的最后一条短信。但我只收到接收者出现在索引表中的短信。所有发送给未在此数据库中保存电话号码的接收者的短信均不会显示。

我知道我的LEFT OUTER JOIN 索引 有误。但是

那么我怎样才能得到接收者发送的最后一条短信，按 last_sent 排序，电话号码是否保存在索引中？

谢谢。

Answer 1

将 WHERE 子句移动到 LEFT OUTER JOIN 的 ON 子句中

SELECT S.receiver, S.message, I.name, temp.last_sent, I.id
FROM sms S
INNER JOIN (
    SELECT receiver, user_id, MAX(created_at) AS last_sent
        FROM sms
        WHERE user_id = ?
        GROUP BY receiver, user_id
    ) temp
ON S.receiver = temp.receiver
AND S.user_id = temp.user_id
AND S.created_at = temp.last_sent
LEFT OUTER JOIN indexes I ON S.receiver = I.phone_number 
AND I.user_id = ? 
ORDER BY temp.last_sent DESC