欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

MySQL仿淘宝页面评论评分统计SQL

程序员文章站 2022-05-07 10:24:12
...

假设淘宝有一张商品评论表,存储在MySQL中.表设计如下:

IDproductlist_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

 查询得到如下评论数据:

MySQL仿淘宝页面评论评分统计SQL
            
    
    博客分类: Oracle/MysqlSql/derby MySQLCaseWhenSUMAVG 
要求在商品页面需要计算出,

好评数(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

 得出的数据展现如下: 

MySQL仿淘宝页面评论评分统计SQL
            
    
    博客分类: Oracle/MysqlSql/derby MySQLCaseWhenSUMAVG 

  • MySQL仿淘宝页面评论评分统计SQL
            
    
    博客分类: Oracle/MysqlSql/derby MySQLCaseWhenSUMAVG 
  • 大小: 42.7 KB
  • MySQL仿淘宝页面评论评分统计SQL
            
    
    博客分类: Oracle/MysqlSql/derby MySQLCaseWhenSUMAVG 
  • 大小: 9.8 KB