Mysql查询合并两列并获得两者的唯一值

Question

我只是不知道如何执行以下操作。

我有什么

... 是一个 mysql 表“邮件”，例如:

id | sender | recipient | date                 | content | read
---------------------------------------------------------------
1  | 3      | 2         | 2016-07-29 09:04:21  | hello   | 1
2  | 2      | 3         | 2016-07-29 09:14:21  | hello   | 1
3  | 1      | 2         | 2016-07-29 09:24:21  | hello   | 1
4  | 1      | 3         | 2016-07-29 09:34:21  | hello   | 0
5  | 1      | 1         | 2016-07-29 09:44:21  | hello   | 1
6  | 3      | 1         | 2016-07-29 09:54:21  | hello   | 0
7  | 1      | 1         | 2016-07-29 09:56:21  | hello   | 1
8  | 1      | 3         | 2016-07-29 09:58:21  | hello   | 0
9  | 2      | 2         | 2016-07-29 09:59:21  | hello   | 0

我需要什么

...就是获取一个人最近的聊天伙伴概况，比如id为1的用户(用户也可以给自己发送消息)like

date                | partner
2016-07-29 09:58:21 | 3
2016-07-29 09:56:21 | 1
2016-07-29 09:24:21 | 2

无论用户是收件人还是发件人，无论发送或接收了多少消息。 (我在示例输出中包含了日期列以阐明我需要它们按日期 DESC 排序，它不需要包含在最终结果中)

我尝试过的

SELECT DISTINCT sender,recipient
FROM mails
WHERE recipient=1 OR sender=1
ORDER BY date DESC

但当然它不会合并两列。输出是

sender | recipient
1      | 3
1      | 1
3      | 1
1      | 2

如果我添加 GROUP BY sender，它不会显示用户 1 只是收件人的邮件。

我也尝试了以下

SELECT
    max(date) as d,
    CONCAT(greatest(sender,recipient),"_",least(sender,recipient)) AS p
FROM mails
WHERE recipient=1 OR sender=1
GROUP BY p ORDER BY d DESC

例如，给出

d                   | p
2016-07-29 09:58:21 | 3_1
2016-07-29 09:56:21 | 1_1
2016-07-29 09:24:21 | 2_1

貌似厉害，我只能取显示的第一个id ...但是当我寻找用户 id 2 时，它给出了

d                   | p
2016-07-29 09:59:21 | 2_2
2016-07-29 09:24:21 | 2_1
2016-07-29 09:14:21 | 3_2

...所以不清楚第一个id还是第二个id是合作伙伴的id。

结果

我有点绝望。任何帮助都会很棒。

Answer 1

我想你想要这样的聚合:

select max(date) as date,
       (case when m.recipient = 1 then m.sender else m.recipient end) as partner
from mails
where 1 in (m.recipient, m.sender)
group by (case when m.recipient = 1 then m.sender else m.recipient end)
order by max(date);