Discord用户数据SQL查询需求：获取最新昵称与关键词统计

已在phpMyAdmin中创建名为table_one的表，包含以下字段：

• USER_ID：Discord用户ID，对应message.author.id
• USER_NAME：Discord用户名，对应message.author.name
• USER_NICKNAME：服务器显示名，对应message.author.display_name
• TIMESTAMP：消息创建时间戳，对应message.created_at
• MESSAGE_CONTENT：清洗后的消息关键词（示例值：apple、orange）

需要编写SQL查询或视图，返回以下结果：

1. 用户基于最新TIMESTAMP的最新USER_NICKNAME
2. 用户输入特定关键词的总次数（例如仅统计apple，排除orange）

要求合并统计用户修改昵称前后的同一关键词输入次数，显示最新昵称及总次数。现有查询可按USER_ID分组统计，但需替换为显示最新USER_NICKNAME，现有查询如下：

SELECT USER_ID, COUNT(USER_ID) FROM table_one WHERE MESSAGE_CONTENT = 'apple' GROUP BY USER_ID

方法1：子查询关联获取最新昵称 #

先通过子查询找出每个用户的最新时间戳对应的昵称，再关联原表完成关键词次数统计：

SELECT 
    t.USER_ID,
    latest_nick.USER_NICKNAME,
    COUNT(t.USER_ID) AS KEYWORD_COUNT
FROM table_one t
JOIN (
    SELECT USER_ID, USER_NICKNAME
    FROM table_one
    WHERE (USER_ID, TIMESTAMP) IN (
        SELECT USER_ID, MAX(TIMESTAMP)
        FROM table_one
        GROUP BY USER_ID
    )
) latest_nick ON t.USER_ID = latest_nick.USER_ID
WHERE t.MESSAGE_CONTENT = 'apple'
GROUP BY t.USER_ID, latest_nick.USER_NICKNAME;

方法2：窗口函数实现（MySQL 8.0+ 适用） #

利用ROW_NUMBER()窗口函数标记每个用户的最新记录，筛选出最新昵称后再统计关键词次数：

WITH user_latest_nick AS (
    SELECT 
        USER_ID,
        USER_NICKNAME,
        ROW_NUMBER() OVER (PARTITION BY USER_ID ORDER BY TIMESTAMP DESC) AS rn
    FROM table_one
)
SELECT 
    t.USER_ID,
    uln.USER_NICKNAME,
    COUNT(t.USER_ID) AS KEYWORD_COUNT
FROM table_one t
JOIN user_latest_nick uln ON t.USER_ID = uln.USER_ID AND uln.rn = 1
WHERE t.MESSAGE_CONTENT = 'apple'
GROUP BY t.USER_ID, uln.USER_NICKNAME;

补充说明 #

• 两种方法均基于USER_ID合并统计次数，确保昵称修改前后的同用户关键词次数不会拆分
• 将查询中的'apple'替换为目标关键词即可统计其他内容
• 若需创建视图，只需在查询语句前添加CREATE VIEW [视图名] AS

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