sql - 如何在同一列中加入不同的 FK 值？

Question

尽可能简单：

我有的...

Table users:
user_id | user_name 
   1    |   Jack  
   2    |   Chuck   


Table jobs:
job_id |   job_name 
   1   |    Farmer
   2   |    Ninja
   3   |    Teacher   

我希望我能用一个 JOIN 来做...

user_id | user_name | user_role
   1    |   Jack    |   1, 2
   2    |   Chuck   |   2, 3

我真正想要的...

user_id | user_name |     user_role
   1    |   Jack    |   Farmer, Ninja
   2    |   Chuck   |   Ninja, Teacher

那么，我该怎么做呢？顺便说一句，我使用的是 knexjs，但是通过 SQL 解释，我可以自己完成剩下的工作。

标签： sqlpostgresqlknex.js

Answer 1

首先，您缺少一个将用户与其工作相关联的联结表：

user_jobs

user_id | job_id
1       | 1
1       | 2
2       | 2
2       | 3

定义此表后，我们只需要一系列连接：

SELECT
    u.user_id,
    u.user_name,
    STRING_AGG(j.job_name, ',' ORDER BY j.job_id) user_role
FROM users u
INNER JOIN user_jobs uj
    ON u.user_id = uj.user_id
INNER JOIN jobs j
    ON uj.job_id = j.job_id
GROUP BY
    u.user_id,
    u.user_name;