如何限制SQL查询中IN子句每个元素对应的返回行数

实现方案 #

通用窗口函数方案（支持MySQL8.0+、PostgreSQL、SQL Server、Oracle等所有支持窗口函数的数据库） #

用ROW_NUMBER()窗口函数给每个field2值对应的行单独编号，筛选编号≤25的即可实现需求：

WITH ranked_data AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY field2 ORDER BY 替换为你需要的排序字段) AS row_num
    FROM table1
    WHERE field1 = 4 
      AND field2 IN (2,3,4,5)
)
SELECT * 
FROM ranked_data 
WHERE row_num <= 25;

注意：ORDER BY后请替换为实际业务需要的排序规则，比如按主键、创建时间排序，否则返回的25行是随机结果。如果确实不需要固定排序，只要随机取25行，可以写ORDER BY RAND()（MySQL/PostgreSQL）或者ORDER BY NEWID()（SQL Server）。

MySQL 5.x 兼容方案（不支持窗口函数的老版本） #

可以用自定义变量模拟行号实现相同效果：

SELECT * 
FROM (
    SELECT 
        *,
        @row_num := IF(@current_field2 = field2, @row_num + 1, 1) AS row_num,
        @current_field2 := field2
    FROM table1
    CROSS JOIN (SELECT @row_num := 0, @current_field2 := NULL) AS vars
    WHERE field1 = 4 
      AND field2 IN (2,3,4,5)
    ORDER BY field2, 替换为你需要的排序字段
) AS ranked_data
WHERE row_num <= 25;

补充说明 #

如果某个field2值对应的总数据量不足25行，上述语句会返回该值的全部可用数据，最终总条数会小于100，如果需要强制补到100行，可以额外加空值填充逻辑。如果需要处理排序字段值重复的取数场景，可以把ROW_NUMBER()替换为RANK()或者DENSE_RANK()。

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