postgres在同一个表上多次迭代(postgres sql)

Question

我正在与SQL从几天作为初学者和卡在一个问题!

问题:

• 给定一个表user_email_table，其中列是(id, user_id, user_id_email)
• 如何让所有用户与每个扩展自己的用户相互关联

user_email_table

id | user_id | email_id
1  |    1    |    xyz
2  |    2    |    xyz2
3  |    3    |    xyz3
4  |    4    |    xyz4

期望输出值

id  | user_id_1 | user_id_2 | user_id_1_email | user_id_2_email |
-----------------------------
1   |      1    |     2     | xyz|xyz2|
1   |      1    |     3     |xyz|xyz3|
1   |      1    |     4     |xyz|xyz4|
1   |      2    |     1     |xyz2|xyz1|
1   |      2    |     3     |xyz2|xyz3|
1   |      2    |     4     |xyz2|xyz4|
1   |      3    |     1     |xyz3|xyz1|
1   |      3    |     2     |xyz3|xyz2|
1   |      3    |     4     |xyz3|xyz4|
1   |      4    |     1     |xy4|xyz1|
1   |      4    |     2     |xyz4|xyz2|
1   |      4    |     3     |xyz4|xyz3|

请忽略电子邮件字段的验证数据这只是为了提醒在输出表中提到这些列

哪些SQL查询可以得到这个表

Answer 1

你想要一个"交叉连接"不包括自匹配的行。你可以这样做:

select
1 as id,
a.user_id as user_id_1,
b.user_id as user_id_2,
a.email_id as user_id_1_email,  
b.email_id as user_id_2_email
from user_email_table a
cross join user_email_table b
where a.user_id <> b.user_id

编辑:

正如@IMSoP指出的那样，查询还可以使用带有连接谓词的公共连接，并且可以重新表述为:

select
1 as id,
a.user_id as user_id_1,
b.user_id as user_id_2,
a.email_id as user_id_1_email,  
b.email_id as user_id_2_email
from user_email_table a
join user_email_table b on a.user_id <> b.user_id