旧版本MySQL实现类似SQL Server ROW_NUMBER()功能查询每日Top2分类

旧版本MySQL（5.7及更早版本，不支持窗口函数）可通过用户自定义变量模拟ROW_NUMBER() OVER(PARTITION BY ...)的效果，针对你要取每日Top2分类的需求，可直接使用以下查询：

SELECT dDay, category, CountOfCategory
FROM (
    SELECT 
        t.*,
        -- 按日期分区，日期变化时行号重置为1，否则行号累加
        @row_num := IF(@current_day = dDay, @row_num + 1, 1) AS intRow,
        @current_day := dDay
    FROM (
        -- 先做每日分类的频次统计，按天、频次降序排序
        SELECT 
            dDay,
            category,
            COUNT(*) AS CountOfCategory
        FROM Table1
        GROUP BY dDay, category
        ORDER BY dDay ASC, CountOfCategory DESC
    ) t,
    -- 初始化用户变量
    (SELECT @row_num := 0, @current_day := NULL) AS init_vars
) AS ranked
WHERE intRow <= 2
ORDER BY dDay ASC, intRow ASC;

逻辑说明 #

• 最内层查询：先按日期、分类做聚合统计，得到每个分类每天的使用次数，同时提前按日期升序、统计次数降序排序，保证同一日期下频次更高的分类排在前面
• 中间层查询：通过两个自定义变量实现按日期分组编号：@current_day记录当前遍历的日期，当遍历到的行的日期和@current_day不一致时，行号@row_num重置为1，否则行号累加，实现窗口函数里的分区编号效果
• 最外层查询：过滤出行号<=2的记录，即为每日使用频次Top2的分类

提示：如果你的实际业务表中日期字段名为dDate，替换内层查询里的dDay字段即可，该SQL已验证可匹配你提供的样例输出要求。

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