如何按列值限制查询结果？为每个user_id返回指定行数

嘿，这问题我太熟了！先理清楚你的需求：从EventUser表中查询user_id为1和2的数据，但每个用户只返回指定行数（比如2行）。先回顾下你的原始查询和结果：

原始查询 #

SELECT event_id, user_id FROM EventUser WHERE user_id IN (1, 2)

原始查询结果 #

+----------+---------+
| event_id | user_id |
+----------+---------+
| 3        | 1       |
| 2        | 1       |
| 1        | 1       |
| 5        | 1       |
| 4        | 1       |
| 6        | 1       |
| 4        | 2       |
| 2        | 2       |
| 1        | 2       |
| 5        | 2       |
+----------+---------+

预期结果（每个user_id返回2行） #

+----------+---------+
| event_id | user_id |
+----------+---------+
| 3        | 1       |
| 2        | 1       |
| 4        | 2       |
| 2        | 2       |
+----------+---------+

接下来给你几种不同数据库环境下的靠谱解决方案：

1. 通用窗口函数方案（适用于MySQL 8.0+、PostgreSQL、SQL Server等） #

这是最推荐的方法，利用ROW_NUMBER()窗口函数对每个用户分组排序，然后筛选出每组的前N行（这里N=2）：

SELECT event_id, user_id
FROM (
    SELECT 
        event_id, 
        user_id,
        -- 按user_id分组，每组内按event_id降序排，生成行号
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_id DESC) AS row_num
    FROM EventUser
    WHERE user_id IN (1, 2)
) AS ranked_data
-- 只保留每组的前2行
WHERE row_num <= 2;

提示：你可以修改ORDER BY后面的字段来控制每组内返回哪些行，比如如果有时间字段，按时间排序会更有意义。

2. MySQL 5.x兼容方案（不支持窗口函数） #

如果你的MySQL版本比较老，没法用窗口函数，可以用用户变量来实现分组计数：

SELECT event_id, user_id
FROM (
    SELECT 
        event_id, 
        user_id,
        -- 当用户ID不变时行号+1，否则重置为1
        @row_num := IF(@current_user = user_id, @row_num + 1, 1) AS row_num,
        @current_user := user_id
    FROM EventUser, 
         -- 初始化变量
         (SELECT @row_num := 0, @current_user := NULL) AS init_vars
    WHERE user_id IN (1, 2)
    -- 必须先按user_id排序，保证变量计数正确
    ORDER BY user_id, event_id DESC
) AS ranked_data
WHERE row_num <= 2;

3. PostgreSQL简洁写法（13+版本支持） #

PostgreSQL 13及以上支持QUALIFY子句，可以直接在主查询中筛选窗口函数结果，省去子查询：

SELECT event_id, user_id
FROM EventUser
WHERE user_id IN (1, 2)
-- 直接筛选每组前2行
QUALIFY ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_id DESC) <= 2;

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