mysql group by 三天_Mysql第三天(高级查询、group by、having、子查询、单行(多行)运算符)...

最新推荐文章于 2025-06-26 20:21:45 发布

小猪舔阳

最新推荐文章于 2025-06-26 20:21:45 发布

阅读量438

点赞数

CC 4.0 BY-SA版权

文章标签： mysql group by 三天

本文链接：https://blog.csdn.net/weixin_33640562/article/details/114750287

本文详细介绍了SQL中的分组函数如COUNT、MIN、MAX、SUM、AVG的使用方法，以及如何利用GROUP BY子句进行数据分组。此外，还深入探讨了子查询的应用场景，包括单行子查询、多行子查询等高级技巧。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

高级查询

常用的分组函数

COUNT

MIN

MAX

SUM

AVG

分组函数的语法:

SELECT [column,] group_function(column)

FROM table

[WHERE condition]

[GROUP BY column]

[HAVING group_function(column)expression

[ORDER BY column | group_function(column)expression];

COUNT

COUNT( [DISTINCT | ALL] column | expression)

COUNT( [DISTINCT | ALL] column | expression)：返回满足条件的非空(NULL)行的数量

select COUNT(COMM) from emp;

结果 4

使用DISTINCT关键字

select COUNT(DISTINCT DEPTNO) from emp;

结果 3

查询部门30有多少个员工领取奖金

select COUNT(COMM) from emp WHERE DEPTNO = 30;

MIN函数和MAX函数

MIN和MAX函数主要是返回每组的最小值和最大值。

MIN([DISTINCT | ALL] column | expression)

MAX([DISTINCT | ALL] column | expression)

MIN和MAX可以用于任何数据类型

查询入职日期最早和最晚的日期:

SELECT

MIN(hiredate),

MAX(hiredate)

FROM

emp;

SUM函数和AVG函数

SUM和AVG函数分别返回每组的总和及平均值。

SUM([DISTINCT | ALL] column | expression)

AVG([DISTINCT | ALL] column | expression)

SUM和AVG函数都是只能够对数值类型的列或表达式操作。

查询职位以SALES开头的所有员工平均工资、最低工资、最高工资、工资和。

SELECT

AVG(SAL) 平均工资,

MIN(SAL) 最低工资,

MAX(SAL) 最高工资,

SUM(SAL) 工资和

FROM

emp

WHERE

job LIKE 'SALES%'

IFNULL函数

IFNULL 函数可以使分组函数强制包含含有空值的记录

SELECT AVG(IFNULL(comm,0))

FROM emp;

所有员工的平均奖金

SELECT

avg(IFNULL(comm, 0))

FROM

emp;

有奖金的平均奖金

SELECT

AVG(COMM)

FROM

emp;

练习：

查询部门20的员工，每个月的工资总和及平均工资。

SELECT

AVG(sal) 平均工资,

SUM(sal) 工资和

FROM

emp

WHERE

deptno = 20;

查询工作在CHICAGO的员工人数，最高工资及最低工资。

SELECT

COUNT(m.empno) 员工人数,

MAX(sal) 最高工资,

MIN(sal) 最低工资

FROM

emp m,

dept d

WHERE

m.deptno = d.DEPTNO

AND d.loc = 'CHICAGO';

查询员工表中一共有几种岗位类型

SELECT

COUNT(DISTINCT JOB)

FROM

emp;

group by 语句

通过 GROUP BY 子句可将表中满足WHERE条件的记录按照指定的列划分成若干个小组，其中GROUP BY子句指定要分组的列

查询每个部门的部门编号和平均工资

SELECT

deptno,

avg(sal)

FROM

emp

GROUP BY

deptno;

6a7857a9198e

查询每个部门的部门编号，每个部门的最低工资

查询每个部门的部门编号，每个部门的最高工资

select deptno, min(sal) from emp group by deptno;

select deptno, max(sal) from emp group by deptno;

使用规则:

在SELECT列表中除了分组函数那些项，所有列都必须包含在GROUP BY 子句中。

select ename, deptno, avg(sal) from emp group by deptno, ename;

GROUP BY 所指定的列并不是必须出现在SELECT 列表中

select ename, avg(sal) from emp group by deptno, ename;

按多个列分组

查询每个部门每个岗位的工资总和

select deptno, job, sum(sal) from emp group by deptno, job;

练习：

查询每个部门的部门编号，部门名称，部门人数，最高工资，最低工资，工资总和，平均工资。

SELECT

d.DEPTNO 部门编号,

d.DNAME 部门名称,

COUNT(*) 部门人数,

MAX(e.SAL) 最高工资,

MIN(e.SAL) 最低工资,

SUM(e.SAL) 工资总和,

AVG(e.SAL) 平均工资

FROM

emp e,

dept d

WHERE

e.DEPTNO = d.DEPTNO

GROUP BY

e.DEPTNO, d.DNAME;

查询每个部门，每个岗位的部门编号，部门名称，岗位名称，部门人数，最高工资，最低工资，工资总和，平均工资。

SELECT

d.DEPTNO 部门编号,

d.DNAME 部门名称,

e.JOB 岗位名称,

COUNT(*) 部门人数,

MAX(e.SAL) 最高工资,

MIN(e.SAL) 最低工资,

SUM(e.SAL) 工资总和,

AVG(e.SAL) 平均工资

FROM

emp e,

dept d

WHERE

e.DEPTNO = d.DEPTNO

GROUP BY

d.DEPTNO,d.DNAME, e.JOB;

查询每个经理所管理的人数，经理编号，经理姓名，要求包括没有经理的人员信息。

SELECT

COUNT(*) 管理人数,

m.EMPNO 经理编号,

m.ENAME 经理姓名

FROM

emp w

LEFT OUTER JOIN emp m ON w.MGR = m.EMPNO

GROUP BY

m.EMPNO,

m.ENAME

分组后的筛选

使用WHERE子句中限制分组会失败，应该使用having

每个组内最高薪水大于3000时候输出

SELECT

e.DEPTNO,

MAX(e.sal)

FROM

emp e

WHERE

max(e.SAL) > 3000

GROUP BY

e.DEPTNO

6a7857a9198e

Having

使用步骤：

记录已经分组.

使用过组函数.

与 HAVING 子句匹配的结果才输出

SELECT column, group_function

FROM table

[WHERE condition]

[GROUP BY group_by_expression]

[HAVING group_condition]

[ORDER BY column];

举个例子：

查询每个部门最高薪水大于3000的部门编号，最高工资

SELECT

e.DEPTNO,

MAX(e.sal)

FROM

emp e

GROUP BY

e.DEPTNO

HAVING

MAX(e.SAL) > 2900

6a7857a9198e

查询每个工种，工资总和，并且工作不是以SALE开头的且每个工种的工资和大于5000时输出，并按照工资和进行升序排序。

SELECT

JOB,

SUM(sal)

FROM

emp

GROUP BY

job

HAVING

job NOT LIKE 'SALE%'

AND SUM(sal) > 5000

ORDER BY

SUM(sal)

SELECT语句执行过程

SELECT语句执行过程：

1.通过FROM子句中找到需要查询的表；

2.通过WHERE子句进行非分组函数筛选判断；

3.通过GROUP BY子句完成分组操作；

4.通过HAVING子句完成组函数筛选判断；

5.通过SELECT子句选择显示的列或表达式及组函数；

6.通过ORDER BY子句进行排序操作。

举个例子

1.查询部门人数大于2的部门编号，部门名称，部门人数。

select e.deptno 部门编号,d.dname 部门名称,count(e.empno) 部门人数 from emp e ,dept d

where e.deptno = d.deptno group by d.dname ,e.deptno having count(e.empno) > 2;

查询部门平均工资大于2000，且人数大于2的部门编号，部门名称，部门人数，部门平均工资，并按照部门人数升序排序。

select e.deptno,d.dname,count(e.deptno),avg(e.sal) from emp e ,dept d

where e.deptno = d.deptno group by e.deptno,d.dname

having avg(e.sal) > 2000 and count(e.deptno) >2 order by count(e.deptno);

子查询

查询哪个员工的工资比JONES的高

select * from emp e where e.sal >(select e.sal from emp e where e.ename = 'JONES');

6a7857a9198e

查询工资最低的员工

select e.ename from emp e where e.sal = (select min(e.sal) from emp e );

语法：

SELECT select_list

FROM table

WHERE expr operator

(SELECT select_list

FROM table);

括号内的查询叫做子查询，也叫内部查询，先于主查询执行。

子查询的结果被主查询(外部查询)使用

expr operator包括比较运算符

单行运算符：>、=、>=、、<=

多行运算符： IN、ANY、ALL

子查询可以嵌于以下SQL子句中：

WHERE子句

HAVING子句

FROM子句

子查询类型

6a7857a9198e

子查询使用指导：

子查询要用括号括起来

将子查询放在比较运算符的右边

对于单行子查询要使用单行运算符

对于多行子查询要使用多行运算符

单行子查询(>、=、>=、、<=)

显示和工号7369从事相同工作并且工资大于7876的员工姓名和工作

select e.ename,e.job from emp e where

e.job = (select e.job from emp e where e.empno = 7369) and

e.sal > (select e.sal from emp e where e.empno = 7876);

查询部门最低工资比20部门最低工资高的部门编号及最低工资

SELECT e.DEPTNO 部门编号 ,MIN(e.SAL)

from emp e

GROUP BY e.DEPTNO

HAVING

MIN(e.SAL) > (SELECT MIN(sal) from emp WHERE DEPTNO = 20)

查询入职日期最早的员工姓名，入职日期

select e.ename , e.hiredate from emp e where

e.hiredate = (select min(e.hiredate) from emp e);

查询工资比SMITH工资高并且工作地点在CHICAGO的员工姓名，工资，部门名称

select e.ename , e.sal,d.dname from emp e,dept d where

e.deptno = d.deptno and e.sal >

(select e.sal from emp e where e.ename = 'SMITH') and

d.loc = (select d.loc from dept d where d.loc = 'CHICAGO');

查询入职日期比20部门入职日期最早的员工还要早的员工姓名，入职日期

select e.ename,e.hiredate from emp e

where e.hiredate >

(select min(e.hiredate) from emp e where e.deptno = 20);

多行子查询

多行子查询进行比较时，需要使用多行操作符，多行操作符包括：

IN 判断是否与子查询的任意一个返回值相同。

SELECT ENAME, SAL

FROM emp

WHERE EMPNO in (SELECT m.mgr FROM emp m);

ANY

有一个满足就可以

查询是经理的员工姓名和工资

SELECT ENAME, SAL

FROM emp

WHERE EMPNO = ANY(SELECT m.mgr FROM emp m);

查询部门编号不为10，且工资比10部门任意一名员工工资高的员工编号，姓名，职位，工资

SELECT EMPNO, ENAME,JOB, SAL

FROM emp

WHERE SAL >ANY(SELECT sal from emp WHERE DEPTNO = 10) AND DEPTNO <> 10;

ALL

每一个都要满足

查询部门编号不为20，且工资比20部门所有员工工资高的员工编号，姓名，职位，工资。

SELECT EMPNO, ENAME,JOB, SAL

FROM emp

WHERE SAL >ALL(SELECT sal from emp WHERE DEPTNO = 20) AND DEPTNO <> 20;

查询部门编号不为10，且工资比10部门所有员工工资低的员工编号，姓名，职位，工资。

SELECT EMPNO, ENAME,JOB, SAL

FROM emp

WHERE SAL < ALL(SELECT sal from emp WHERE DEPTNO = 10) AND DEPTNO <> 10;

子查询中的空值

查询不是经理的员工姓名

SELECT ename FROM emp WHERE EMPNO not in (SELECT mgr from emp);

子查询返回的结果中含有空值

上面的SQL语句试图查找出没有下属的雇员，逻辑上，这个SQL语句应该会返回8条记录，但是却一条也没返回，why?

因为子查询的结果中有一条空值，这条空值导致主查询没有记录返回。这是因为所有的条件和空值比较结果都是空值。因此无论什么时候只要空值有可能成为子查询结果集合中的一部分，就不能使用NOT IN 运算符

正确的查询不是经理的员工姓名

SELECT

ename

FROM

emp

WHERE

empno NOT IN (SELECT mgr FROM emp WHERE MGR is NOT NULL);

6a7857a9198e

FROM 子句中使用子查询

查询比自己部门平均工资高的员工姓名，工资，部门编号，部门平均工资

SELECT e.ename 员工姓名, e.SAL 工资, e.DEPTNO 部门编号, a.avgsal 部门平均工资

FROM emp e, (SELECT DEPTNO, AVG(SAL) avgsal from emp GROUP BY DEPTNO) a

WHERE

e.DEPTNO = a.DEPTNO AND e.SAL > a.avgsal;

查询入职日期比10部门任意一个员工晚的员工姓名、入职日期，不包括10部门员工

select ename,hiredate from emp where deptno <> 10 and hiredate >

any(select hiredate from emp where deptno = 10);

6a7857a9198e

查询入职日期比10部门所有员工晚的员工姓名、入职日期，不包括10部门员工

select ename,hiredate from emp where deptno <> 10 and

hiredate > all(select hiredate from emp where deptno = 10);

6a7857a9198e

查询职位和10部门任意一个员工职位相同的员工姓名，职位，不包括10部门员工

select ename,job from emp where deptno <> 10 and

job = any(select job from emp where deptno = 10);

6a7857a9198e

查询部门平均工资在2500元以上的部门名称及平均工资。

select d.dname,avg(e.sal) from emp e ,dept d

where e.deptno = d.deptno group by d.dname,e.deptno having avg(e.sal) > 2500;

6a7857a9198e

查询员工岗位中不是以“SA”开头并且平均工资在2500元以上的岗位及平均工资，并按平均工资降序排序。

select job ,avg(sal) from emp where job not like 'SA%' group by job

having avg(sal) > 2500 order by avg(sal) desc ;

6a7857a9198e

查询部门人数在2人以上的部门名称、最低工资、最高工资。

select d.dname,min(e.sal),max(e.sal) from emp e ,dept d

where e.deptno = d.deptno group by e.deptno,d.dname having count(e.deptno) > 2;

6a7857a9198e

查询岗位不为SALESMAN，工资和大于等于2500的岗位及每种岗位的工资和。

select job,sum(sal) from emp where job not in('SALESMAN') group by job having sum(sal) > 2500;

6a7857a9198e

显示经理号码和经理姓名，这个经理所管理员工的最低工资，没有经理的KING也要

显示，不包括最低工资小于3000的，按最低工资由高到低排序

select m.empno,m.ename,min(w.sal) from emp w left outer join emp m

on w.mgr = m.empno group by m.empno,m.ename

having min(w.sal) <> 3000 order by min(w.sal) desc;

6a7857a9198e

查询工资高于编号为7782的员工工资，并且和7369号员工从事相同工作的员工的编号、姓名及工资。

select e.empno,e.ename,e.sal from emp e

where e.sal > (select sal from emp where empno = 7782)

and e.job =(select job from emp where empno = 7369 );

6a7857a9198e

查询工资最高的员工姓名和工资。

select ename,sal from emp where sal = (select max(sal) from emp ) ;

6a7857a9198e

查询部门最低工资高于10号部门最低工资的部门的编号、名称及部门最低工资。

select e.deptno,d.dname,min(e.sal)from emp e,dept d

where e.deptno = d.deptno group by e.deptno,d.dname

having min(e.sal) > (select min(sal) from emp where deptno = 10);

6a7857a9198e

查询员工工资为其部门最低工资的员工的编号和姓名及工资。

select empno,ename,sal from emp where sal in(select min(sal) from emp group by deptno );

6a7857a9198e

显示经理是KING的员工姓名，工资。

select ename,sal from emp where mgr = (select empno from emp where ename = 'KING');

6a7857a9198e

显示比员工SMITH参加工作时间晚的员工姓名，工资，参加工作时间。

select ename,sal,hiredate from emp where hiredate > (select hiredate from emp where ename = 'SMITH');

6a7857a9198e