SQL JOIN 同一张表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41794026/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL JOIN with the same table
提问by L. Flor
I am trying to query in SQL and I can not resolve it.
I have a table tCliente:
我正在尝试使用 SQL 进行查询，但无法解决。我有一张桌子tCliente：
What I want to do is a JOIN with the same table to find each pair of clients that lives in the same city.
我想要做的是用同一张表 JOIN 找到住在同一个城市的每一对客户。
I try to do this:
我尝试这样做：
SELECT DISTINCT c.codiClien, c.nombreClien, c1.codiClien, c1.nombreClien, c.ciudadClien
FROM tCliente c
INNER JOIN tCliente c1 ON c.ciudadClien = c1.ciudadClien
and get this:
得到这个：
But I should get this:
但我应该得到这个：
I know I have to filter data, but I have tried many things and I can not find the solution.
Also, I tried to use GROUP BYbut it is not possible. I wanted to group by pair, that is, something like this:
我知道我必须过滤数据，但是我尝试了很多事情，但找不到解决方案。另外，我尝试使用GROUP BY但这是不可能的。我想按对分组，也就是说，像这样：
...
GROUP BY c.codiClien, c1.codiClien
But in doing so I get errors in the query. Could someone please help me? Thanks
但这样做时，我在查询中出现错误。有人可以帮我吗？谢谢
Note:When using ONin the INNER JOIN, I would like to know if it is "possible" to do that or should not do it, because the usual thing is to do tb1.id = tb2.id
注意：在ONin 中使用时INNER JOIN，我想知道是否“可能”这样做或不应该这样做，因为通常的事情是做tb1.id = tb2.id
回答by McNets
You must exclude itself on the inner join.
您必须在内部联接中排除自身。
SELECT c.codiClien, c.nombreClien, c1.codiClien, c1.nombreClien, c.ciudadClien
FROM tCliente c
INNER JOIN tCliente c1
ON c.ciudadClien = c1.ciudadClien
AND c.codiClien < c1.codiClien;
