MySQL19-25:排序/分组查询

已于 2022-04-05 08:57:16 修改
阅读量245 收藏
点赞数
分类专栏： 锦到说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

Python常用组件、命令大总结(持续更新)
LDC，公众号【轻松学编程】
03-12 1万+
Python后端开发常用组件、命令（干货） 持续更新中… 1、生成6位数字随机验证码 import random import string def num_code(length=6): """ 生成长度为length的数字随机验证码 :param length: 验证码长度 :return: 验证码 """ return ''.jo.....................
数据库-根据某个字段去重/分组并查询最新的一条记录
iwlnner的博客
05-27 5230
今天在工作中，遇到一个业务：根据某个字段去重查询最新的记录列表，于是建了一张测试表(以下使用postgreSQL建表语句)，并记录下三种不同类型数据库下的查询方式（oracle/postgreSQL/mysql） DROP TABLE IF EXISTS "public"."t_group_member"; CREATE TABLE "public"."t_group_member" ( "id" int4 NOT NULL, "group_id" varchar(255) COLLATE "pg_
MySQL之聚合查询、分组和排序
10-24
附件包含的主要内容为演示MySQL之聚合查询、分组和排序的相关命令和效果。
MYSQL分组排序查询
qq_48550824的博客
12-17 355
详情见： mysql特殊查询----分组后排序
Mysql基础（二）分组查询、排序查询、分页查询
前端笔记——放弃很容易,但坚持一定很酷
11-20 452
版权申明：本文用于个人学习记录，学习课程为黑马程序员的mysql教程。如需获取官方的学习视频和文档资料，请至黑马程序员官方获取。下面附上教学视频的链接地址，向提供免费教学视频的老师致敬，学如逆水行舟，不进则退，共勉。
mysql分组排序查询
zhangt85的专栏
02-19 825
查询需求：查找用户时间最晚的一条记录 SELECT temp.*, FROM (SELECT * FROM table ORDER BY date DESC) temp GROUP BY user_id 查询需求：查找用户时间最晚的两条记录 SELECT t1.* FROM table t1 WHERE (SELECT COUNT(1) FROM table t2 WHERE
mysql分组查询排序问题
weixin_41011482的博客
04-21 553
这数据明显是不对的，id为 1 对应的时间应该是 2023-02-21 10:00:00。group by分组之后，用max函数的话，除了分组字段，其他字段依旧取的第一条。多个地方查阅之后，依旧没有解决这个查询逻辑问题，好多博客都是写的这个max的函数。想要查询每天时间最大的一条数据，按照分组并且排序的sql应该是这样写的。最后决定用java流来处理这个查询逻辑问题。有这样一张表 tableName: T。查询出来的数据是长这样。
玩转Mysql系列 - 第9篇：分组查询详解（group by & having）
CSDN_KONGlX的博客
04-23 773
打算提升sql技能的，可以加我微信itsoku，带你成为sql高手。 这是Mysql系列第9篇。 环境：mysql5.7.25，cmd命令中进行演示。 本篇内容 分组查询语法 聚合函数 单字段分组 多字段分组 分组前筛选数据 分组后筛选数据 where和havin...
MySQL高级
小小猿的博客
07-22 702
MySQL高级 1 Linux环境下MySQL的搭建 1.1 MySQL下载 MySQL5.7下载地址 1.2 安装MySQL 卸载 centos 中预安装的 mysql rpm -qa | grep -i mysql rpm -e mysql-xxx --nodeps centos7.6自带的类mysql数据库是mariadb，会跟mysql冲突，要先删除 rpm -qa|grep mari rpm -e --nodeps mariadb-libs 在mysql的安装文件目录下执行：
03 MySQL进阶
巅峰产生虚伪的拥护，黄昏见证虞城的信徒。
11-05 345
mysql进阶1. 二进制格式mysql安装2. mysql配置文件3. mysql数据库备份与恢复3.1 数据库常用备份方案3.2 mysql备份工具mysqldump 1. 二进制格式mysql安装 //下载二进制格式的mysql软件包 [root@localhost ~]# cd /usr/src/ [root@localhost src]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.34-linux-glibc
MySQL分组查询并排序
qq_41942094的博客
12-18 1740
总结：会出现多个 结果集，但是已经是排序好的，再用程序处理保留最新的时间数据即可。主要是熟悉GROUP_CONCAT函数用法。场景：获取分销商所有订单数并显示最近订单时间。1、按照分销商分组统计订单数量。2、在分组中按订单时间排序。
mysql分组排序
阳水平的博客
11-08 978
分组排序：mysql表格：需求是把不同的name和其对应的id找出来：方法一：对于低于4.2的版本采用以下方法，弊端是结果多了个不需要的字段。select *, count(distinct name) from student group by name ;方法二：完美解决select id,group_concat(distinct name) from student group by nam
mysql 分组、排序
ernesto的博客
07-12 744
mysql 分组、排序
mysql的排序/分组/索引
最新发布
weixin_45635107的博客
05-07 255
综上所述，通过正确使用排序、分组和索引，可以有效地优化 MySQL 查询的性能。MySQL中的排序、分组和索引是优化查询性能的重要工具。
mysql 分组查询排序实现 & 排名（全）
热门推荐
pscool的博客
11-06 2万+
参考：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`
SQL获取分组数据中max/first/least列的方法（转载备份）
水果蔬菜拼盘，大象与跳蚤共舞
07-29 434
这几天在做一个和oracle相关的项目的时候，遇到一个问题，我需要一次性查出一个表里每一个分组的最新的一条记录，于是想到了group by，处理后，可以获得每个分组最新的记录的时间，但是下一步卡住了，后来在网上查到了一个相关的解决方案，作为技术备份，先记录在这里。 原始链接：http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstl...
mySQL（DQL排序查询和分组查询）
qingmenghan的博客
10-16 137
目录 一.排序查询 二. 常见函数 2.1 字符函数 2.2 数学函数 2.3 日期函数 2.4 流程控制函数 2.5 其他函数 三. 分组函数 3.1 函数 3.2 特点 3.3 注意 四.分组查询 4.1 语法 4.2 特点 4.3 面试相关 一.排序查询 * 语法： select 要查询的东西 from 表 where 条件 order by 排序的字段|表...
mysql分组排列的查询语句_MySQL查询语句详解，排序、分组、聚合函数、约束
weixin_33487490的博客
01-19 240
create database day20;查询的时候from前面的字段是需要显示出来的内容，后面是条件use day20;create table phones(id int,pinpai varchar(20),xinghao varchar(20),jiage double,miaoshu varchar(200));insert into phones values (1,'apple',...
【0基础学习mysql】之DQL-聚合函数、分组查询及排序查询
m0_67388084的博客
10-07 734
mysql的数据查询语言的聚合函数、分组查询及排序查询（建议收藏，复习时反复观看）
MySql常用SQL语句集合：25条高效查询技巧
资源摘要信息: "MySQL常用SQL语句汇总，共25条" 本资源详细地汇总了25条常用的MySQL SQL语句，这不仅对于数据库初学者，也对有一定经验的后端程序员都极为有用。内容的整理旨在帮助开发者在日常的数据库操作中能够...
评论 2
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值