我正在制作一个发送短信的小型网络应用程序。我必须向用户显示他们最后发送的短信(如Android、iOS中的短信应用程序…)
我的三张桌子:
--------- ----------- -----------
| users | | sms | | indexes |
--------- ----------- -----------
id id id
.. user_id user_id
receiver phone_n
created_at name
我已经发出了一个SQL请求,以获取按索引表的名称/phone_numbers分组的最后发送的SMS:
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请求有效。我正在通过接收者名称获取用户发送给它的最后一条短信。但我只收到了"索引"表中接收者所在的SMS。发送给未在此数据库中保存电话号码的接收者的所有SMS都不会显示。
我知道我的LEFT OUTER JOIN indexes
犯了一个错误。但是
那么,我如何才能获得接收器发送的最后一条短信,按lastrongent排序,电话号码保存在索引中还是不保存在索引?
谢谢。
将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