`
sangei
  • 浏览: 336360 次
  • 性别: Icon_minigender_1
  • 来自: 西安
社区版块
存档分类
最新评论

MySQL获取分组后的TOP 1和TOP N记录

阅读更多

有时会碰到一些需求,查询分组后的最大值,最小值所在的整行记录或者分组后的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  ;
 
 
 
 
分享到:
评论

相关推荐

    sql基本语句30条

    **解释**:这些语句分别用于在Access、SQL Server和MySQL中随机选取前n条记录。 ### 8. 查询超过五分钟未完成的任务 **语法示例**: ```sql SELECT * FROM &lt;table_name&gt; WHERE DATEDIFF(MINUTE, start_time, ...

    MySQL命令大全

    mysql&gt;insert into MyClass values(1,’Tom’,96.45),(2,’Joan’,82.99), (2,’Wang’, 96.59); 5、查询表中的数据 1)、查询所有行 命令:select &lt;字段,字段,...&gt; from 表名 &gt; where 表达式 &gt; 例如:查看表 ...

    mysqlSQL语法[文].pdf

    - `TOP` 在某些SQL方言中(如SQL Server),用于限制返回的记录数,但在MySQL中,通常使用 `LIMIT`,如:`SELECT * FROM table_name LIMIT n` 获取前n条记录。 这些是MySQL SQL语法的基本知识点,掌握了它们,你...

    数据库经典常用查询语句

    5. **HAVING子句**:在GROUP BY后过滤分组。 ```sql SELECT 列名, COUNT(*) FROM 表名 GROUP BY 列名 HAVING 条件; ``` 它与WHERE不同,WHERE过滤单行记录,而HAVING过滤分组。 6. **JOIN操作**:用于合并来自...

    SQL_select.rar_sql 语法

    4. **GROUP BY和HAVING子句**:GROUP BY用于将数据分组,HAVING用于过滤分组后的数据: ``` SELECT column1, COUNT(column2) FROM table_name GROUP BY column1 HAVING COUNT(column2) &gt; n; ``` 5. **JOIN...

    MSSQL 分页

    1. **TOP 和 OFFSET/FETCH** MSSQL 2012及更高版本引入了OFFSET/FETCH语句,这是官方推荐的分页方法。`TOP`关键字用于获取查询结果的前n行,而`OFFSET`则用来跳过指定数量的行。`FETCH NEXT`结合`TOP`和`OFFSET`,...

    数据库基础知识(经典的常用T-sql语句)

    在SQL Server中,处理TOP N问题时,可以使用`TOP N`配合`ORDER BY`子句来获取排名前N的记录。在DB2中,还有`RANK()`、`ROW_NUMBER()`和`DENSE_RANK()`等OLAP函数,它们提供了更灵活的排序和分组方式。 总的来说,...

    MySql数据库中Select用法小结

    4. **按百分比筛选**:使用TOP N PERCENT返回前N%的记录,例如: ```sql SELECT TOP 30 PERCENT * FROM table; ``` 5. **排序**:使用ORDER BY进行升序(ASC)或降序(DESC)排序,例如: ```sql SELECT * ...

    SQL语言参考大全(CHM版)

    - `TOP`(在某些数据库系统中):返回结果集的前N条记录。 6. **DCL语句** - `GRANT`:赋予用户访问数据库的权限。 - `REVOKE`:撤销用户的权限。 7. **TCL语句** - `COMMIT`:提交事务,保存所有更改。 - `...

    常用SQL 语句大全

    14. **前10条记录**: 使用`TOP 10`获取前10条记录。 15. **选择每组中的最大数**: 结合`GROUP BY`和聚合函数`MAX()`,如 `SELECT Department, MAX(Salary) FROM Employees GROUP BY Department;` **第三部分、技巧...

    sqlite数据库操作指令

    例如,在 SQL Server 中,你可以使用 `ORDER BY NEWID()`,而在 MySQL 中则是 `ORDER BY RAND() LIMIT n`。 9. 提前提醒: 在 SQL 查询中,你可以使用 `DATEDIFF` 函数结合当前时间来实现提前提醒。例如,提前5...

    计算机SQL数据查询11.pdf

    11. **TOP N查询**:用于选取结果集的前N条记录,如查询身高或成绩最高的前3名学生。 12. **分组与计算**:`GROUP BY`用于按照一个或多个字段进行分组,`AVG()`计算平均值,`COUNT()`计算数量。如统计男女学生人数...

    程序员的SQL金典.pdf 高清 下载

    - **4.3.2 数据分组与聚合函数**:结合GROUP BY子句使用聚合函数对分组后的数据进行统计。 - **4.3.3 HAVING语句**:类似于WHERE子句,但用于过滤GROUP BY的结果。 **4.4 限制结果集行数** - **4.4.1 MySQL**:...

    经典SQL脚本大全

    │ │ 7.2.1 TOP n 实现的通用分页存储过程.sql │ │ 7.2.2 字符串缓存实现的通用分页存储过程.sql │ │ 7.2.3 临时表缓存实现的通用分页存储过程.sql │ │ 7.2.4 使用系统存储过程实现的通用分页存储过程.sql │...

    Sqlserver2000经典脚本

    限制列数的交叉表.sql │ ├─第07章 │ │ 7.1 splitpage.asp │ │ 7.2.1 TOP n 实现的通用分页存储过程.sql │ │ 7.2.2 字符串缓存实现的通用分页存储过程.sql │ │ 7.2.3 临时表...

Global site tag (gtag.js) - Google Analytics