MySQL：为WHERE IN条件中的每个值最多选取2行数据

问题场景 #

需要优化查询，让SELECT * FROM files WHERE department IN (2,3,4);返回每个指定部门最多2行数据。相关数据表信息、期望结果及建表语句如下：

现有数据表 #

期望查询结果 #

建表及插入数据语句 #

CREATE TABLE files (department INT, file VARCHAR(20));
INSERT INTO files (department, file) VALUES
(1, "Innovation Arch"),(1, "Strat Security"),(1, "Inspire Fitness Co"),(1, "Candor Corp"),
(2, "Cogent Data"),(2, "Epic Adventure Inc"),(2, "Sanguine Skincare"),(2, "Vortex Solar"),
(3, "Admire Arts"),(3, "Bravura Inc"),(3, "Bonefete Fun"),(3, "Moxie Marketing"),(3, "Zeal Wheels"),
(4, "Obelus Concepts");

解决方案 #

方案1：使用窗口函数（MySQL 8.0+ 推荐） #

利用ROW_NUMBER()窗口函数按部门分组并编号，筛选出每个部门编号≤2的行。可通过ORDER BY控制选取特定行（比如部门2取升序前2，部门3取降序前2）：

SELECT department, file
FROM (
    SELECT 
        department, 
        file,
        ROW_NUMBER() OVER (
            PARTITION BY department 
            ORDER BY CASE department 
                WHEN 3 THEN file DESC 
                ELSE file ASC 
            END
        ) AS row_num
    FROM files
    WHERE department IN (2,3,4)
) AS ranked
WHERE row_num <= 2;

若无需针对不同部门设置排序规则，统一按file升序取每个部门前2行，可简化为：

SELECT department, file
FROM (
    SELECT 
        department, 
        file,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY file) AS row_num
    FROM files
    WHERE department IN (2,3,4)
) AS ranked
WHERE row_num <= 2;

方案2：兼容MySQL 5.x版本（无窗口函数） #

针对不支持窗口函数的旧版本MySQL，使用用户变量实现分组编号：

SELECT department, file
FROM (
    SELECT 
        department, 
        file,
        @row_num := IF(@current_dept = department, @row_num + 1, 1) AS row_num,
        @current_dept := department
    FROM files, (SELECT @row_num := 0, @current_dept := NULL) AS vars
    WHERE department IN (2,3,4)
    ORDER BY 
        department,
        CASE department WHEN 3 THEN file DESC ELSE file ASC END
) AS ranked
WHERE row_num <= 2;

关键逻辑说明 #

• PARTITION BY department：将数据按部门分组，每个部门独立计算行号
• ROW_NUMBER()/用户变量：给每个部门内的行按指定顺序分配唯一编号
• 调整ORDER BY子句可灵活控制选取每个部门的目标行（升序取前N，降序取后N）

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