我有几张桌子,比如:
我希望此表仅选择以下值(删除重复项):
from x- to
46 0
46 18
46 50
46 39
46 11
46 12
46 56
我使用DISTINCT关键字,但返回的值如下:
46 0
46 18
46 50
46 39
46 11
11 46
46 12
46 56
请帮帮我
发布于 2013-12-07 13:35:30
SELECT DISTINCT a.*
FROM TableName a
INNER JOIN TableName b
ON a.from = b.to
AND a.to = b.from
WHERE a.from < b.from发布于 2013-12-08 10:10:33
这是我的解决方案,它似乎不太理想,但它有效:
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;在这个查询中,我只是通过子查询在这些对话中搜索不必要的行:
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`)并从主表中“减去”这一结果。
https://stackoverflow.com/questions/20441947
复制相似问题