MySQL5 实现ROW_NUMBER 查询每天发文章最多的2个用户

我用的是MySQL5.7 还不支持 ROW_NUMBER，只能另寻他法。

以日期、作者分组，

SELECT LEFT(addtime,10) as date, writer, count(writer) as writer_count FROM archives
GROUP BY date,writer ORDER BY date DESC, writer_count DESC

查询结果有多余数据，每天只需要取出前两行

以日期分组标出序号 row_num，

SET @num = 0;
SET @date = '';
SELECT *, @num := if(@date=date, @num := @num+1, 1) as row_num, @date := date as '@date' FROM
    ( SELECT LEFT(addtime,10) as date, writer, count(writer) as writer_count FROM archives
      GROUP BY date,writer ORDER BY date DESC, writer_count DESC
    ) as archives_count

WHERE row_num <= 2 就行了

SET @num = 0;
SET @date = '';
SELECT * FROM 
    ( SELECT *, @num := if(@date=date, @num := @num+1, 1) as row_num, @date := date as '@date' FROM
            ( SELECT LEFT(addtime,10) as date, writer, count(writer) as writer_count FROM archives
              GROUP BY date,writer ORDER BY date DESC, writer_count DESC
            ) as archives_count
    ) as archives_count_row_num
WHERE row_num<=2

MySQL5 实现ROW_NUMBER 查询每天发文章最多的2个用户

推荐阅读