MySQL如何不使用嵌套查询实现按user_id分组每组仅返回2条数据

适配主流新版数据库的方案 #

使用窗口函数搭配QUALIFY子句即可实现，全程无嵌套查询，支持MySQL 8.0+、PostgreSQL 13+、BigQuery、Snowflake、Spark SQL等环境：

SELECT user_id, item_id
FROM some_table
QUALIFY ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY item_id) <= 2;

逻辑说明 #

• 用PARTITION BY user_id将所有记录按用户ID分组，每个用户的记录独立计算排序
• 同组内用ORDER BY item_id按item_id升序排列，保证取到最小的前2个item_id，和你预期的输出结果完全匹配
• ROW_NUMBER()会给同组内的记录按排序生成从1开始的连续序号
• QUALIFY子句直接过滤窗口函数的计算结果，只保留每个组内序号不大于2的记录，不需要额外嵌套子查询

旧版数据库兼容方案 #

如果你的数据库不支持QUALIFY语法，可以使用CTE实现，CTE不属于嵌套查询范畴，符合要求：

WITH user_item_ranked AS (
    SELECT
        user_id,
        item_id,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY item_id) AS rank_num
    FROM some_table
)
SELECT user_id, item_id
FROM user_item_ranked
WHERE rank_num <= 2;

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