mysql多行函数(聚合函数)

多行函数(聚合函数):
没分组,多行函数不能和其他普通字段一起查询;分组字段可以查询
(oracle直接报错,mysql执行结果是查询第一行的数据,结果毫无意义)  牢记!!!

• sum()
• avg()
• max()
• min()
• count()

多个多行函数可以一起查询
select sum(sal),avg(sal),max(sal),min(sal),count(*) from emps;

多行函数会忽略null值

count(*) 记行数
count(distinct a) 去除重复再记数

group by子句 分组求多行函数
按指定字段中相同的值进行分组
分组后分别求多行函数
分组字段可以查询
group by a  按照a字段相同值进行分组
group by a,b  按照a,b组合的相同值进行分组(比较少见)

-- 每个部分的平均工资,并过滤掉dept_id为null的部门
先过滤,后分组
select dept_id,avg(sal) a from emps where dept_id is not null group by dept_id order by a;

-- 每个工作岗位job_id的人数
select job_id,count(*) from emps group by job_id;

-- 按主管id分组,求每个主管的手下人数
select mgr_id,count(*) from emps where mgr_id is not null group by mgr_id;

having子句
用来对多行函数结果,再进行过滤
执行顺序:where>group by>having>order by
having和where作用相同,都是条件过滤
where过滤"普通条件",最早执行
having过滤"多行函数结果",先分组group by,求完多行函数后,后执行having过滤
-- 按主管id分组,求出每个主管的手下人数为1的分组(只有一个手下的主管)
select mgr_id,count(*) from emps group by mgr_id having count(*)=1;
select mgr_id,count(*) c from emps group by mgr_id having c=1;
select mgr_id,count(*) c from emps where mgr_id is not null group by mgr_id having c=1 order by mgr_id desc;

-- 平均工资小于等于5000的岗位代码
select avg(sal) a,job_id from emps group by job_id having a<=5000;

子查询/查询嵌套
条件子查询
from子查询/行内视图
select子查询(较为少见)

条件子查询
一个查询的查询结果,作为另一个查询的过滤条件

单值子查询 =(select ...) >(select ...) >=(select ...) <(select ...) <=(select ...)
多值子查询 in(select ...)   >all(select ...) 比最大值大    >any(select ...) 比最小值大
多列子查询(a,b必须加小括号) where (a,b) in(select ...)      
where (a,b)=(select ...)      

-- 工资小于平均工资
select fname,sal from emps where sal<(
  select avg(sal) from emps
);

-- 每个部门拿最高工资的员工 (经典面试题)
select fname,sal,dept_id from emps where (dept_id,sal) in (
  select dept_id,max(sal) m from emps where dept_id is not null group by dept_id
) order by dept_id;  

-- 只有一个人的部门,查询这些员工(经典面试题)
方法:第一步,按部门分组求人数,过滤只有一人的部门
    select job_id from emps where dept_id is not null group by job_id having count(*)=1;
第二步,用部门id过滤查询员工
select fname,dept_id from emps where dept_id in (
  select dept_id from emps where dept_id is not null group by dept_id having count(*)=1
) order by dept_id;  

from子查询/行内视图
从查询的查询结果,再查询(此处mysql必须要别名,oracle不作要求)
-- 平均工资最低的工作岗位代码(经典面试题)三层嵌套
方法:第一步,按部门分组求平均工资
select job_id,avg(sal) a from emps group by job_id;
第二步,求平均工资最小值
select min(a) from (
    select job_id,avg(sal) a from emps group by job_id
) t;
第三步,用平均工资最小值,过滤岗位代码  重用第一步,设置having过滤
select job_id,avg(sal) a from emps group by job_id having a=(
    select min(a) from (
        select job_id,avg(sal) a from emps group by job_id
    ) t
);

oracle:  
select min(avg(sal)) from emps group by job_id; 
支持多行函数嵌套执行,
对from子查询/行内视图的结果,不需要别名
mysql:
不支持多行函数嵌套执行,
对from子查询/行内视图的结果,必须用别名才能查询

-- 手下人数最多的主管id
mgr_id  人数
20      7
30      11
50      11
80      11
90      10

方法:第一步,按mgr_id分组求人数(去除第一行凑巧结果order by c)
select mgr_id,count(*) c from emps where mgr_id is not null group by mgr_id order by c;

第二步,求人数的最大值11
select max(c) from(
    select mgr_id,count(*) c from emps where mgr_id is not null group by mgr_id order by c
) t;

第三步,用人数过滤查询主管id  重用第一步,设置having过滤
select mgr_id,count(*) c from emps where mgr_id is not null group by mgr_id having c=(
    select max(c) from(
        select mgr_id,count(*) c from emps where mgr_id is not null group by mgr_id order by c
    ) t
);