Oracle分组函数和连接子查询

时间：2022-07-11 15:06:29

作者：gqk:

 什么是组函数：

用于一组数据，并对一组数据返回一个值：

Oracle分组函数和连接子查询

组函数类型：

  • 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;

分组查询：

Oracle分组函数和连接子查询

--查询每个部门的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;

Oracle分组函数和连接子查询

 

--查询每个部门的部门名称，人数：

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的

　Oracle分组函数和连接子查询

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;　　

子查询：

　Oracle分组函数和连接子查询

Oracle分组函数和连接子查询

--查询谁的工资比Abel高？（先查询Abel的工资，再去查询比他高的值）

select salary from employees where last_name='Abel';

　　Oracle分组函数和连接子查询

select * from employees where salary>(select salary from employees where last_name='Abel');

--查询工资最低的员工(先查询最低工资为多少，在查询最低工资的员工)

select min(salary) from employees;　　

Oracle分组函数和连接子查询

SELECT *
FROM   employees
WHERE  salary = (SELECT MIN(salary)
                 FROM   employees);

--查询和149号员工同一个部门的其他员工

  • 　先去查询149号员工所在的部门编号：
SELECT department_id
                        FROM   employees
                        WHERE  employee_id = 149

Oracle分组函数和连接子查询

 

  • 　　再查询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);

　Oracle分组函数和连接子查询

Oracle分组函数和连接子查询

 

　

　　

　　

　

　　

　　

　　

　　

　　

　　

　　

　

　

　

 

标签：

相关文章