MySQL仿淘宝页面评论评分统计SQL
程序员文章站
2022-05-07 10:24:12
...
假设淘宝有一张商品评论表,存储在MySQL中.表设计如下:
ID为productlist_comment主键,
UserID为用户的ID,
ProductID为商品ID
Content为评论的内容,
CreateTime为评论的时间.
Star为商品评分分数,取值为1-5;
ProductID和UserID已经分别建立索引.
SELECT ID, ProductID, UserID, Star, Content, Createtime FROM mysqldb.productlist_comment limit 10
查询得到如下评论数据:
要求在商品页面需要计算出,
好评数(Star>3),差评数(Star<3),中评数(Star=3),
以及评论总数,平均Star分数(如4.7), 好评百分比.
正对上面需求, 整理SQL如下:
select SUM(s.Star45) as Star45Num, SUM(s.Star3) as Star3Num, SUM(s.Star12) as Star12Num, SUM(s.Record) as TotalNum, AVG(s.starValue) AS AverageStar, SUM(s.Star45)/SUM(s.Record) AS Star45Percent from( select case when pc.star>3 then 1 else 0 end as Star45, case when pc.star=3 then 1 else 0 end as Star3, case when pc.star<3 then 1 else 0 end as Star12, pc.star as starValue, 1 AS Record from mysqldb.ProductList_comment pc where pc.ProductID=15792 ) as s
得出的数据展现如下:
推荐阅读