mysql - 如何根据条件获取别名字段的 SUM？

Question

总的来说，我对 MySQL 和 SQL 很陌生，我能够让部分查询按预期工作，但我没有信心，也不确定如何精确地将我的查询转换为实现我的目标的子查询。

有关数据库的上下文:我正在帮助为我的大学 session 设计一个应用程序，他们希望评委能够对演示者拥有的海报进行评分，并且演示者可以拥有多张海报。有五张 table 。

Judge
Poster
Presenter
Owns
Scores

有关查询的上下文:下面的查询输出演示者的姓名以及分数表中该特定记录值的总和

    SELECT
    presenter.presenter_name,
    (visual + clarity + thoroughness
     + breadth + depth + quality
     + discussion + understanding + overall)
    AS rec_tot
    FROM scores
    INNER JOIN judge ON judge.judge_id = scores.judge_ID
    INNER JOIN poster ON poster.poster_ID = scores.poster_ID
    INNER JOIN owns ON poster.poster_ID = owns.poster_ID
    INNER JOIN presenter ON presenter.presenter_ID = owns.presenter_ID
    ORDER BY presenter.academic_level, presenter.area_of_study,
    presenter.category; 

这将输出以下内容:

presenter_name | rec_tot
------------------------
nameOne        | 17
nameTwo        | 27
nameTwo        | 18
nameTwo        | 16
nameThree      | 9

我想接受此查询并按 Presenter_name 输出 SUM(rec_tot) 组，如下所示:

presenter_name | rec_tot
------------------------
nameOne        | 17
nameTwo        | 61
nameThree      | 9

以下是我解决该问题的尝试:

    SELECT a.*, SUM(rec_tot) AS total
    FROM (SELECT (visual + clarity + thoroughness + breadth
                 + depth + quality + discussion
                 + understanding + overall) AS rec_tot
    FROM scores
    INNER JOIN judge ON judge.judge_id = scores.judge_ID
    INNER JOIN poster ON poster.poster_ID = scores.poster_ID
    INNER JOIN owns ON poster.poster_ID = owns.poster_ID
    INNER JOIN presenter ON presenter.presenter_ID = 
    owns.presenter_ID
    ORDER BY presenter.academic_level, presenter.area_of_study, 
    presenter.category
    ) AS a;

但是，这只是给我所有条目的总和:

rec_tot | total
----------------
  27    |  87

但是每次我尝试使用 GROUP BY Presenter.presenter_name 引用在子查询中创建的表时，我都会收到“错误:1054。“GROUP BY”中的未知列“presenter.presenter_ID””。所以我认为父查询无法访问子查询，这导致我尝试在父查询上一起使用子查询的 FROM 子句，但是，总计输出为 435，即 5x87...所以这就是令人困惑。

此外，删除别名“a”时，我收到错误“错误:1284。每个派生表必须有自己的别名”

简而言之，我对别名、子查询的范围以及如何正确实现它们感到非常困惑。

Answer 1

你差不多明白了，你只需要添加组:

select a.presenter_name,sum(a.rec_tot)
from 
(SELECT
    presenter.presenter_name as presenter_name,
    (visual + clarity + thoroughness
     + breadth + depth + quality
     + discussion + understanding + overall)
    AS rec_tot
    FROM scores
    INNER JOIN judge ON judge.judge_id = scores.judge_ID
    INNER JOIN poster ON poster.poster_ID = scores.poster_ID
    INNER JOIN owns ON poster.poster_ID = owns.poster_ID
    INNER JOIN presenter ON presenter.presenter_ID = owns.presenter_ID
    ORDER BY presenter.academic_level, presenter.area_of_study,
    presenter.category) a
group by a.presenter_name