现在有两张表,一个是商家表,存储商家信息,如(shopId,shopName),另一个是评价表,存储对商家的评价信息,如(id,shopId,score),score分数分别为1,2,3,4,5。3分以上为好评,以下为差评, 评价表可以有多条对同一商家的评论的数据,分数也可以不一样,现在要查询商家列表,并且要算数每个商家的好评率,根据商家的好评率,从高到低排序,怎么办啊?请各位指点指点,谢谢了。
1条回答
- shoes_2003 2010-09-27 10:16关注
试试这个
[code="sql"]
select a.shopId, a.shopName, b.goodScore/c.totalScore
from 商家信息 a
inner join (select shopId, count(1) goodNumber from 评价信息 where score >= 3 group by shopId) b on a.shopId = b.shopId
inner join (select shopId, count(1) totalNumber from 评价信息 group by shopId) c on a.shopId = c.shopId
order by b.goodScore/c.totalScore desc
[/code]本回答被题主选为最佳回答 , 对您是否有帮助呢?解决 无用评论 打赏 举报