mysql - 如何正确排除相互引用的行？

Question

这个问题是基于不是很琐碎的问题 How to remove two duplicate column .我已经就那个问题提出了解决方案，但我认为有一些比我的更合适和优雅的解决方案。

有一些包含列 msg_id 的私有(private)消息表, from , to .
我们在里面有这些数据:

msg_id from   to
----------------
1      46     0
2      46     18
3      46     50
4      46     39
5      46     11  
6      11     46
7      46     12
8      46     56
9      46     11 

我们需要排除包含对话的行，其中有多条消息(例如包含 msg_id = 5 、 6 和 9 的行)，同时我们需要在输出中保留第一行这些行。一般输出应该是这样的(注意:没有 msg_id = 6 和 msg_id = 9 ):

msg_id from   to
----------------
1      46     0
2      46     18
3      46     50
4      46     39
5      46     11  
7      46     12
8      46     56 

我的解决方案是:

select distinct pm.`from`, pm.`to`
from `tsk_private_message` pm
left join
    (select distinct pm.`from`, pm.`to`
     from `tsk_private_message` pm
     inner join `tsk_private_message` pm2
     on (pm.`to` = pm2.`from`) and (pm2.`to` <> pm.`from`)) a
     using (`from`, `to`)
where a.`from` is null;

我只是通过子查询在这些对话中搜索不需要的行，然后从主表中“减去”结果。你怎么认为？有没有更优雅、更简单的解决方案？我真的不喜欢这种棘手的代码。

这里是 SQL Fiddle

Answer 1

SELECT mx.msg_id, pm.ffrom, pm.tto
FROM tsk_private_message pm
WHERE NOT EXISTS (
    SELECT * FROM tsk_private_message nx1
    WHERE nx1.ffrom = pm.ffrom AND nx1.tto = pm.tto
    AND nx1.msg_id < pm.msg_id
    )
AND NOT EXISTS (
    SELECT * FROM tsk_private_message nx2
    WHERE nx2.ffrom = pm.tto AND nx2.tto = pm.ffrom
    AND nx2.msg_id < pm.msg_id
    );

注意:我将 to 和 from 列重命名为 tto 和 ffrom，因为 to 和 from 都是 SQL 中的关键字，我不喜欢引用标识符。

额外:sqlfiddle (courtesy of Alexander Myshov)