SQL查询需求：获取水果评论多数Verdict及占比（仅统计最新评论）

现有数据表 #

1. TblFruits表 #

2. TblReview表 #

查询需求 #

编写SQL查询，返回单行结果，包含以下字段：

• 水果名称
• 评论中的多数评价（Verdict）
• 该评价的占比（百分比）

特殊规则 #

若用户对同一水果发布多条评论，仅统计其最新的一条评论。

期望输出 #

现有初步代码 #

select 
    name as 'fruit name', 
    tbReview.verdict, tbReview.userID as 'reviewed by', tbReview.datePosted
from
    tbFruits
left join 
    tbReview on tbFruits.fruitId = tbReview.fruitID
group by 
    tbReview.userID, tbReview.datePosted, tbFruits.name, tbReview.verdict

完善后的SQL查询 #

WITH LatestReviews AS (
    -- 筛选每个用户对对应水果的最新评论
    SELECT 
        fruitID,
        userID,
        verdict,
        datePosted,
        ROW_NUMBER() OVER (PARTITION BY fruitID, userID ORDER BY datePosted DESC) AS rn
    FROM TblReview
)
SELECT 
    f.name AS 'fruit name',
    lr.verdict AS 'Verdict',
    -- 计算占比并取整
    ROUND((COUNT(lr.verdict) * 100.0) / (SELECT COUNT(*) FROM LatestReviews WHERE fruitID = f.id AND rn = 1), 0) AS '%'
FROM TblFruits f
JOIN LatestReviews lr ON f.id = lr.fruitID
WHERE lr.rn = 1 -- 仅保留最新评论
GROUP BY f.name, lr.verdict
-- 按评论数量降序排序，取第一条（即多数评价）
ORDER BY COUNT(lr.verdict) DESC
LIMIT 1;

代码说明 #

1. CTE LatestReviews：用ROW_NUMBER()窗口函数，按fruitID和userID分组，对每个用户的评论按发布时间倒序编号，最新评论的编号为1。
2. 主查询：
   • 关联水果表和筛选后的最新评论表，只保留编号为1的有效评论。
   • 按水果名称和评价内容分组，统计每种评价的出现次数。
   • 计算当前评价占总有效评论数的百分比并取整。
   • 按评价数量降序排序，取第一条结果即为多数评价及其占比。

内容的提问来源于stack exchange，提问作者Wohn Jick