如何联接有多个关系表并按类型获取结果 [英] How to join has many relation table and fetch result by type

问题描述

我有一些表要尝试加入并获取列表的结果

I have a few tables which I am trying to join and fetch the results for a list

Interviews Table
+--------------+-----------+
| interview_id | Candidate |
+--------------+-----------+
|            1 | Ram       |
|            2 | Rahim     |
|            3 | Joseph    |
+--------------+-----------+

Participant Ratings Table
+--------------+-----------+-------+
| interview_id | Rater Type|Rating |
+--------------+-----------+-------+
|            1 | Candidate |  4    |
|            2 | Candidate |  4    |
|            1 | Recruiter |  5    |
+--------------+-----------+-------+


System Ratings Table

+--------------+------------+-------+
| interview_id | Rating Type|Rating |
+--------------+------------+-------+
|            1 | Quality    |  4    |
|            1 | Depth      |  4    |
|            1 | Accuracy   |  5    |
|            2 | Quality    |  4    |
|            2 | Depth      |  3    |
|            2 | Accuracy   |  5    |
|            3 | Quality    |  4    |
|            3 | Depth      |  5    |
|            3 | Accuracy   |  5    |
+--------------+------------+-------+

我需要按以下方式获取每次采访的平均评分结果.

I need to fetch the result of average ratings for each interview given in the following manner.

+--------------+--------------+-----------------+-----------------+
| interview_id | System Rating|Recruiter Rating |Candidate Rating |
+--------------+--------------+-----------------+-----------------+
|            1 | 4.3          |  5              |   4             |
|            2 | 4.0          |  0              |   4             |
|            3 | 4.6          |  0              |   0             |
+--------------+--------------+-----------------+-----------------+

每个面试可以有1个候选人评级和1个招聘者评级，但这是可选的.如果给定记录，则会在参与者评分中创建带有评分和类型的记录.

Each interview can will have one 1 candidate rating and 1 recruiter rating but that is optional. If given a record is created in participant rating with rating and type.

需要获取所有类型的系统评级的平均值，并获得一个值作为系统评级，如果参与者提供的评级，则如果参与者中的任何一个或两个参与者均未提供任何评级，则显示否则显示为0.

Need to get the average of system ratings of all the types and get one value as system rating and if rating provided by participants then display else display as 0 if any or both the participants not provided any rating.

如果有误，请忽略这些值.

Please ignore the values, if there is a mistake.

我试图获取结果的SQL.

The SQL which I tried to get the result.

SELECT i.candidate, i.id AS interview_id, 
       AVG(sr.rating) AS system_rating, 
       AVG(CASE WHEN pr.rater_type = 'Candidate' THEN pr.rating END) AS candidate_rating, 
       AVG(CASE WHEN pr.rater_type = 'Recruiter' THEN pr.rating END) AS recruiter_rating 
  FROM system_ratings sr, participant_ratings pr, interviews i 
 WHERE sr.interview_id = i.id AND i.id = 2497 AND pr.interview_id = i.interview_id

问题在于，只要不存在参与者评分，就会因为加入而缺少结果.

The problem is whenever participant ratings are not present then results are missing as there is join.

推荐答案

使用LEFT JOIN确保关系表中没有任何数据，但我们仍然可以从主表中获得记录.

Use LEFT JOIN to make sure if relation tables do not have any data, still we can have records from the main table.

参考:了解MySQL左联接

问题:

• 错误的字段名:pr.interview_id = i.interview_id，应为pr.interview_id = i.id，因为我们在interviews表中没有任何visit_id字段，因此应为id字段-根据您的查询.
where子句中的
• pr.interview_id = i.id :如果participant_rating表没有给定采访的任何记录，这将导致从结果集中删除该采访.将LEFT JOIN用于participant_rating表.
where子句中的
• sr.interview_id = i.id :如果system_rating表没有给定采访的任何记录，这将导致从结果集中删除该采访.也将LEFT JOIN用于system_rating表.
• Usage of AVG有效，但不适用于SUM, COUNT ..之类的其他聚合函数，因为如果我们具有一对多关系，那么join将使同一行有多个记录.

• Wrong field name: pr.interview_id = i.interview_id, it should be pr.interview_id = i.id as we don't have any interview_id field in interviews table, it would be id field - based on your query.
• pr.interview_id = i.id in where clause: If participant_rating table does not have any records for a given interview, this will cause the removal of that interview from the result set. Use LEFT JOIN for participant_rating table.
• sr.interview_id = i.id in where clause: If system_rating table does not have any records for a given interview, this will cause the removal of that interview from the result set. Use LEFT JOIN for system_rating table too.
• Usage of AVG works but won't work for other aggregates functions like SUM, COUNT.. because if we have one to many relationships then join will make there will be multiple records for the same row.

解决方案:

SELECT 
    i.id AS interview_id,
    i.candidate,  
    AVG(sr.rating) AS system_rating, 
    AVG(CASE WHEN pr.rater_type = 'Candidate' THEN pr.rating END) AS candidate_rating, 
    AVG(CASE WHEN pr.rater_type = 'Recruiter' THEN pr.rating END) AS recruiter_rating
FROM interviews i
LEFT JOIN system_rating sr ON sr.interview_id = i.id
LEFT JOIN participant_rating pr ON pr.interview_id = i.id
-- WHERE i.id IN (1, 2, 3) -- use whenever required
GROUP BY i.id

这篇关于如何联接有多个关系表并按类型获取结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！