查询分组后每个分组的前几条记录
时间:2014-10-30 17:21 来源:linux.it.net.cn 作者:it
在MySQL使用中，经常需要查询每个分组的前几条记录（查询分组后每一个组的前几项），下面写了个简单的例子说明下SQL的写法。简单的表设计如下，要求每个班总分排名最前的前两条数据。
测试表语句如下：
1
create table test(id int unsigned not null auto_increment primarykey,name varchar(10),class varchar(20),score varchar(20));
2
insert into test(name, class, score) values ('gonn', '6(1)', '299');
3
insert into test(name, class, score) values ('yyun', '6(1)', '259');
4
insert into test(name, class, score) values ('lin', '6(1)', '289');
5
insert into test(name, class, score) values ('mei', '6(1)', '277');
6
insert into test(name, class, score) values ('xj', '6(2)', '287');
7
insert into test(name, class, score) values ('zhl', '6(2)', '277');
8
insert into test(name, class, score) values ('lwjs', '6(2)', '257');
9
insert into test(name, class, score) values ('lulu', '6(2)', '265');
运行以上SQL，得到的表结构如下：
01
mysql> SELECT * FROM test;
02
+----+------+-------+-------+
03
| id | name | class | score |
04
+----+------+-------+-------+
05
| 1 | gonn | 6(1) | 299 |
06
| 2 | yyun | 6(1) | 259 |
07
| 3 | lin | 6(1) | 289 |
08
| 4 | mei | 6(1) | 277 |
09
| 5 | xj | 6(2) | 287 |
10
| 6 | zhl | 6(2) | 277 |
11
| 7 | lwjs | 6(2) | 257 |
12
| 8 | lulu | 6(2) | 265 |
13
+----+------+-------+-------+
14
8 rows in set
方法一
01
mysql> SELECT a.id,a.name,a.class,a.score
02
FROM test a LEFT JOIN test b on a.class = b.class and a.score < b.score
03
GROUP BY a.id,a.name,a.class,a.score
04
HAVING count(b.id) < 2
05
ORDER BY a.class,a.score DESC;
06
+----+------+-------+-------+
07
| id | name | class | score |
08
+----+------+-------+-------+
09
| 1 | gonn | 6(1) | 299 |
10
| 3 | lin | 6(1) | 289 |
11
| 5 | xj | 6(2) | 287 |
12
| 6 | zhl | 6(2) | 277 |
13
+----+------+-------+-------+
14
4 rows in set
方法二
01
mysql> SELECT * FROM test a
02
WHERE 2 >(SELECT count(*) FROM test WHERE class = a.class andscore>a.score)
03
ORDER BY a.class,a.score DESC;
04
+----+------+-------+-------+
05
| id | name | class | score |
06
+----+------+-------+-------+
07
| 1 | gonn | 6(1) | 299 |
08
| 3 | lin | 6(1) | 289 |
09
| 5 | xj | 6(2) | 287 |
10
| 6 | zhl | 6(2) | 277 |
11
+----+------+-------+-------+
12
4 rows in set
这里列出了多种SQL语句的实现方法，有些是MySQL特有的(Limit, 其它数据库可根据实际更改，比如oracle的rownum,MS SQL SERVER 的 top,..)，有时是SQL标准支持的。但效率上和应用的场合或许不同。具体应用时可根据实际表中的记录情况，索引情况进行选择。
(责任编辑：IT)
在MySQL使用中，经常需要查询每个分组的前几条记录（查询分组后每一个组的前几项），下面写了个简单的例子说明下SQL的写法。简单的表设计如下，要求每个班总分排名最前的前两条数据。 测试表语句如下：
运行以上SQL，得到的表结构如下：
方法一
方法二
这里列出了多种SQL语句的实现方法，有些是MySQL特有的(Limit, 其它数据库可根据实际更改，比如oracle的rownum,MS SQL SERVER 的 top,..)，有时是SQL标准支持的。但效率上和应用的场合或许不同。具体应用时可根据实际表中的记录情况，索引情况进行选择。 (责任编辑：IT) |