如何按日期限制SQL记录？实现每日返回5条数据的方法

直接在原语句末尾加LIMIT 0,5只能返回全局前5条记录，没法实现每个日期返回5条的需求。要达成目标，得用窗口函数（或低版本MySQL的变量）按日期分组给记录编号，再筛选每组内的前5条。

支持窗口函数的数据库（MySQL 8.0+、PostgreSQL、SQL Server等） #

这是最简洁的实现方式，利用ROW_NUMBER()窗口函数给每个日期分组内的记录编号：

SELECT *
FROM (
    SELECT 
        *,
        -- 按loginDate分组，组内按loginDate排序并编号
        ROW_NUMBER() OVER (PARTITION BY loginDate ORDER BY loginDate) AS row_num
    FROM UserLoginHistory 
    WHERE loginDate BETWEEN '2022-10-03' AND '2022-10-09'
) AS temp
-- 筛选每个日期的前5条记录
WHERE row_num <= 5
ORDER BY loginDate, row_num;

特殊情况处理（需去重场景） #

如果原语句的GROUP BY userId,loginDate是为了去重（同一用户同一天多次登录只保留一条），可以先完成去重再套窗口函数：

SELECT *
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY loginDate ORDER BY loginDate) AS row_num
    FROM (
        -- 先去重：同一用户同一天的登录记录只保留一条
        SELECT userId, loginDate
        FROM UserLoginHistory 
        WHERE loginDate BETWEEN '2022-10-03' AND '2022-10-09'
        GROUP BY userId, loginDate
    ) AS distinct_records
) AS temp
WHERE row_num <= 5
ORDER BY loginDate;

MySQL 5.x版本（不支持窗口函数） #

如果用的是不支持窗口函数的旧版MySQL，可以用用户变量实现分组编号：

SELECT 
    t.*
FROM (
    SELECT 
        *,
        -- 当当前日期和上一条相同时编号+1，否则重置为1
        @row_num := IF(@current_date = loginDate, @row_num + 1, 1) AS row_num,
        @current_date := loginDate
    FROM UserLoginHistory,
         -- 初始化变量
         (SELECT @current_date := '', @row_num := 0) AS init_vars
    WHERE loginDate BETWEEN '2022-10-03' AND '2022-10-09'
    -- 必须按loginDate排序，保证变量逻辑正确
    ORDER BY loginDate
) AS t
WHERE t.row_num <= 5;

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