我有一个查询(从一个单独的评审表中)对一家餐厅的评分取平均值,我试图从中得到最大值。以下是一个基于加入餐厅评论获得平均评分的查询得到的结果:
1 Panera 4.0000
2 BurgerKing 4.5000
3 ChuckECheese 2.6667
4 Cafe Gratitude 2.0000
但是,我尝试只获取评论和评级最高的餐厅,我使用以下查询:
select BusinessName, MAX(AvgReview) as Rating from
(select BusinessName, avg(ReviewRating) as AvgReview
from Business
join Review
on Review.BusinessID = Business.BusinessID
group by Business.BusinessID) AvgRatingsTable;
但这会导致:
Panera 4.5000
它得到了正确的最高评级,但没有餐厅名称。我试过添加“where avgrating=max(avgrating)”,但它表示组函数的使用无效。
此外,当我尝试使用以下方法查找评论最少的餐厅时:
select AvgRatingsTable.BusinessName, MIN(AvgRatingsTable.AvgReview) as Rating from
(select BusinessName, avg(ReviewRating) as AvgReview
from Business
join Review
on Review.BusinessID = Business.BusinessID
group by Business.BusinessName) AvgRatingsTable;
我收到
BurgerKing 2.0000
因此,使用groupby businessname或businessid并没有真正的帮助;它只是抓住了第一个名字。
我做错什么了?我如何告诉它我想要与最大/最小平均值对应的名称?
编辑编辑编辑编辑
如果我这样做,在外部查询上有一个groupby
select AvgRatingsTable.BusinessName, MIN(AvgRatingsTable.AvgReview) as Rating from
(select BusinessName, avg(ReviewRating) as AvgReview
from Business
join Review
on Review.BusinessID = Business.BusinessID
group by Business.BusinessName) AvgRatingsTable
group by BusinessName;
它只是返回所有的表,而不是至少给我最低评级。如果我用groupby businessname,avgreview也一样。
暂无答案!
目前还没有任何答案,快来回答吧!