MySQL中用FIRST_VALUE获取帖子首末赞并实现单帖单行问题

你遇到的问题根源是FIRST_VALUE和LAST_VALUE这类窗口函数会为每个分组（即每个帖子）内的每一行点赞记录都返回计算结果，关联点赞表后，每条点赞数据都会生成一行结果，导致同一帖子出现多行重复。下面给你三种直接可行的解决办法：

方法一：添加DISTINCT去重 #

直接在SELECT语句开头加上DISTINCT，因为同一帖子的首个/最后点赞信息是固定的，去重后就能得到每个帖子唯一一行的结果。

示例SQL：

SELECT DISTINCT
    p.post_id,
    p.content,
    FIRST_VALUE(l.like_id) OVER (
        PARTITION BY p.post_id 
        ORDER BY l.like_time ASC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS first_like_id,
    FIRST_VALUE(l.like_time) OVER (
        PARTITION BY p.post_id 
        ORDER BY l.like_time ASC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS first_like_time,
    LAST_VALUE(l.like_id) OVER (
        PARTITION BY p.post_id 
        ORDER BY l.like_time ASC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_like_id,
    LAST_VALUE(l.like_time) OVER (
        PARTITION BY p.post_id 
        ORDER BY l.like_time ASC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_like_time
FROM posts p
LEFT JOIN likes l ON p.post_id = l.post_id;

注意：加上ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING是为了确保LAST_VALUE能取到分组内的最后一条数据，默认窗口范围是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，会导致只取到当前行及之前的最后值，可能不符合预期。

方法二：用ROW_NUMBER()筛选唯一行 #

通过ROW_NUMBER()给每个帖子的点赞记录编号，然后只取编号为1的行，这样就能得到每个帖子的单行结果。

示例SQL：

WITH post_likes AS (
    SELECT
        p.post_id,
        p.content,
        FIRST_VALUE(l.like_id) OVER (PARTITION BY p.post_id ORDER BY l.like_time ASC) AS first_like_id,
        FIRST_VALUE(l.like_time) OVER (PARTITION BY p.post_id ORDER BY l.like_time ASC) AS first_like_time,
        LAST_VALUE(l.like_id) OVER (
            PARTITION BY p.post_id 
            ORDER BY l.like_time ASC
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS last_like_id,
        LAST_VALUE(l.like_time) OVER (
            PARTITION BY p.post_id 
            ORDER BY l.like_time ASC
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS last_like_time,
        ROW_NUMBER() OVER (PARTITION BY p.post_id ORDER BY l.like_time ASC) AS rn
    FROM posts p
    LEFT JOIN likes l ON p.post_id = l.post_id
)
SELECT post_id, content, first_like_id, first_like_time, last_like_id, last_like_time
FROM post_likes
WHERE rn = 1;

方法三：用聚合函数+子查询实现 #

如果不想用窗口函数，也可以通过聚合函数找到每个帖子的最早/最晚点赞时间，再关联点赞表获取对应的like_id，这种方式逻辑更直观。

示例SQL：

SELECT
    p.post_id,
    p.content,
    fl.like_id AS first_like_id,
    fl.like_time AS first_like_time,
    ll.like_id AS last_like_id,
    ll.like_time AS last_like_time
FROM posts p
LEFT JOIN (
    SELECT post_id, MIN(like_time) AS min_like_time, MAX(like_time) AS max_like_time
    FROM likes
    GROUP BY post_id
) lt ON p.post_id = lt.post_id
LEFT JOIN likes fl ON lt.post_id = fl.post_id AND lt.min_like_time = fl.like_time
LEFT JOIN likes ll ON lt.post_id = ll.post_id AND lt.max_like_time = ll.like_time;

如果同一时间有多个点赞记录，这个方法会返回多行，此时可以结合DISTINCT或者用FIRST_VALUE在子查询里取唯一的like_id。

内容的提问来源于stack exchange，提问作者Константин Сурков