MySQL分组排序取前N条记录以及生成自动数字序列--group by 后 limit 外加 rownumber

同事提了一个需求，要求按照某列分组，然后将各组的前几条抽取出来。

表结构

CREATE TABLE `total_freq_ctrl` (
  `time` int(10) unsigned NOT NULL,
  `machine` char(64) NOT NULL,
  `module` char(32) NOT NULL,
  `total_flow` int(10) unsigned NOT NULL,
  `deny_flow` int(10) unsigned NOT NULL,
  PRIMARY KEY (`module`,`machine`,`time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

[email protected]

原sql

SELECT machine, deny_flow, total_flow, time FROM total_freq_ctrl A WHERE 1 > (SELECT COUNT(machine) FROM total_freq_ctrl WHERE machine = A.machine AND time > A.time) AND A.module = 'all' ORDER BY A.time desc;

只要将 1 改成N就变成取每组的前N条，因为我极其不喜欢子查询，就改就尝试改称join 的方式。

不过这里需要对所有数据进行排序才能确定每组的前N条，所以最佳优化也要全表扫描一次。

首先我要对表中数据进行排序，引入一个变