我知道以前曾问过类似的问题,但是没有一个人有相同的条件,他们的答案在这种情况下不起作用。
包含消息的表如下所示:
id | owner_id | recipient_id | content | created
1 | 1 | 2 | Hello | 2015-12-08 20:00
2 | 2 | 1 | Hey | 2015-12-08 20:10
3 | 3 | 1 | You there? | 2015-12-08 21:00
4 | 1 | 3 | Yes | 2015-12-08 21:15
5 | 4 | 1 | Hey buddy | 2015-12-08 22:00
假设我从每个对话中查询用户ID 1的最后一条消息,预期结果是:
id | owner_id | recipient_id | content | created
5 | 4 | 1 | Hey buddy | 2015-12-08 22:00
4 | 1 | 3 | Yes | 2015-12-08 21:15
2 | 2 | 1 | Hey | 2015-12-08 20:10
我使用JOIN和子查询尝试了许多组合,但没有一个给出预期的结果。
这是我尝试的查询之一,但无法正常工作。我相信还没有达到我的需要。
SELECT
IF ( owner_id = 1, recipient_id, owner_id ) AS Recipient,
(
SELECT
content
FROM
messages
WHERE
( owner_id = 1 AND recipient_id = Recipient )
OR
( owner_id = Recipient AND recipient_id = 1 )
ORDER BY
created DESC
LIMIT 1
)
FROM
messages
WHERE
owner_id = 1
OR
recipient_id = 1
GROUP BY
Recipient;
select t.*
from
t
join
(select user, max(created) m
from
(
(select id, recipient_id user, created
from t
where owner_id=1 )
union
(select id, owner_id user, created
from t
where recipient_id=1)
) t1
group by user) t2
on ((owner_id=1 and recipient_id=user) or
(owner_id=user and recipient_id=1)) and
(created = m)
order by created desc
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句