<span style="font-size:18px;">第5节 分组函数
1.分组函数:avg count max min sum
2.使用group by 进行分组
3.使用having 进行分组数据的过滤
--1.avg()/sum():只试用与number类型的数据
SELECT AVG(salary) , sum(salary)
FROM employees;
select avg(last_name)
from employees;
--2.max() min() count() :适用于number,Date类型 varchar2类型
select max(salary) , min(salary) , max(last_name) , min(last_Name) ,
max(hire_date) , min (hire_date)
from employees
--count 在累加个数时不计算空值
select count (employee_id) , count (salary) , count(1) , count(2),
count(*) , count(commission_pct)
from employees;
select commission_pct
from employees
where commission_pct is not null ;
--结论:avg = sum / count
select avg(salary) , sum(salary)/count(salary)
from employees
select avg(commission_pct) , sum(commission_pct)/count(commission_pct),
sum(commission_pct)/count(1)
from employees;
select count(nvl(commission_pct , 0))
from employees;
select count(distinct nvl(department_id , 0))
from employees;
--2.使用group by
-- 求出employees表中各部门的平均薪资
----结论:如果在查询项里出现了分组函数以外的列,
--那么分组函数以外的列都应该出现在group by当中,否则出错。
select department_id , avg(salary)
from employees
group by department_id;
select job_id , avg(salary)
from employees
group by job_id;
select department_id , job_id , avg(salary)
from employees
group by department_id , job_id ;
--包含在group by 子句中的列不必包含在select 列表中
select department_id , avg(salary)
from employees
group by department_id , job_id;
--3使用having 进行过滤
--如果过滤条件中出现了分组函数,使用having 替代where
select department_id , avg(salary)
from employees
--having avg(salary)>600
group by department_id
having avg(salary)>600;
--部门最高工资比1000高的部门
select department_id , max (salary)
from employees
having max(salary)>10000
group by department_id;
--分组函数可以嵌套
select max(avg(salary))
from employees
group by department_id;
练习
--查询公司员工工资的最大值,最小值,平均值,总和
select max (salary) , min (salary) ,avg(salary),sum(salary)
from employees;
--查询各job_id的员工工资的最大值,最小值,平均值,总和
select job_id , max(salary) , min(salary) , avg(salary) , sum(salary)
from employees
group by job_id;
--选择具有各个job_id的员工人数
select job_id , count(employee_id)
from employees
group by job_id;
--查询员工最高工资和最低工资的差距(DIFFERENCE)
select max(salary) , min(salary) , max(salary)-min(salary)"difference"
from employees;
--查询各个管理者手下员工的最低工资,其中最低工资不能低于
--6000,没有管理者的员工不计算在内
select manager_id , min(salary)
from employees
group by manager_id
having min(salary) >= 6000 and manager_id is not null;
--查询所有部门的名字,location_id,员工数量和工资平均值
select department_name , location_id , count(employee_id) , avg(salary)
from departments d join employees e
on e.department_id = e.department_id
group by location_id ,department_name ;
--查询公司在1995-1998年之间,每年雇用的人数,结果类似下面的格式
select hire_date , count(employee_id)
from employees
group by hire_date
having hire_date between to_date('1995','yyyy') and to_date('1998','yyyy');
select count(1) ,
count(decode( to_char(hire_date , 'yyyy'),'1995',1 ,null ))"1995",
count(decode( to_char(hire_date , 'yyyy'),'1996',1 ,null ))"1996",
count(decode( to_char(hire_date , 'yyyy'),'1997',1 ,null ))"1997",
count(decode( to_char(hire_date , 'yyyy'),'1998',1 ,null ))"1998"
from employees
where to_char(hire_date , 'yyyy') IN('1995' , '1996' , '1997' ,'1998');
*******************************************************
第六节 子查询
--子查询
--外查询
select last_name , salary
from employees
where salary >(
--内查询
SELECT salary
from employees
where last_name='Abel'
);
--查询last_name 为'Chen'的manager的信息
select manager_id
from employees
where last_Name = 'Chen';
select last_name , salary
from employees
where employees_id = 108;
--自连接
select e2.last_name , e2.salary
from employees e1 , employees e2
where e1.manager_id = e2.manager_id
and e1.last_name = 'Chen';
--子查询
select last_name , salary
from employees
where employee_id = (
select manager_id
from employees
where last_name = 'Chen'
);
--如何书写一个包含子查询的select语句?第一种方式:
--从里向外写 第二种方式:从外向里写。
--返回job_id与141号员工相同,salary比143号员工多的员工姓名,job_id 和工资
select last_name , job_id , salary
from employees
where job_id = (
select job_id
from employees
where employee_id = 141
)
select last_name , job_id ,salary
from employees
where job_id = (
select job_id
from employees
where employee_id = 141
)
and salary > (
select salary
from employees
where employee_id =143
)
--返回公司工资最少的员工的last_name,job_id和salary
select last_name , job_id ,salary
from employees
where salary = (
select min(salary)
from employees
)
--查询最低工资大于50号部门最低工资的部门id和其最低工资
select department_id , min(salary)
from employees
group by department_id
having min(salary) > (
SELECT min(salary)
from employees
group by department_id---???????
having department_id = 50
)
--如下的操作是错误的
select employee_id , last_name
from employees
where salary = (
select MIN(salary)
from employees
group by department_id
)
select last_name , job_id
from employees
where job_id = (
select job_id
from employees
where last_name = 'Haas'
)
--查询工资最低的员工信息: last_name, salary
select last_name , salary
from employees
where salary = (
select min(salary)
from employees
)
--查询平均工资最低的部门信息
select *
from departments
where department_id = (
select department_id
from employees
group by department_id
having avg(salary) = (
select min(avg(salary))
from employees
group by department_id
)
)
--查询平均工资最低的部门信息和该部门的平均工资
select *
from departments
where department_id = (
select department_id
from employees
group by department_id
having avg(salary) = (
select Min(avg(e.salary))
from employees e
group by department_id
)
)
第六节
--谁的工资比Abel的高
--子查询
--外查询
select last_name , salary
from employees
where salary > (
--内查询
select salary
from employees
where last_Name = 'Abel'
)
--查询last_name为'Chen'的manager的信息
select *
from employees
where employee_id IN (
select manager_id
from employees
where last_name = 'Chen'
)
--自连接
select d.*
from employees e , employees d
where e.manager_id = d.employee_id and e.Last_name = 'Chen'
--子查询
select last_name , salary
from employees
group by manager_id
having employee_id = (
select manager_id
from employees
where last_name = 'Chen'
)
select *
from employees
where employee_id = (
select manager_id
from employees
where last_name ='Chen'
)
--返回job_id与141号员工相同,
--salary比143号员工多的员工姓名,job_id 和工资
select last_name , job_id , salary
from employees
where job_id in(
select job_id
from employees
where employee_id = 141
) and salary > (
select salary
from employees
where employee_id =143
)
select last_name , job_id salary
from employees
where job_id = (
select job_id
from employees
where employee_id = 141
)
and salary > (
select salary
from employees
where employee_id = 143
)
--返回公司工资最少的员工的last_name,job_id和salary
select last_name ,job_id ,salary
from employees
where salary = (
select min(salary)
from employees
)
--查询最低工资大于--50号部门最低工资--的部门id和其最低工资
select department_id , min(salary)
from employees
group by department_id
having min(salary) > (
select min(salary)
from employees
where department_id = 50
)
select last_name , job_id
FROM employees
where job_id = (
select job_id
from employees
where last_name = 'Chen'
)
--查询工资最低的员工信息: last_name, salary
select last_name , salary
from employees
where salary = (
select min(salary)
from employees
)
--查询平均工资最低的部门信息
select *
from departments
where department_id = (
select department_id
from employees
group by department_id
having avg(salary)= (
select min(avg(salary))
from employees
group by department_id
)
)
--查询平均工资最低--的部门信息和该部门的平均工资
select d.* , ( select avg(salary)
from employees e
where e.department_id =d.department_id
)
from departments d
where department_id = (
select department_id
from employees e1
group by department_id
having avg(salary) = (
select min(avg(salary))
from employees
group by department_id
)
)
--查询平均工资最高的 job 信
select *
from jobs
where job_id = (
select job_id
from employees
group by job_id
having avg(salary) = (
select min(avg(salary))
from employees
group by job_id
)
)
--查询平均工资高于公司平均工资的部门有哪些?
select department_name
from departments
where department_id IN(
select department_id
from employees
group by department_id
having avg(salary)> (
select min(avg(salary))
from employees
group by department_id
)
)
--查询出公司中所有 manager 的详细信息.
select * from
employees
where employee_id in (
select manager_id
from employees
group by manager_id
)
--各个部门中 最高工资中最低的那个部门的
--最低工资是多少
select min(salary)
from employees
group by department_id
having department_id =(
select department_id
from employees
where salary = (
select max(salary)
from employees
)
)
--查询平均工资最高的部门的 manager
-- 的详细信息: last_name, department_id, email, salary
select last_name , department_id , email , salary
from employees
where employee_id in (
select distinct manager_id
from employees
where department_id = (
select department_id
from employees
group by department_id
having avg(salary) = (
select max(avg(salary))
from employees
group by department_id
)
)
)
select last_name, department_id, email, salary
from employees
where employee_id in (
select distinct manager_id
from employees
where department_id = (
select department_id
from employees
group by department_id
having avg(salary) = (
select max(avg(salary))
from employees
group by department_id
)
)
)
--查询 1999 -----年来公司的人------所有员工的最高工资---的那个员工的信息.
--1.查询出1999年来公司的职工的最高工资
--2.找到最高工资为11000的员工
select *
from employees
where employee_id in(
select employee_id
from employees e1
where salary = (
select min(salary)
from employees e2
where hire_date like '%97'
)and hire_date like '%97'
)
--返回其它job_id中比job_id为‘IT_PROG’
--的任一工资低的员工的员工号、姓名、job_id
--这样是不对的
select min(salary),hire_date
from employees
group by hire_dat
having
hire_date like '%99'
--这是个对的
select salary,hire_date
from employees
where salary = (
select min(salary)
from employees e2
where hire_date like '%99'
)
and hire_date like '%97'
--查询 1999 年来公司的人所有员工的最高工资的那个员工的信息.
--1.查询出1999年来公司的职工的最高工资
--2.找到最高工资为11000的员工
select *
from employees
where salary in (
select max(salary)
from employees
where to_char(hire_date , 'yyyy') = '1999'
)and to_char(hire_date , 'yyyy') = '1999'
--返回其它job_id中比job_id为‘IT_PROG’的任一工资低的员工
--的员工号、姓名、job_id 以及salary
select job_id ,employee_id , last_name , job_id ,salary
from employees
where salary > (
select min(salary)
from employees
group by job_id
having job_id = 'IT_PROG'
)
and job_id <> 'IT_PROG'
练习
--查询和Zlotkey相同部门的员工姓名和雇用日期
select last_name , hire_date
from employees
where department_id = (
select department_id
from employees
where last_name ='Zlotkey'
)
--查询工资---比公司平均工资--高的员工的员工号,
--姓名和工资。
select employee_id , last_Name , salary
from employees
where salary >(
select avg(salary)
from employees
)
--查询各部门中工资比本部门平均工资
--高的员工的员工号, 姓名和工资
select employee_id , last_name , salary
from employees
where salary > (
select avg(salary)
from employees
)
--查询和姓名中包含字母u的员工在相同部门的
--员工的员工号和姓名
select employee_id , last_name
from employees
where department_id in (
select department_id
from employees
where last_name like '%u%'
)
--查询在部门的location_id为1700的部门
--工作的员工的员工号
select employee_id
from employees
where department_id IN (
select distinct department_id
from departments
where location_id = 1700
)
--查询管理者是King的员工姓名和工资
select last_name , salary
from employees
where manager_id in (
select employee_id
from employees
where last_name = 'King'
)
</span>
(尚硅谷)Orcal sql 分组函数 子查询
最新推荐文章于 2022-11-18 23:29:50 发布