与SQL搏斗

试试这个

SELECT distinct recipient_id
FROM Message
WHERE sender_id = @id

UNION

SELECT distinct sender_id
FROM Message
WHERE recipient_id = @id

SELECT x.friend,
       MAX(x.created)
  FROM (SELECT t.recipient_id 'friend',
               t.created
          FROM MESSAGE t
         WHERE t.sender_id = @id 
        UNION
        SELECT r.sender_id 'friend',
               r.created
          FROM MESSAGE r
         WHERE r.recipient_id = @id) x
GROUP BY x.friend

不,这很容易做到。

SELECT DISTINCT IF(sender_id=4, recipient_id, sender_id) AS partner_id
FROM message
WHERE sender_id=4 OR recipient_id=4

这是我的:)

select id, max(created) as lastMessage
(
    select sender_id as id, created
    from Message
    union
    select recipient_id as id, created
    from Message
) as MergedMessage
where id = ?id
group by id
order by max(created) desc

就是这样:

• 无重复项
• 按创建者desc排序

它可能朝着以下方向发展

select 
  sender_id,
  recipient_id
from
  message
where
 (sender_id    = n or 
  recipient_id = n   )
order by
  created desc

我认为这将最符合您的要求:

SELECT *
FROM user
WHERE 
  user_id in 
    (select recipient_id FROM message m where m.sender_id = X)
  OR user_id in
    (select sender_id FROM message m where m.recipient_id = X)
order by
  created DESC

没有重复,没有联合,也没有SQL方言特定的东西。