mysql - 使用内部连接对多列进行分组

Question

我有以下数据库。

msg
....
 - uid (pk)
 - sender
 - msg

relation
---------
 - uid (fk)
 - reciever

一条消息可以发送给多个收件人，我想收到一个包含所有收件人和发件人的唯一列表。问题是发件人也可以是收件人，所以我得到了重复。

一个例子是:

SELECT  msg.sender,relation.reciever,msg.msg,msg.uid from msg inner join relation on msg.uid = relation.uid group by msg.sender;


+-------------+-------------+-------+-----+
| sender      | reciever    | msg   | uid |
+-------------+-------------+-------+-----+
| 123         | 321         | Test1 | 1   |
| 321         | 123         | Test2 | 2   |
+-------------+-------------+-------+-----+

问题是现在存在重复项。我想要其中之一，而不是两者都在发件人或收件人在任何一个领域都是独一无二的。为此，我在组中添加了 msg.reciever，方法如下:

SELECT  msg.sender,relation.number,msg.msg,msg.uid from msg inner join
 relation on msg.uid = relation.uid group by msg.sender, relations.reciever;

但结果是一样的。我将如何完成跨两列生成独特结果的任务？

为了澄清，最终结果应该是:

+-------------+-------------+-------+-----+
| sender      | reciever    | msg   | uid |
+-------------+-------------+-------+-----+
| 123         | 321         | Test1 | 1   |
+-------------+-------------+-------+-----+

或

+-------------+-------------+-------+-----+
| sender      | reciever    | msg   | uid |
+-------------+-------------+-------+-----+
| 321         | 123         | Test2 | 2   |
+-------------+-------------+-------+-----+

Answer 1

使用联合创建单个列，在该列上应用分组依据。

像这样:

select * from 
(
      SELECT  msg.sender as user, msg.msg,msg.uid from msg 
      inner join relation on msg.uid = relation.uid
   union
      SELECT  relation.reciever as user, msg.msg,msg.uid from msg 
      inner join relation on msg.uid = relation.uid
) Temp
group by user