sqlite - 与自身联合表并打印独特的对

Question

我正在尝试自行加入一张桌子并打印一对住在同一栋楼里的公民

SELECT DISTINCT a.citizen AS, b.citizen, a.home
FROM   table_citizen a,  table_citizen b
WHERE a.home = b.home 
GROUP BY a.citizen 
ORDER BY a.citizen

但是，这会同时返回

citizenA citizenB Hilton
citizenB citizenA Hilton

但我只想保留一对，因为这只是相同的结果。我做了类似的事情

FROM (SELECT DISTINCT  table_citizen a),  table_citizen b

但这似乎不起作用..

TIA

标签： sqlite

Answer 1

ON您应该使用包含条件的子句进行适当的连接，a.citizen < b.citizen以便您只获得每对一次：

SELECT a.citizen AS citizen1, b.citizen AS citizen2, a.home
FROM table_citizen a INNER JOIN table_citizen b
ON a.home = b.home AND a.citizen < b.citizen 
ORDER BY citizen1

没有必要使用GROUP BY.
查看简化的演示。