Oracle分组函数和连接子查询
作者：gqk:
什么是组函数：
用于一组数据，并对一组数据返回一个值：
组函数类型：
- AVG:求平均值
- COUNT:计数
- MAX：最大值
- MIN：最小值
- SUM：求和
--查询所有工资的总和
SELECT SUM(salary) FROM employees;
--组函数
SELECT SUM(salary),AVG(salary),MAX(salary),MIN(salary),COUNT(salary) FROM employees WHERE department_id=50;
--所有组函数都是忽略空值的
SELECT COUNT(commission_pct) FROM employees;
--查询所有没有奖金的人数
SELECT COUNT(*)-COUNT(commission_pct) FROM employees; SELECT COUNT(*) FROM employees WHERE commission_pct IS NULL;
--查询部门的总数（统计不重复的计数）
SELECT COUNT(DISTINCT department_id) FROM employees;
--以下是错误的写法，两列的行数不兼容 --SELECT employee_id,SUM(salary) FROM employees;
分组查询：
--查询每个部门的ID，员工工资总和，最高工资
SELECT department_id,SUM(salary) sal_sum,MAX(salary) FROM employees WHERE department_id IS NOT NULL GROUP BY department_id ORDER BY sal_sum DESC;
--多个分组表达式 SELECT department_id,job_id,SUM(SALARY) FROM employees GROUP BY department_id,job_id;
--查询每个部门的部门名称，人数：
SELECT d.department_name,count(e.employee_id) emp_count FROM employees e INNER JOIN departments d ON e.department_id=d.department_id GROUP BY d.department_name;
--统计每年入职的人数:年份，人数
SELECT to_char(hire_date,'yyyy') 年份,count(*) 人数 FROM employees GROUP BY to_char(hire_date,'yyyy') ORDER BY 1;
非法使用组函数：
- 不能在where子句中使用组函数
- 可以在having子句中使用组函数
--查询部门编号，每个部门的平均共组，平均工资大于8000的
select department_id, avg(salary) from employees group by department_id having avg(salary)>8000 and department_id is not null;
--统计每年入职的人数:年份，人数（仅返回入职不少于2人的年份的数据）
select to_char(hire_date,'yyyy'),count(*) from employees group by to_char(hire_date,'yyyy') having count(*)>=2;
--WHERE条件：过滤分组之前的数据
--HAVING条件：过滤分组之后的数据
--查询每个部门的ID，员工工资总和(where 可以存放在 分组之前)
select department_id,sum(salary) from employees group by department_id having department_id is not null;
子查询：
--查询谁的工资比Abel高？（先查询Abel的工资，再去查询比他高的值）
select salary from employees where last_name='Abel';
select * from employees where salary>(select salary from employees where last_name='Abel');
--查询工资最低的员工(先查询最低工资为多少，在查询最低工资的员工)
select min(salary) from employees;
SELECT *
FROM employees
WHERE salary = (SELECT MIN(salary)
FROM employees);
--查询和149号员工同一个部门的其他员工
- 先去查询149号员工所在的部门编号：
SELECT department_id
FROM employees
WHERE employee_id = 149
- 再查询80号员工部门的员工出过149号员工
SELECT *
FROM employees
WHERE department_id = (SELECT department_id
FROM employees
WHERE employee_id = 149)
AND employee_id <> 149;
--查询比202号员工的部门经理的工资还要高的员工
- 查询202号员工所在的部门编号（20号部门）
- 查询20号部门的员工经理id（201号）
- 查询经理的工资
- 查询还要高的员工
SELECT *
FROM employees
WHERE salary > (SELECT salary
FROM employees
WHERE employee_id =
(SELECT manager_id
FROM departments
WHERE department_id =
(SELECT department_id
FROM employees
WHERE employee_id = 202)));
--查询员工编号，姓名，工资，工资总和(在查询列表中使用子查询，只能是单行单列)
SELECT employee_id,last_name,salary,
(SELECT SUM(salary) FROM employees) AS 工资总和
FROM employees;
--查询工资最高的前5名的员工（临时视图，内联视图）
SELECT *
FROM (SELECT *
FROM employees
ORDER BY salary DESC)
WHERE ROWNUM <= 5;
--查询员工表中第6到第12条数据
SELECT *
FROM (SELECT ROWNUM rnum,
e.*
FROM employees e)
WHERE rnum BETWEEN 6 AND 12;
--推荐以下写法，效率较高
SELECT *
FROM (SELECT ROWNUM rnum,
e.*
FROM employees e
WHERE ROWNUM <= 12)
WHERE rnum >= 6;
--查询工资最高的第6到第12条员工
SELECT *
FROM (SELECT rownum rnum,
e.*
FROM (SELECT *
FROM employees
ORDER BY salary DESC) e
WHERE rownum <= 12)
WHERE rnum >= 6;
--查询所有是部门经理的员工（多行子查询）
SELECT *
FROM employees
WHERE employee_id IN (SELECT manager_id
FROM departments);
--查询所有不是部门经理的员工（多行子查询）
SELECT *
FROM employees
WHERE employee_id NOT IN
(SELECT manager_id
FROM departments
WHERE manager_id IS NOT NULL);
--ANY逻辑或比较 >ANY(50,80,90)
--查询大于60号部门任何一个员工工资的员工
SELECT *
FROM employees
WHERE salary > ANY (SELECT salary
FROM employees
WHERE department_id = 60);
--ALL逻辑与比较 >ALL(50,80,90)
--查询所有员工人数不少于3人的部门信息
SELECT *
FROM departments
WHERE department_id IN (SELECT department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*) >= 3);
