mysql分组保留n条,Mysql取分组中前N条记录

表结构如下：

CREATE TABLE `dwb_rmirror_req_d` (

`thedate` varchar(10) NOT NULL DEFAULT ‘‘,

`node` varchar(15) NOT NULL DEFAULT ‘‘,

`req_num` bigint(20) DEFAULT NULL,

PRIMARY KEY (`thedate`,`node`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

表中的记录如下：

mysql> select * from dwb_rmirror_req_d;

+----------+------+------------+

| thedate | node | req_num |

+----------+------+------------+

| 20160215 | f | 2 |

| 20160215 | i | 1 |

| 20160215 | l | 3 |

| 20160217 | f | 2 |

| 20160217 | i | 1 |

| 20160217 | l | 3 |

| 20160218 | f | 2 |

| 20160218 | i | 1 |

| 20160218 | l | 3 |

| 20160219 | f | 2 |

| 20160219 | i | 1 |

| 20160219 | l | 3 |

| 20160220 | f | 2 |

| 20160220 | i | 1 |

| 20160220 | l | 3 |

| 20160221 | f | 2 |

| 20160221 | i | 1 |

| 20160221 | l | 3 |

+----------+------+------------+

18 rows in set (0.00 sec)

1.获取每天查询量最大的记录：

select a.thedate,a.node,a.req_num from dwb_rmirror_req_d a left join dwb_rmirror_req_d b

on a.thedate = b.thedate and a.req_num <= b.req_num

group by a.thedate,a.node,a.req_num

having count(b.node)<=1;

结果如下：

+----------+------+------------+

| thedate | node | req_num |

+----------+------+------------+

| 20160215 | l | 3 |

| 20160217 | l | 3 |

| 20160218 | l | 3 |

| 20160219 | l | 3 |

| 20160220 | l | 3 |

| 20160221 | l | 3 |

+----------+------+------------+

6 rows in set (0.01 sec)

2. 获取每天查询量最高的两条记录：

select a.thedate,a.node,a.req_num from dwb_rmirror_req_d a left join dwb_rmirror_req_d b

on a.thedate = b.thedate and a.req_num <= b.req_num

group by a.thedate,a.node,a.req_num

having count(b.node)<=2

order by a.thedate,a.req_num;

结果如下：

+----------+------+------------+

| thedate | node | req_num |

+----------+------+------------+

| 20160215 | f | 2 |

| 20160215 | l | 3 |

| 20160217 | f | 2 |

| 20160217 | l | 3 |

| 20160218 | f | 2 |

| 20160218 | l | 3 |

| 20160219 | f | 2 |

| 20160219 | l | 3 |

| 20160220 | f | 2 |

| 20160220 | l | 3 |

| 20160221 | f | 2 |

| 20160221 | l | 3 |

+----------+------+------------+

12 rows in set (0.01 sec)

原文：http://www.cnblogs.com/zhzhang/p/5336935.html