mysql - 有没有办法连接多个关系表并根据类型聚合多个列

Question

我有 2 个表采访和评级，数据如下

Interviews Table
+--------------+-----------+
| interview_id | Candidate |
+--------------+-----------+
|            1 | Ram       |
|            2 | Rahim     |
|            3 | Joseph    |
+--------------+-----------+
Ratings Table
+-----------+--------+-------+------------+--------------+
| rating_id | rating | skill | skill_type | interview_id |
+-----------+--------+-------+------------+--------------+
|         1 |      3 | Java  | Required   |            1 |
|         2 |      4 | C     | Optional   |            1 |
|         3 |      5 | MySQL | Required   |            1 |
|         4 |      3 | HTML  | Optional   |            1 |
|         5 |      5 | Java  | Required   |            2 |
|         6 |      2 | C     | Optional   |            2 |
|         7 |      4 | MySQL | Required   |            2 |
|         8 |      1 | HTML  | Optional   |            2 |
+-----------+--------+-------+------------+--------------+

我试图获取每种技能类型的候选人的平均评分格式

+-----------+-----------------+-----------------+
| Candidate | Required Rating | Optional Rating |
+-----------+-----------------+-----------------+

这条SQL

Select i.candidate, r.rating, r.skill, r.skill_type 
from interviews i, ratings r 
where r.interview_id = i.interview_id;

给我

+-----------+--------+-------+------------+
| candidate | rating | skill | skill_type |
+-----------+--------+-------+------------+
| Ram       |      3 | Java  | Required   |
| Ram       |      4 | C     | Optional   |
| Ram       |      5 | MySQL | Required   |
| Ram       |      3 | HTML  | Optional   |
| Rahim     |      5 | Java  | Required   |
| Rahim     |      2 | C     | Optional   |
| Rahim     |      4 | MySQL | Required   |
| Rahim     |      1 | HTML  | Optional   |
+-----------+--------+-------+------------+

我在这里根据每个候选人的技能类型获取聚合。

Answer 1

您可以使用条件聚合来获得所需的结果，根据 ratings 表中 skill_type 的值取平均值:

SELECT i.Candidate, 
       AVG(CASE WHEN r.skill_type = 'Required' THEN rating END) AS `Required Rating`,
       AVG(CASE WHEN r.skill_type = 'Optional' THEN rating END) AS `Optional Rating`
FROM interviews i
JOIN ratings r
ON r.interview_id = i.interview_id
GROUP BY i.Candidate

输出:

Candidate   Required Rating Optional Rating
Rahim       4.5             1.5
Ram         4               3.5

Demo on dbfiddle