查询分组后每个分组的前几条记录

附带简单的MySQL例子
服务器君一共花费了295.718 ms进行了6次数据库查询,努力地为您提供了这个页面。
试试阅读模式?希望听取您的建议

在MySQL使用中,经常需要查询每个分组的前几条记录(查询分组后每一个组的前几项),下面写了个简单的例子说明下SQL的写法。简单的表设计如下,要求每个班总分排名最前的前两条数据。

测试表语句如下:

create table test(id int unsigned not null auto_increment primary key,name varchar(10),class varchar(20),score varchar(20));
insert into test(name, class, score) values ('gonn', '6(1)', '299');
insert into test(name, class, score) values ('yyun', '6(1)', '259');
insert into test(name, class, score) values ('lin', '6(1)', '289');
insert into test(name, class, score) values ('mei', '6(1)', '277');
insert into test(name, class, score) values ('xj', '6(2)', '287');
insert into test(name, class, score) values ('zhl', '6(2)', '277');
insert into test(name, class, score) values ('lwjs', '6(2)', '257');
insert into test(name, class, score) values ('lulu', '6(2)', '265');

运行以上SQL,得到的表结构如下:

mysql> SELECT * FROM test;
+----+------+-------+-------+
| id | name | class | score |
+----+------+-------+-------+
|  1 | gonn | 6(1)  | 299   |
|  2 | yyun | 6(1)  | 259   |
|  3 | lin  | 6(1)  | 289   |
|  4 | mei  | 6(1)  | 277   |
|  5 | xj   | 6(2)  | 287   |
|  6 | zhl  | 6(2)  | 277   |
|  7 | lwjs | 6(2)  | 257   |
|  8 | lulu | 6(2)  | 265   |
+----+------+-------+-------+
8 rows in set

方法一

mysql> SELECT a.id,a.name,a.class,a.score
FROM test a LEFT JOIN test b on a.class = b.class and a.score < b.score
GROUP BY a.id,a.name,a.class,a.score
HAVING count(b.id) < 2
ORDER BY a.class,a.score DESC;
+----+------+-------+-------+
| id | name | class | score |
+----+------+-------+-------+
|  1 | gonn | 6(1)  | 299   |
|  3 | lin  | 6(1)  | 289   |
|  5 | xj   | 6(2)  | 287   |
|  6 | zhl  | 6(2)  | 277   |
+----+------+-------+-------+
4 rows in set

方法二

mysql> SELECT * FROM test a
WHERE 2 >(SELECT count(*) FROM test WHERE class = a.class and score>a.score)
ORDER BY a.class,a.score DESC;
+----+------+-------+-------+
| id | name | class | score |
+----+------+-------+-------+
|  1 | gonn | 6(1)  | 299   |
|  3 | lin  | 6(1)  | 289   |
|  5 | xj   | 6(2)  | 287   |
|  6 | zhl  | 6(2)  | 277   |
+----+------+-------+-------+
4 rows in set

这里列出了多种SQL语句的实现方法,有些是MySQL特有的(Limit, 其它数据库可根据实际更改,比如oracle的rownum,MS SQL SERVER 的 top,..),有时是SQL标准支持的。但效率上和应用的场合或许不同。具体应用时可根据实际表中的记录情况,索引情况进行选择。

本文地址:http://www.nowamagic.net/librarys/veda/detail/1910,欢迎访问原出处。

不打个分吗?

转载随意,但请带上本文地址:

http://www.nowamagic.net/librarys/veda/detail/1910

如果你认为这篇文章值得更多人阅读,欢迎使用下面的分享功能。
小提示:您可以按快捷键 Ctrl + D,或点此 加入收藏

大家都在看

阅读一百本计算机著作吧,少年

很多人觉得自己技术进步很慢,学习效率低,我觉得一个重要原因是看的书少了。多少是多呢?起码得看3、4、5、6米吧。给个具体的数量,那就100本书吧。很多人知识结构不好而且不系统,因为在特定领域有一个足够量的知识量+足够良好的知识结构,系统化以后就足以应对大量未曾遇到过的问题。

奉劝自学者:构建特定领域的知识结构体系的路径中再也没有比学习该专业的专业课程更好的了。如果我的知识结构体系足以囊括面试官的大部分甚至吞并他的知识结构体系的话,读到他言语中的一个词我们就已经知道他要表达什么,我们可以让他坐“上位”毕竟他是面试官,但是在知识结构体系以及心理上我们就居高临下。

所以,阅读一百本计算机著作吧,少年!

《UNIX环境高级编程(第2版)》 史蒂文斯 (作者), 拉戈 (作者), 尤晋元 (译者), 张亚英 (译者), 戚正伟 (译者)

《UNIX环境高级编程(第2版)》是被誉为UNIX编程“圣经”的Advanced Programming in the UNIX Environment一书的更新版。在本书第1版出版后的十几年中,UNIX行业已经有了巨大的变化,特别是影响UNIX编程接口的有关标准变化很大。本书在保持了前一版的风格的基础上,根据最新的标准对内容进行了修订和增补,反映了最新的技术发展。书中除了介绍UNIX文件和目录、标准I/O库、系统数据文件和信息、进程环境、进程控制、进程关系、信号、线程、线程控制、守护进程、各种I/O、进程间通信、网络IPC、伪终端等方面的内容,还在此基础上介绍了多个应用示例,包括如何创建数据库函数库以及如何与网络打印机通信等。

更多计算机宝库...