MySQL如何统计每个ID用户首日观看的不同电影数量并修正查询错误

你原查询中WHERE条件的子查询SELECT MIN(day) FROM moviewatching返回的是全表所有记录的最早日期，不是每个用户ID对应的专属最早日期，所以只会返回全表最早当天的用户记录。同时你原查询GROUP BY包含了movies字段，会导致每条电影单独分为一组，COUNT(movies)结果永远为1，无法统计单用户单日的总观影数。

方案1：关联子查询（兼容所有MySQL版本） #

通过关联子查询匹配每个ID自己的最早观影日：

-- 仅查询每个用户首次观影日的所有观影记录
SELECT ID, name, movies, day
FROM moviewatching t1
WHERE day = (
    SELECT MIN(day) 
    FROM moviewatching t2 
    WHERE t2.ID = t1.ID
)
ORDER BY ID;

如果需要统计每个用户首次观影日的不同电影数量，用以下语句：

-- 统计每个用户首次观影日的不同电影数量
SELECT ID, name, day, COUNT(DISTINCT movies) AS distinct_movies
FROM moviewatching t1
WHERE day = (
    SELECT MIN(day) 
    FROM moviewatching t2 
    WHERE t2.ID = t1.ID
)
GROUP BY ID, name, day
ORDER BY ID;

方案2：窗口函数（MySQL 8.0+ 推荐） #

用RANK()窗口函数给每个用户的观影记录按日期升序排序，取排序值为1的就是首次观影日的记录：

-- 仅查询每个用户首次观影日的所有观影记录
WITH user_movie_rank AS (
    SELECT 
        ID, name, movies, day,
        RANK() OVER(PARTITION BY ID ORDER BY day ASC) AS rn
    FROM moviewatching
)
SELECT ID, name, movies, day
FROM user_movie_rank
WHERE rn = 1
ORDER BY ID;

统计数量的版本：

-- 统计每个用户首次观影日的不同电影数量
WITH user_movie_rank AS (
    SELECT 
        ID, name, movies, day,
        RANK() OVER(PARTITION BY ID ORDER BY day ASC) AS rn
    FROM moviewatching
)
SELECT ID, name, day, COUNT(DISTINCT movies) AS distinct_movies
FROM user_movie_rank
WHERE rn = 1
GROUP BY ID, name, day
ORDER BY ID;

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