有时会碰到一些需求,查询分组后的最大值,最小值所在的整行记录或者分组后的top n行的记录,在一些别的数据库可能有窗口函数可以方面的查出来,但是MySQL没有这些函数,没有直接的方法可以查出来,可通过以下的方法来查询。
准备工作
测试表结构如下:
root:test> show create table test1\G *************************** 1. row *************************** Table: test1 Create Table: CREATE TABLE `test1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL, `course` varchar(20) DEFAULT NULL, `score` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
插入数据:
insert into test1(name,course,score) values ('张三','语文',80), ('李四','语文',90), ('王五','语文',93), ('张三','数学',77), ('李四','数学',68), ('王五','数学',99), ('张三','英语',90), ('李四','英语',50), ('王五','英语',89);
查看结果:
root:test> select * from test1; +----+--------+--------+-------+ | id | name | course | score | +----+--------+--------+-------+ | 1 | 张三 | 语文 | 80 | | 2 | 李四 | 语文 | 90 | | 3 | 王五 | 语文 | 93 | | 4 | 张三 | 数学 | 77 | | 5 | 李四 | 数学 | 68 | | 6 | 王五 | 数学 | 99 | | 7 | 张三 | 英语 | 90 | | 8 | 李四 | 英语 | 50 | | 9 | 王五 | 英语 | 89 | +----+--------+--------+-------+
TOP 1
查询每门课程分数最高的学生以及成绩
1、使用自连接【推荐】
root:test> select a.name,a.course,a.score from -> test1 a -> join (select course,max(score) score from test1 group by course) b -> on a.course=b.course and a.score=b.score; +--------+--------+-------+ | name | course | score | +--------+--------+-------+ | 王五 | 语文 | 93 | | 王五 | 数学 | 99 | | 张三 | 英语 | 90 | +--------+--------+-------+ 3 rows in set (0.00 sec)
2、使用相关子查询
root:test> select name,course,score from test1 a -> where score=(select max(score) from test1 where a.course=test1.course); +--------+--------+-------+ | name | course | score | +--------+--------+-------+ | 王五 | 语文 | 93 | | 王五 | 数学 | 99 | | 张三 | 英语 | 90 | +--------+--------+-------+ 3 rows in set (0.00 sec)
或者
root:test> select name,course,score from test1 a -> where not exists(select 1 from test1 where a.course=test1.course and a.score < test1.score); +--------+--------+-------+ | name | course | score | +--------+--------+-------+ | 王五 | 语文 | 93 | | 王五 | 数学 | 99 | | 张三 | 英语 | 90 | +--------+--------+-------+ 3 rows in set (0.00 sec)
TOP N
N>=1
查询每门课程前两名的学生以及成绩
1、使用union all
如果结果集比较小,可以用程序查询单个分组结果后拼凑,也可以使用union all
root:test> (select name,course,score from test1 where course='语文' order by score desc limit 2) -> union all -> (select name,course,score from test1 where course='数学' order by score desc limit 2) -> union all -> (select name,course,score from test1 where course='英语' order by score desc limit 2); +--------+--------+-------+ | name | course | score | +--------+--------+-------+ | 王五 | 语文 | 93 | | 李四 | 语文 | 90 | | 王五 | 数学 | 99 | | 张三 | 数学 | 77 | | 张三 | 英语 | 90 | | 王五 | 英语 | 89 | +--------+--------+-------+ 6 rows in set (0.01 sec)
2、自身左连接
root:test> select a.name,a.course,a.score -> from test1 a left join test1 b on a.course=b.course and a.score<b.score -> group by a.name,a.course,a.score -> having count(b.id)<2 -> order by a.course,a.score desc; +--------+--------+-------+ | name | course | score | +--------+--------+-------+ | 王五 | 数学 | 99 | | 张三 | 数学 | 77 | | 张三 | 英语 | 90 | | 王五 | 英语 | 89 | | 王五 | 语文 | 93 | | 李四 | 语文 | 90 | +--------+--------+-------+ 6 rows in set (0.00 sec)
3、相关子查询
root:test> select * -> from test1 a -> where 2>(select count(*) from test1 where course=a.course and score>a.score) -> order by a.course,a.score desc; +----+--------+--------+-------+ | id | name | course | score | +----+--------+--------+-------+ | 6 | 王五 | 数学 | 99 | | 4 | 张三 | 数学 | 77 | | 7 | 张三 | 英语 | 90 | | 9 | 王五 | 英语 | 89 | | 3 | 王五 | 语文 | 93 | | 2 | 李四 | 语文 | 90 | +----+--------+--------+-------+ 6 rows in set (0.01 sec)
4、使用用户变量
root:test> set @num := 0, @course := ''; Query OK, 0 rows affected (0.00 sec) root:test> root:test> select name, course, score -> from ( -> select name, course, score, -> @num := if(@course = course, @num + 1, 1) as row_number, -> @course := course as dummy -> from test1 -> order by course, score desc -> ) as x where x.row_number <= 2; +--------+--------+-------+ | name | course | score | +--------+--------+-------+ | 王五 | 数学 | 99 | | 张三 | 数学 | 77 | | 张三 | 英语 | 90 | | 王五 | 英语 | 89 | | 王五 | 语文 | 93 | | 李四 | 语文 | 90 | +--------+--------+-------+ 6 rows in set (0.00 sec) sqlserver oracle oracel
1
2
3
4
5
6
|
[sql] SELECT *
FROM ( SELECT ROW_NUMBER() OVER(PARTITION BY x ORDER BY y DESC ) rn,
test1.*
FROM test1)
WHERE rn = 1 ;
|
相关推荐
MySql实现分组取n条最大记录,还在为分组取最大困扰么?
mysql获取分组后每组的最大值实例详解 1. 测试数据库表如下: create table test ( `id` int not null auto_increment, `name` varchar(20) not null default '', `score` int not null default 0, primary key...
本文实例讲述了mysql使用GROUP BY分组实现取前N条记录的方法。分享给大家供大家参考,具体如下: MySQL中GROUP BY分组取前N条记录实现 mysql分组,取记录 GROUP BY之后如何取每组的前两位下面我来讲述mysql中GROUP BY...
主要介绍了详解MySQL分组排序求Top N的相关资料,需要的朋友可以参考下
一般来说,mysql获取group by内部可以获取到某字段的记录分组统计总数,而无法统计出分组的记录数。 mysql中可以使用SQL_CALC_FOUND_ROWS来获取查询的行数,在很多分页的程序中都这样写: 代码如下:SELECT COUNT(*) ...
Mysql取分组后的每组第一条数据 gruop by 分组后 进行 order by mysql会按照 先分组后排序的形式进行输出 并不能做到每组中的第一条数据取出。 我的思路是 : 先将要查询的数据表转换成已经排序的临时表 在进行 分组...
mysql获取当前时间
从MySQL获取ip,是一套非常适用的命令组成
–按某一字段分组取最大(小)值所在行的数据 代码如下: /* 数据如下: nameval memo a 2 a2(a的第二个值) a 1 a1–a的第一个值 a 3 a3:a的第三个值 b 1 b1–b的第一个值 b 3 b3:b的第三个值 b 2 b2b2b2b2 b 4 b4b4 b ...
思路:先随机排序然后再分组就好了。 1、创建表: CREATE TABLE `xdx_test` ( `id` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, `class` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=...
这是我自己学习mysql时的学习笔记,每一个知识点都是自己动手写的,涵盖了mysql中的几乎全部的基础知识点,查询,子查询,分组,排序,常用函数,多表连接,视图,变量,存储过程,函数,分支结构,循环结构等等
本文给大家分享的是使用mysql实现获取所有分类的前N条记录的方法,本文给出了3个示例,有需要的小伙伴可以参考下。
介绍Mysql如何获得结果集的行号的几种方法,类似oracle的Row_no,当然有一定的局限性。请朋友们自己体会,呵呵。
MYSQL注入获取权限 MYSQL注入获取权限 MYSQL注入获取权限
MySQL- 分组查询讲解
使用MySQL函数,实现获取汉字拼音首字母。在SQL语句中传入汉字返回对应的首字母
此Markdown文档提供了MySQL数据库中分组查询的DQL操作示例代码和说明。通过文档,您可以学习如何使用分组查询将数据按特定的列进行分组,并对每个分组进行统计计算。 示例代码演示了使用`GROUP BY`关键字将数据按照...