如何用SQL查询获取用户记录及合并后的最新两条评论

问题分析 #

你之前的查询已经通过ROW_NUMBER()筛选出了每个用户的最新两条评论，但没有将这些评论合并为单个字符串，而是返回了多行记录。要实现预期效果，需要使用字符串聚合函数将同一用户的多条评论拼接成一列。

解决方案 #

根据你使用的数据库类型，选择对应的聚合函数即可，以下是主流数据库的实现方式：

MySQL 实现

WITH cteRowNumber AS (
    SELECT 
        u.id AS user_id,
        u.name,
        u.email,
        uc.comment,
        ROW_NUMBER() OVER (PARTITION BY uc.user_id ORDER BY uc.id DESC) AS RowNum
    FROM user_comments uc
    JOIN users u ON u.id = uc.user_id
)
SELECT 
    user_id,
    name,
    email,
    GROUP_CONCAT(comment ORDER BY RowNum SEPARATOR ', ') AS comment
FROM cteRowNumber
WHERE RowNum <= 2
GROUP BY user_id, name, email;

PostgreSQL/SQL Server 实现

WITH cteRowNumber AS (
    SELECT 
        u.id AS user_id,
        u.name,
        u.email,
        uc.comment,
        ROW_NUMBER() OVER (PARTITION BY uc.user_id ORDER BY uc.id DESC) AS RowNum
    FROM user_comments uc
    JOIN users u ON u.id = uc.user_id
)
SELECT 
    user_id,
    name,
    email,
    STRING_AGG(comment, ', ' ORDER BY RowNum) AS comment
FROM cteRowNumber
WHERE RowNum <= 2
GROUP BY user_id, name, email;

Oracle 实现

WITH cteRowNumber AS (
    SELECT 
        u.id AS user_id,
        u.name,
        u.email,
        uc.comment,
        ROW_NUMBER() OVER (PARTITION BY uc.user_id ORDER BY uc.id DESC) AS RowNum
    FROM user_comments uc
    JOIN users u ON u.id = uc.user_id
)
SELECT 
    user_id,
    name,
    email,
    LISTAGG(comment, ', ') WITHIN GROUP (ORDER BY RowNum) AS comment
FROM cteRowNumber
WHERE RowNum <= 2
GROUP BY user_id, name, email;

关键说明 #

1. 筛选最新评论：通过ROW_NUMBER() OVER (PARTITION BY uc.user_id ORDER BY uc.id DESC)为每个用户的评论按ID倒序编号，取前2条（RowNum <= 2）确保只保留最新的两条。
2. 聚合拼接：使用对应数据库的字符串聚合函数（GROUP_CONCAT/STRING_AGG/LISTAGG），将同一用户的多条评论按编号顺序拼接成逗号分隔的字符串。
3. 分组逻辑：按user_id, name, email分组，确保每个用户只返回一行记录，避免之前查询中按comment分组导致的多行结果。

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