如何优化多类别SQL查询，确保每个类别返回指定行数？

核心方案：使用窗口函数ROW_NUMBER() #

这是目前通用且高效的实现方式，支持MySQL 8.0+、PostgreSQL、SQL Server等主流数据库，能精准控制每个类别返回的行数，同时保证每个类别至少返回已有数据。

假设你的表结构为：

• 表名：data_table
• 类别字段：category（值如a、b、c等）
• 时间戳字段：create_time（用于排序取最新数据）

要每个类别取最新3条数据，SQL语句如下：

SELECT *
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY create_time DESC) AS row_num
    FROM data_table
    -- 若需针对每个类别指定不同的时间戳上限，可在这里加WHERE条件：
    -- WHERE (category = 'a' AND create_time < '2024-01-01') 
    --    OR (category = 'b' AND create_time < '2024-02-01')
    --    OR (category = 'c' AND create_time < '2024-03-01')
) AS ranked_data
WHERE row_num <= 3;

逻辑说明 #

1. PARTITION BY category：将数据按类别分组，每个类别单独处理
2. ORDER BY create_time DESC：每个类别内按时间戳倒序排列，确保最新数据排在前面
3. ROW_NUMBER()：给每个类别内的行分配唯一编号（从1开始）
4. 外层查询筛选row_num <= 3的行，即可得到每个类别最新的3条数据；若某个类别不足3条，会返回该类别所有存在的数据

自定义行数 #

只需修改外层WHERE子句的数字即可，比如要每个类别取5条，就改成row_num <= 5。

兼容老版本MySQL（5.x）的替代方案 #

如果你的数据库不支持窗口函数，可以使用用户变量实现：

SELECT *
FROM (
    SELECT 
        *,
        @row_num := IF(@current_category = category, @row_num + 1, 1) AS row_num,
        @current_category := category
    FROM data_table
    -- 同样可在这里添加各类别时间戳限制的WHERE条件
    ORDER BY category, create_time DESC
) AS ranked_data
WHERE row_num <= 3;

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