时间戳

首页 > 解决方案 > 如何连接来自不同表的多行和单行

mysql - 如何连接来自不同表的多行和单行

问题描述

我正在尝试将用户、简历和 resume_experience 加入一行，但是

一个用户有多个resume_experience 并且只有一个resume

我如何加入他们并通过 job_position 和 job_category 进行比较？

或者这是不可能的，我必须为它编写另一个 sql 查询？

SELECT * FROM resume r
INNER JOIN job j ON j.job_category = r.resume_category
LEFT JOIN user u ON r.resume_user = u.user_id  
LEFT JOIN resume_experience re ON re.resume_experience_position = j.job_position 
WHERE j.job_id = 1 GROUP BY r.resume_user

以上是我的 sql 查询，但LEFT JOIN resume_experience不工作。

resume
+-----------+-------------+-----------------+
| resume_id | resume_user | resume_category |
+-----------+-------------+-----------------+
|         1 |           1 |               1 |
|         2 |           2 |               1 |
|         3 |           3 |               1 |
+-----------+-------------+-----------------+

job
+---------+--------------+--------------+--------------+
|  job_id | job_employer | job_position | job_category |
+---------+--------------+--------------+--------------+
|       1 |            1 |            1 |            1 |
|       2 |            2 |            1 |            1 |
|       3 |            3 |            2 |            1 |
+---------+--------------+--------------+--------------+

resume_experience
+-----------------------+----------------------------+------------------------+
|  resume_experience_id | resume_experience_position | resume_experience_user |
+-----------------------+----------------------------+------------------------+
|                     1 |                          1 |                      1 |     
|                     2 |                          2 |                      1 |  
+-----------------------+----------------------------+------------------------+

user
+---------+-----------+
| user_id | user_name |
+---------+-----------+
|       1 |     david |
|       2 |    kelvin |
|       3 |       bob |
+---------+-----------+

标签： mysqlsqlleft-join

解决方案

如果您希望每个用户的单行结果，您可以将 group_concat 用于具有多个值的列

SELECT resume.* , group_concat(re.resume_experience_position)
FROM resume r
INNER JOIN job j ON j.job_category = r.resume_category
LEFT JOIN user u ON r.resume_user = u.user_id  
LEFT JOIN resume_experience re ON re.resume_experience_position = j.job_position 
WHERE j.job_id = 1 
GROUP BY r.resume_user

左连接用于非加工值。对于减少行，您需要一个聚合函数和 group by ..

推荐阅读