sql - UNION ALL 是获取此数据的正确方法吗？

Question

我有一个带有用户表和附属表的 Postgres 9.3 数据库。

用户表列

+----+-------+
|    |       |
+----+-------+
| id | email |
+----+-------+

附属栏目

+----+------------------+------------------+--------+
| id | referred_user_id | referrer_user_id | amount |
+----+------------------+------------------+--------+

我尝试了以下查询:

select
  users.email as referred_email,
  affiliates.amount
from affiliates
  JOIN users ON affiliates.referred_user_id = users.id

UNION ALL

select
  users.email as referrer_email,
  users.id
from affiliates
  JOIN users ON affiliates.referrer_user_id = users.id

它生成包含列的表格:

+----------------+--------+
| referred_email | amount |
+----------------+--------+

但我想要一对一的表格，例如:

+----------------+----------------+--------+
| referrer_email | referred_email | amount |
+----------------+----------------+--------+

我实际上是用 referrer_email 和 referred_email 替换每个 *_user_id，然后加上相应的 amount。我能做些什么不同的事情？我认为 UNION ALL 将所有列连接在一起。

Answer 1

I thought UNION ALL joined all the columns together.

不，UNION ALL 连接行。如果您想加入列，您需要使用JOIN。

可能看起来像这样:

select
  rer.email as referrer_email,
  red.email as referred_email,
  affiliates.amount
from affiliates
  JOIN users rer ON affiliates.referrer_user_id = rer.id
  JOIN users red ON affiliates.referred_user_id = red.id