sql - 内部连接后自连接

Question

我正在查找哪些城市在不同的州具有相同的名称。城市名称和州名称位于单独的表(城市和州)中，并且可以通过单独的公共(public)列进行内部连接。

select c1.city, c1.state, c2.city, c2.state 
from cities 
inner join states on cities.commonid = states.commonid

内部加入后我需要 self 加入来执行如下功能

select c1.city, c1.state, c2.city, c2.state
from *joined table* c1 join
     *joined table* c2
     on c1.city = c2.city and c1.state <> c2.state

我想知道如何 self 连接一个表，该表是同一查询中另一个连接的结果输出将是这样的

+----------+-------+--------+--------+
| city1    | state1|city2   |state2  |
+----------+-------+--------+--------+
| x        | melb  | x      | syd    |
| y        | bris  | y      | ACT    |
+----------+-------+--------+--------+

Answer 1

我假设表 cities有一列像 state_id引用列 state_id在表中states (将名称更改为列的实际名称)。
首先为 cities 做一个自连接条件:

c1.city = c2.city AND c1.state_id < c2.state_id

<运算符确保每对城市只返回一次。
然后加入2份states ，因为它们中的每一个都将用于获取 2 个城市中每个城市的州名称:

SELECT c1.city city1, s1.state state1,
       c2.city city2, s2.state state2
FROM cities c1 
INNER JOIN cities c2 ON c1.city = c2.city AND c1.state_id < c2.state_id
INNER JOIN states s1 ON s1.state_id = c1.state_id
INNER JOIN states s2 ON s2.state_id = c2.state_id
ORDER BY city1