前端之家

  1. 首页
  2. 问答

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

• 问答

我的应用中有一个评分系统。现在，我试图从评级中获得所有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

我遇到的问题是我无法在ratingresult中使用subselect。

  

查询1错误：不支持参考“ ratingresult”（参考   组功能）

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

可以在这里使用一些帮助，谢谢！

-编辑

评级结果说明了评级。因此，如果AVG​​等级为4，则在rating_results表中，我可以找到该等级的含义：

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

xyhjojoy 回答：在选择子查询中使用AVG结果

代替选择列值，您可以对联接中的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

避免子查询

group-bymysqlsqlsubquery
本文链接：https://www.f2er.com/3128501.html

大家都在问