基于MySQL 8.0.29查询多次评分同一影片且第二次评分更高的评论者

嘿，我来帮你搞定这个SQL查询问题～你要找的是对同一影片评分超过1次，而且第二次评分星级比第一次高的评论者对吧？你现在的SQL已经筛选出了有多次评分的用户，但还没加上“第二次评分更高”这个关键判断，我给你用MySQL 8.0的窗口函数来实现这个需求：

思路解析 #

因为MySQL 8.0支持窗口函数，我们可以用ROW_NUMBER()给每个评论者的每部电影评分按日期排个序，这样就能明确区分出第一次和第二次评分，之后只要比较两者的星级大小就行。

最终SQL代码 #

SELECT DISTINCT r.name AS Reviewer, m.title AS Title
FROM (
    -- 给每个评论者的每部电影评分按日期排序，生成评分序号
    SELECT 
        reviewerID, 
        movieID, 
        stars, 
        ratingDate,
        ROW_NUMBER() OVER (PARTITION BY reviewerID, movieID ORDER BY ratingDate) AS rating_seq
    FROM rating
    WHERE ratingDate IS NOT NULL -- 排除没有日期的评分，没法确定先后顺序
) AS first_rating
-- 关联评论者和电影表，获取名称与影片标题信息
JOIN reviewer r ON r.reviewerID = first_rating.reviewerID
JOIN movie m ON m.movieID = first_rating.movieID
-- 自连接找到同一用户同一电影的第二次评分记录
JOIN (
    SELECT 
        reviewerID, 
        movieID, 
        stars,
        ROW_NUMBER() OVER (PARTITION BY reviewerID, movieID ORDER BY ratingDate) AS rating_seq
    FROM rating
    WHERE ratingDate IS NOT NULL
) AS second_rating 
    ON first_rating.reviewerID = second_rating.reviewerID 
    AND first_rating.movieID = second_rating.movieID 
    AND first_rating.rating_seq = 1  -- 匹配第一次评分
    AND second_rating.rating_seq = 2 -- 匹配第二次评分
-- 核心判断条件：第二次评分星级高于第一次
WHERE second_rating.stars > first_rating.stars;

结果验证 #

用你提供的示例数据运行这条SQL，会得到你期望的结果：

Sarah Martinez（reviewerID 201）对《Gone with the Wind》第一次评了2星（2011-01-22），第二次评了4星（2011-01-27），完全符合第二次评分更高的要求。

额外说明 #

• 为什么要过滤ratingDate IS NULL？因为没有日期的话我们没法确定评分的先后顺序，这类记录不能用来判断第一次和第二次评分的关系。
• 加DISTINCT是为了避免重复返回同一评论者+影片的组合——我们只需要确认存在这种情况，不需要重复输出。
• 如果想看到两次评分的具体星级和日期，可以把SELECT语句改成：

SELECT 
    r.name AS Reviewer, 
    m.title AS Title,
    first_rating.stars AS First_Stars,
    first_rating.ratingDate AS First_Date,
    second_rating.stars AS Second_Stars,
    second_rating.ratingDate AS Second_Date

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