MySQL19-25:排序/分组查询

已于 2022-04-05 08:57:16 修改
阅读量219 收藏
点赞数
分类专栏： 锦到说MySQL 文章标签： 数据库
于 2021-03-04 21:04:05 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_43568982/article/details/114337321
版权

开始时间：2021-03-03

排序

在Java,Python,VBA中都有对应的排序。

mysql> select ename,sal from emp order by sal;
+--------+---------+
| ename  | sal     |
+--------+---------+
| SMITH  |  800.00 |
| JAMES  |  950.00 |
| ADAMS  | 1100.00 |
| WARD   | 1250.00 |
| MARTIN | 1250.00 |
| MILLER | 1300.00 |
| TURNER | 1500.00 |
| ALLEN  | 1600.00 |
| CLARK  | 2450.00 |
| BLAKE  | 2850.00 |
| JONES  | 2975.00 |
| FORD   | 3000.00 |
| SCOTT  | 3000.00 |
| KING   | 5000.00 |
+--------+---------+

默认是正序，要想指定，可以用asc(ascend)和desc(descend)

mysql> select ename,sal from emp order by sal asc;//升序
mysql> select ename,sal from emp order by sal desc;//降序

如果要指定次关键字排序，直接加在后面就行

mysql> select ename,sal from emp order by sal desc, ename asc;
+--------+---------+
| ename  | sal     |
+--------+---------+
| KING   | 5000.00 |
| FORD   | 3000.00 |
| SCOTT  | 3000.00 |
| JONES  | 2975.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| ALLEN  | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| MARTIN | 1250.00 |
| WARD   | 1250.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| SMITH  |  800.00 |
+--------+---------+

如果要列的字段很多，可以按字段所打印的顺序来排

mysql> select ename,sal from emp order by 2;//按sal来排
+--------+---------+
| ename  | sal     |
+--------+---------+
| SMITH  |  800.00 |
| JAMES  |  950.00 |
| ADAMS  | 1100.00 |
| WARD   | 1250.00 |
| MARTIN | 1250.00 |
| MILLER | 1300.00 |
| TURNER | 1500.00 |
| ALLEN  | 1600.00 |
| CLARK  | 2450.00 |
| BLAKE  | 2850.00 |
| JONES  | 2975.00 |
| FORD   | 3000.00 |
| SCOTT  | 3000.00 |
| KING   | 5000.00 |
+--------+---------+

找出工作岗位是SALESMAN的员工，并且要求按照薪资的降序排列。

mysql> select * from emp where job='salesman' order by sal desc;
+-------+--------+----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB      | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+----------+------+------------+---------+---------+--------+
|  7499 | ALLEN  | SALESMAN | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7521 | WARD   | SALESMAN | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
+-------+--------+----------+------+------------+---------+---------+--------+

分组函数

分组函数又叫 多行处理函数
输入多行，输出一行

count计数
sum求和
avg取平均
max求最大值
min最小值

找出工资总和

mysql> select sum(sal) from emp;
+----------+
| sum(sal) |
+----------+
| 29025.00 |
+----------+

同理：

mysql> select max(sal) from emp;
mysql> select min(sal) from emp;
mysql> select avg(sal) from emp;

分组函数自动忽略掉null，但不会忽略0,
这里和Excel处理数据有点类似
只要数学表达式中有null出现，最后结果都是null
比如800+null 那么结果就是null
但直接使用sum(comm)这种，是把null当做零的

mysql> select sum(comm) from emp;
+-----------+
| sum(comm) |
+-----------+
|   2200.00 |
+-----------+
1 row in set (0.00 sec)

mysql> select avg(comm) from emp;//一共有十四行数据
+------------+
| avg(comm)  |
+------------+
| 550.000000 |
+------------+

ifnull处理函数

单行处理函数：输入一行，输出一行
ifnull(可能是null的数据,被当做什么)
例如

mysql> select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+

mysql> select *,ifnull(comm,0) from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+----------------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO | ifnull(comm,0) |
+-------+--------+-----------+------+------------+---------+---------+--------+----------------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |           0.00 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |         300.00 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |         500.00 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |           0.00 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |        1400.00 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |           0.00 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |           0.00 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |           0.00 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |           0.00 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |           0.00 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |           0.00 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |           0.00 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |           0.00 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |           0.00 |

计算年薪

mysql> select ename,(sal+ifnull(comm,0))*12 as yearsal from emp;
+--------+----------+
| ename  | yearsal  |
+--------+----------+
| SMITH  |  9600.00 |
| ALLEN  | 22800.00 |
| WARD   | 21000.00 |
| JONES  | 35700.00 |
| MARTIN | 31800.00 |
| BLAKE  | 34200.00 |
| CLARK  | 29400.00 |
| SCOTT  | 36000.00 |
| KING   | 60000.00 |
| TURNER | 18000.00 |
| ADAMS  | 13200.00 |
| JAMES  | 11400.00 |
| FORD   | 36000.00 |
| MILLER | 15600.00 |
+--------+----------+

找出工资大于平均薪资的员工
我自己尝试写的

mysql> select * from emp where sal>avg(sal);
ERROR 1111 (HY000): Invalid use of group function

报错了，聚合函数不能这样用

where后面不能跟分组函数
所以解决办法是，嵌套执行

mysql> select * from emp where sal>(select avg(sal) from emp);
+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7566 | JONES | MANAGER   | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  7698 | BLAKE | MANAGER   | 7839 | 1981-05-01 | 2850.00 | NULL |     30 |
|  7782 | CLARK | MANAGER   | 7839 | 1981-06-09 | 2450.00 | NULL |     10 |
|  7788 | SCOTT | ANALYST   | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
|  7902 | FORD  | ANALYST   | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
+-------+-------+-----------+------+------------+---------+------+--------+

count(星号*)和count(具体的某个字段)，他们有什么区别?
count(星号):不是统计某个字段中数据的个数，而是统计总记录条数。（和某个字段无关)
count(comm) :表示统计comm字段中不为NULL的数据总数量。

分组查询 group by/having

例如：找出每个工作岗位的最高薪资
分组函数常常和group by联用

mysql> select max(sal),job from emp group by job;
+----------+-----------+
| max(sal) | job       |
+----------+-----------+
|  3000.00 | ANALYST   |
|  1300.00 | CLERK     |
|  2975.00 | MANAGER   |
|  5000.00 | PRESIDENT |
|  1600.00 | SALESMAN  |
+----------+-----------+

当一条语句中有group by的话，select后面只能跟分组函数和参与分组的字段，如果有其他的，显示的也是错误的。

练习：
找出每个部门不同工作岗位的最高薪资
原始数据应该如下

mysql> select deptno,job,sal from emp order by deptno;
+--------+-----------+---------+
| deptno | job       | sal     |
+--------+-----------+---------+
|     10 | CLERK     | 1300.00 |
|     10 | PRESIDENT | 5000.00 |
|     10 | MANAGER   | 2450.00 |
|     20 | ANALYST   | 3000.00 |
|     20 | CLERK     | 1100.00 |
|     20 | ANALYST   | 3000.00 |
|     20 | MANAGER   | 2975.00 |
|     20 | CLERK     |  800.00 |
|     30 | MANAGER   | 2850.00 |
|     30 | SALESMAN  | 1250.00 |
|     30 | SALESMAN  | 1500.00 |
|     30 | SALESMAN  | 1250.00 |
|     30 | CLERK     |  950.00 |
|     30 | SALESMAN  | 1600.00 |
+--------+-----------+---------+

分组的关键字有俩，这俩关键字也一定能做表头，多个字段作为group by后面跟的

mysql> select deptno,job,max(sal) from emp group by deptno,job;
+--------+-----------+----------+
| deptno | job       | max(sal) |
+--------+-----------+----------+
|     10 | CLERK     |  1300.00 |
|     10 | MANAGER   |  2450.00 |
|     10 | PRESIDENT |  5000.00 |
|     20 | ANALYST   |  3000.00 |
|     20 | CLERK     |  1100.00 |
|     20 | MANAGER   |  2975.00 |
|     30 | CLERK     |   950.00 |
|     30 | MANAGER   |  2850.00 |
|     30 | SALESMAN  |  1600.00 |
+--------+-----------+----------+

练习：找出每个部门的最高薪资，显示薪资大于2900的数据
分步骤来
先写出每个部门的最高薪资

mysql> select deptno,max(sal) from emp group by deptno;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
|     10 |  5000.00 |
|     20 |  3000.00 |
|     30 |  2850.00 |
+--------+----------+

再单独写出薪资大于2900的数据
方法1：
加having

mysql> select deptno,max(sal) from emp group by deptno having max(sal)>2900;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
|     10 |  5000.00 |
|     20 |  3000.00 |
+--------+----------+

这样效率低，应该一开始就过滤掉2900以下的
使用where

mysql> select deptno,max(sal) from emp where sal>2900 group by deptno;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
|     10 |  5000.00 |
|     20 |  3000.00 |
+--------+----------+

但如果题目改为：
找出每个部门的平均薪资，显示薪资大于2000的数据

mysql> select deptno,avg(sal) from emp group by deptno having (avg(sal)>2000);
+--------+-------------+
| deptno | avg(sal)    |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
+--------+-------------+

总结完整DQL语句的书写
select … from … where…group by …having …order by …

先from查询表
经过where筛选
再经过group by分组
进一步筛选用having
查出来select
最后排序输出order by

高开低走。
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 2
    评论
专栏目录
ORM框架-VB/C#.Net实体代码生成工具(EntitysCodeGenerate)ECG4.3.pdf
04-11
(5)、Group By分组条件及排序 12 (6)、结合事务处理的功能 14 3、Delete删除 15 4、Update更新 15 5、Insert插入 16 2.2.4 DbCore+SQL/存储过程 17 1、DbCore+SQL 17 2、DbCore+存储过程 19 2.2.5 Extend辅助扩展...
mysql 分组查询排序实现 & 排名（全）
热门推荐
pscool的博客
11-06 1万+
参考：https://blog.csdn.net/a364901254/article/details/104382012 MySQL5.7版本没有row_number()over()函数，MySQL8以上版本才有 1、mysql分组取每组前几条记录(排序) 2、mysql 实现row_number() over(partition by ) 分组排序功能 3、mysql 中关于获取行号@rownum：=@rownum+1 创建表和打入数据 --创建表 CREATE TABLE `a` ( `ID`
MySQL数据排序与分组（详解）
大佬云集技术答疑交流群：743262921(进群暗号:222)
11-16 1万+
MySQL数据排序与分组 一、数据排序：order by 用法：select 字段列表 from 表名 [where…] order by 字段名; order by 默认按字段数据的升序排列 降序：desc （放在字段名之后） 升序：asc 如： select * from xxb1 order by 学号; select * from xxb1 where 学号>=2 order by 学号 desc; 1、order by 放在select最后 2、order by可用别名 如： sele
mysql分组查询排序问题
weixin_41011482的博客
04-21 522
这数据明显是不对的，id为 1 对应的时间应该是 2023-02-21 10:00:00。group by分组之后，用max函数的话，除了分组字段，其他字段依旧取的第一条。多个地方查阅之后，依旧没有解决这个查询逻辑问题，好多博客都是写的这个max的函数。想要查询每天时间最大的一条数据，按照分组并且排序的sql应该是这样写的。最后决定用java流来处理这个查询逻辑问题。有这样一张表 tableName: T。查询出来的数据是长这样。
【③MySQL 数据查询】:提高查询的效率
热爱编程的小K的博客
06-12 4759
【MySQL 数据查询】: 基本、分组、排序、聚合、分页、条件查询详解
mysql5.7分组排序
gdssgxf的博客
06-16 2821
mysql分组排序
MySQL数据库零基础到精通视频教程
05-19
14-分组查询与分页查询 15-作业讲解与时间格式函数 16-多表查询一 17-多表查询二 18-联合查询 19-作业讲解与模糊查询 20-外连接查询 21-数据库表设计一 22-数据库表设计二 23-视图 24-数据库建模一(PowerDesigner) ...
mysql基础入门课程
08-30
14. 基础-SQL-DQL-分组查询 15. 基础-SQL-DQL-排序查询 16. 基础-SQL-DQL-分页查询 17. 基础-SQL-DQL-执行顺序 18. 基础-SQL-DCL-用户管理- 19. 基础-SQL-DCL-权限控制- 20. 基础-函数-字符串函数 21. 基础-函数-...
b站动力节点2020版mysql笔记day01
04-06
1、什么是数据库？什么是数据库管理系统？什么是SQL？他们之间的关系是什么？ 2、安装MySQL数据库管理系统步骤。 3、MySQL数据库的完美卸载！...19、分组查询（非常重要：五颗星*****） 20、大总结（单表的查询学完了）
Pytorch学习（二）使用 torchvision
weixin_42835514的博客
10-01 1303
Pytorch学习（二）准备数据集torchvision三级目录 准备数据集 在训练神经网络前，必须有数据。可以使用以下几个数据提供源。 准备图片数据集 一、CIFAR-10CIFAR-10 二、ImageNetImageNet 三、ImageFolderImageFolder 四、LSUN ClassificationLSUN Classification 五、COCO (Captioning and Detection)COCO torchvision 为了方便加载以上五种数据库的数据，torchvis
MySQL分组查询--GROUP BY及排序
ljabcdefg111的博客
07-20 2467
-查询各岗位中工资在1000-3000之间的最高工资和最低工资，要求最高工资不等于3000。--查询各部门中名字中包含E的员工人数，要求人数超过1个，只显示部门编号、人数。--查询公司所有的员工，按工资升序排列，工资相同的按奖金降序排列。--查询每个部门的人数，要求只显示人数大于3人的数据。--查询每个部门中有多少人，要求列出部门编号和人数。--查询每个部门中每个岗位的人数和最高工资。--查询每个部门中，名字包含有E的员工人数。--查询公司所有的员工，按工资升序排列。......
mysql 分组、排序
ernesto的博客
07-12 640
mysql 分组、排序
MySQL如何实现分组排名，找出top-n
最新发布
qq_45509892的博客
09-07 1548
MySQL实现分组排名求出TOPN
【MySQL | 基础】查询语句（简单、条件、排序、聚合、分组、限制）
翼同学
04-09 1450
有关查询语句的总结。具体有：简单查询，条件查询，排序查询，聚合查询，分组查询，限制查询。
MySQL分组查询并排序
qq_41942094的博客
12-18 1331
总结：会出现多个 结果集，但是已经是排序好的，再用程序处理保留最新的时间数据即可。主要是熟悉GROUP_CONCAT函数用法。场景：获取分销商所有订单数并显示最近订单时间。1、按照分销商分组统计订单数量。2、在分组中按订单时间排序。
MySQL 查询结果倒叙后分组（先order by，再按order by的结果group by）
Alice_joy的博客
07-14 4062
MySQL 查询结果倒叙后分组（先order by，再按order by的结果group by）
mysql倒序查询
m0_67393413的博客
03-28 1万+
通过 ”order by“实现，语法 ：order by 字段 asc/desc。 sql:select * from tablename order by filename1 desc; 解释：上面语句的意思就是根据”filename1字段“排序，倒叙输出tablename表中的数据。 备注：asc是表示升序，desc表示降序。 ...
MySQL分组后排序
Hu_1297的博客
03-28 1万+
文章目录前言问题一、使用group by二、使用窗口函数三、窗口函数和group by总结 前言 之前有遇到需要求分组后排序的问题，记录记录 问题 求公司各部门内工资最高的人 一、使用group by 实例代码： select a.dept_no,a.name,a.salary from ( select dept_no,name,salary from employee order by salary desc limit 1000) a group by a.dept_no 必须要使用l
mysql数据库查询所有语句命令
08-05
以下是MySQL数据库查询所有语句命令： 1. SELECT：用于从数据库中检索数据。 2. FROM：用于指定要检索数据的表。 3. WHERE：用于筛选数据。 4. GROUP BY：用于将数据分组。 5. HAVING：用于筛选分组后的数据。 ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 2
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值