mysql - 加入 2 个不同的表

Question

我有 2 个表，follow 和 following

关注

+---------+----------------+
| user_id | follow_user_id |
+---------+----------------+
|       1 |              2 |
|       2 |              3 |
|       3 |              4 |
|     100 |             10 |
+---------+----------------+

关注

+---------+-------------------+
| user_id | following_user_id |
+---------+-------------------+
|       1 |                 2 |
|       3 |                 4 |
|       4 |                 6 |
|     200 |               500 |
+---------+-------------------+

我想连接 2 个表而不重复。

这是我想要的结果。

+---------+----------------+-----------+
| user_id | target_user_id |  category |
+---------+----------------+-----------+
|       1 |              2 | follow    |
|       2 |              3 | follow    |
|       3 |              4 | follow    |
|       4 |              6 | following |
|     100 |             10 | follow    |
|     200 |            500 | following |
+---------+----------------+-----------+

条件 1 - 删除重复行

条件 2 - 必须为每个表的名称添加类别列

条件 3 - 如果类别重复，则可以是 follow 或 following。没关系。

条件 4 - follow_user_id 作为 target_user_id 和 following_user_id 作为 target_user_id

在这种情况下，我必须使用 join 吗？还是工会？

任何建议，非常感谢。

谢谢!

Answer 1

只需使用union和group by，SQL如下:

select 
    user_id,target_user_id,min(tag) as category
from 
(
    select user_id,follow_user_id as target_user_id, 'follow' as tag from follow
    union
    select user_id,following_user_id as target_user_id, 'following' as tag from following
) tmp 
group by 
    user_id,target_user_id 
order by 
    user_id,target_user_id;

+---------+----------------+-----------+
| user_id | target_user_id | category  |
+---------+----------------+-----------+
|       1 |              2 | follow    |
|       2 |              3 | follow    |
|       3 |              4 | follow    |
|       4 |              6 | following |
|     100 |             10 | follow    |
|     200 |            500 | following |
+---------+----------------+-----------+
6 rows in set (0.00 sec)