mysql - 在选择子查询中使用 AVG 结果

Question

我的应用程序中有一个评级系统。现在我正在尝试从评级中获取所有 AVG 结果。每个 AVG 结果都有一个结果(以文本形式)，我需要从 rating_results 表中获取它。

看起来像这样:

select round(avg(rating_results.rating)) as ratingresult, count(*) as votes, score.question_nl,
(select result_nl from rating_results where rating_results.rating = ratingresult and rating_results.score_id = score.id) from score 
inner join score_categories on score_categories.id = score.category_id
inner join rating ON score.id = rating.score_id
inner join rating_results on rating.rating_result_id = rating_results.id
inner join dog on dog.id = rating.ratable_id 
where dog.breed_id = 201
group by score.question_nl

我遇到的问题是我不能在 subselect 中使用 ratingresult。

Query 1 ERROR: Reference 'ratingresult' not supported (reference to group function)

我已经尝试了很多，但找不到其他方法。

这里需要一些帮助，谢谢!

--编辑

评分结果说明评分。因此，如果 AVG 评分为 4，那么在 rating_results 表中我可以找到该评分的含义:

Answer 1

代替选择列值，您可以在连接中使用 avg 的子查询

  select t.ratingresult
  , count(*) as votes
  , score.question_nl
  , rating_results.result_nl 
FROM score
inner join score_categories on score_categories.id = score.category_id
inner join rating ON score.id = rating.score_id
inner join rating_results on rating.rating_result_id = rating_results.id
inner join dog on dog.id = rating.ratable_id 
INNER JOIN  (
  select round(avg(rating_results.rating)) as ratingresult
    , score.question_nl
  from score 
  inner join rating ON score.id = rating.score_id
  inner join rating_results on rating.rating_result_id = rating_results.id
  group by score.question_nl
) t ON t.ratingresult = rating_results.rating   
        AND rating_results.score_id = score.id
          AND score.question_nl = t.question_nl
where dog.breed_id = 201
group by score.question_nl, t.ratingresult

避免子查询