SQL如何按产品ID分组统计各星级对应的评价ID数量

原有语句错误原因 #

你写的SQL不符合预期核心是两个问题：

• CASE WHEN中设置了ELSE 0：count()会统计所有非NULL值，不符合星级条件的记录返回了0而非NULL，会被计入统计，导致结果错误
• 多余的DISTINCT：Review表的id是主键天然唯一，不需要额外去重，无意义且增加计算开销

正确SQL写法 #

写法1：COUNT统计（匹配你的原始写法逻辑调整）

SELECT 
    prod_id,
    COUNT(CASE WHEN star = 5 THEN id END) AS five_star,
    COUNT(CASE WHEN star = 4 THEN id END) AS four_star,
    COUNT(CASE WHEN star = 3 THEN id END) AS three_star,
    COUNT(CASE WHEN star = 2 THEN id END) AS two_star,
    COUNT(CASE WHEN star = 1 THEN id END) AS one_star
FROM Review
GROUP BY prod_id
ORDER BY prod_id;

CASE WHEN不写ELSE时，不符合条件的记录默认返回NULL，count()会自动忽略NULL值，就能得到正确的统计数。

写法2：SUM求和（更易理解的写法）

SELECT 
    prod_id,
    SUM(CASE WHEN star = 5 THEN 1 ELSE 0 END) AS five_star,
    SUM(CASE WHEN star = 4 THEN 1 ELSE 0 END) AS four_star,
    SUM(CASE WHEN star = 3 THEN 1 ELSE 0 END) AS three_star,
    SUM(CASE WHEN star = 2 THEN 1 ELSE 0 END) AS two_star,
    SUM(CASE WHEN star = 1 THEN 1 ELSE 0 END) AS one_star
FROM Review
GROUP BY prod_id
ORDER BY prod_id;

扩展：关联产品表返回产品名称

如果需要同时展示产品名称，关联Product表即可，无评价的产品也会展示，各星级统计为0：

SELECT 
    p.id AS prod_id,
    p.name AS prod_name,
    COUNT(CASE WHEN r.star = 5 THEN r.id END) AS five_star,
    COUNT(CASE WHEN r.star = 4 THEN r.id END) AS four_star,
    COUNT(CASE WHEN r.star = 3 THEN r.id END) AS three_star,
    COUNT(CASE WHEN r.star = 2 THEN r.id END) AS two_star,
    COUNT(CASE WHEN r.star = 1 THEN r.id END) AS one_star
FROM Product p
LEFT JOIN Review r ON p.id = r.prod_id
GROUP BY p.id, p.name
ORDER BY p.id;

内容的提问来源于stack exchange，提问作者Hung Phung