MySQL 多对多 JOIN 返回重复项

Question

我有两张三张 table 。 users、jobs 和 users_jobs。一个用户可以有多个作业，一个作业可以有多个用户。

这是我的 users 表:

+----+------+--------+
| ID | Name | gender |
+----+------+--------+
|  1 | Bob  |   male |
|  2 | Sara | female |
+----+------+--------+

我的工作表:

+----+----------+
| id |  job_id  |
+----+----------+
|  1 | Engineer |
|  2 | Plumber  |
|  3 | Doctor   |
+----+----------+

users_jobs 表:

+---------+--------+
| user_id | job_id |
+---------+--------+
|       1 |      1 |
|       1 |      2 |
|       1 |      3 |
|       2 |      1 |
+---------+--------+

例如，我想选择所有男性并检查他们是否至少有一份工作，然后返回他们的信息。如果他们没有工作，则不要选择/返回该用户。这是我的查询:

SELECT * FROM users
INNER JOIN users_jobs
ON users_jobs.user_id = users.id
WHERE users.gender = 'male'

但它返回 Bob 的信息 3 次，因为他有 3 个工作。我不想要重复项，如何在不使用 DISTINCT 或 GROUP BY 的情况下去除重复项，因为这里的性能非常重要。

谢谢!

Answer 1

MySQL 允许你做一件有点奇怪的事情，你可以选择比 GROUP BY 子句和聚合函数中更多的列(这在大多数其他 SQL 引擎中是不允许的)。虽然这有时会产生意想不到的结果，但如果您不选择可能在结果查询中出现在多行中的数据，它就可以工作。

因此，对于您的问题 - 查询将为同一用户返回多行，因为其中一些人有很多工作(忙碌的生活，是吧？)。您通常无法在一行中获取他们的所有工作，因为每一行都是用户的数据 + 他们的工作 - 这就是我们 JOIN 的基础。但这并不完全正确 - 您可以使用 GROUP BY 和 GROUP_CONCAT() 将所有其他数据连接到一个字符串中。我一般不会推荐它，但如果它是你需要的......

SELECT u.Name, GROUP_CONCAT(j.job_id SEPARATOR ', ') as jobs
FROM users u
JOIN users_jobs uj
  ON u.ID = uj.user_id
JOIN jobs j 
  ON j.id = uj.job_id
GROUP BY u.ID

这会返回

Name    | jobs
--------+-------------------------------
Bob     | Engineer, Plumber, Doctor
Sara    | Engineer

如果您只想要男性，请在 where 子句中添加，

SELECT u.Name, GROUP_CONCAT(j.job_id SEPARATOR ', ') as jobs
FROM users u
JOIN users_jobs uj
  ON u.ID = uj.user_id
JOIN jobs j 
  ON j.id = uj.job_id`
WHERE u.gender = 'male'
GROUP BY u.ID

在 http://sqlfiddle.com/#!9/df0afe/2 查看现场 fiddle