SQL如何在单条查询中实现按日期单日最多返回N行的限制

实现方案 #

可以通过单条SQL实现该需求，核心是用窗口函数做分组内随机排序+序号筛选，仅需一次表扫描，效率远高于逐天查询。

通用实现逻辑（支持MySQL 8.0+、PostgreSQL、Oracle等主流支持窗口函数的数据库） #

将代码中的你的表名、时间范围起止值、N（单日最大返回条数）替换为实际值即可：

SELECT item, vietnamese, cost, unique_id, sales_date
FROM (
    SELECT 
        *,
        -- 按销售日期分组，组内随机排序后标记行号
        ROW_NUMBER() OVER (PARTITION BY sales_date ORDER BY RAND()) AS rn
    FROM 你的表名
    -- 填入所需查询的时间范围
    WHERE sales_date >= '20240101' AND sales_date <= '20240131'
) t
WHERE rn <= N;

MySQL 5.x 旧版本兼容方案（无窗口函数场景） #

可通过变量模拟窗口函数的分组排序逻辑：

SELECT item, vietnamese, cost, unique_id, sales_date
FROM (
    SELECT 
        *,
        @rn := IF(@current_date = sales_date, @rn + 1, 1) AS rn,
        @current_date := sales_date
    FROM (
        SELECT *
        FROM 你的表名
        WHERE sales_date >= '20240101' AND sales_date <= '20240131'
        ORDER BY sales_date, RAND()
    ) t1,
    (SELECT @rn := 0, @current_date := '') AS vars
) t2
WHERE rn <= N;

注意事项 #

• 不同数据库的随机函数存在差异：MySQL用RAND()，PostgreSQL用RANDOM()，Oracle用DBMS_RANDOM.VALUE()，按需替换即可。
• 若单日符合条件的数据不足N条，代码会自动返回该日全部匹配数据，无需额外调整。
• 若需要全局返回结果整体随机打乱，可在外层查询末尾追加ORDER BY 随机函数()即可。

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