mysql - 使用 LEFT JOIN 在查询中添加列

Question

我有三个表。

tblcandidates

candidateid     |     candidatename
1               |     Abc
2               |     Def

判断

judgeid         |     judgename
1               |     Stack
2               |     Overflow

tblscores

scoreid         |     candidateid     |     judgeid     |     swimsuit
1               |     1               |     1           |     100
2               |     1               |     2           |     99
3               |     2               |     1           |     100
4               |     2               |     2           |     93

我正在使用此查询来获取每个候选人的平均值。

SELECT DISTINCT
    (c.candidateid) AS c,
    candidatename AS NAME,
    j1.swimsuit AS j1,
    j2.swimsuit AS j2,
    (
        j1.swimsuit + j2.swimsuit
    ) / 2 AS average
FROM
    tblscores,
    tblcandidates c
LEFT JOIN tblscores j1 ON c.candidateid = j1.candidateid
AND j1.judgeid = 1
LEFT JOIN tblscores j2 ON c.candidateid = j2.candidateid
AND j2.judgeid = 2
WHERE   tblscores.candidateid = c.candidateid;

输出

c      |      name       |      j1       |      j2       |     average
1      |      Abc        |      100      |      99       |     99.5
2      |      Def        |      100      |      93       |     96.5

我的问题是如果评委变成 3 人怎么办。我想根据评委的数量使查询动态化。我的查询仅限于 2 位评委。我还想在我的输出中显示评委分数，以证明他们有分数。

Answer 1

您在这里通过自己实现平均计算来重新发明轮子。相反，您可以使用 MySQL 的内置聚合 avg功能。如果你真的想要所有的分数，你可以使用 group_concat显示它们:

SELECT   c.candidateid AS id, 
         candidatename AS name,
         GROUP_CONCAT(swimsuit) AS all_scores,
         AVG(swimsuit) AS average_score
FROM     tblcandidates c 
JOIN     tblscores s ON c.candidateid = s.candidateid
GROUP BY c.candidateid, candidatename