分组查询
group by
关键字
例子
1.查询各job_id的员工工资的最大值,最小值,平均值,总和
select job_id,max(salary),min(salary),avg(salary),sum(salary)
from employees
group by job_id
输出:
JOB_ID MAX(SALARY) MIN(SALARY) AVG(SALARY) SUM(SALARY)
------------------------------ ----------- ----------- ----------- -----------
AC_MGR 12000 12000 12000 12000
AC_ACCOUNT 8300 8300 8300 8300
IT_PROG 9000 4200 5760 28800
ST_MAN 8200 5800 7280 36400
AD_ASST 4400 4400 4400 4400
PU_MAN 11000 11000 11000 11000
SH_CLERK 4200 2500 3215 64300
AD_VP 17000 17000 17000 34000
FI_ACCOUNT 9000 6900 7920 39600
MK_MAN 13000 13000 13000 13000
PR_REP 10000 10000 10000 10000
2. 选择具有各个job_id的员工人数
select job_id,count(employee_id)
from employees
group by job_id
输出:
JOB_ID COUNT(EMPLOYEE_ID)
------------------------------ ------------------
FI_MGR 1
PU_CLERK 5
SA_MAN 5
MK_REP 1
AD_PRES 1
SA_REP 30
HR_REP 1
ST_CLERK 20
19 rows selected.
3.查询各个管理者手下员工的最低工资,其中最低工资不能低于6000,没有管理者的员工不计算在内
注意:
- where必须跟在 from ...
之后
- having必须跟在group by...
之后
select manager_id,min(salary)
from employees
where manager_id is not null
group by manager_id
having min(salary) >= 6000
输出:
MANAGER_ID MIN(SALARY)
---------- -----------
147 6200
205 8300
108 6900
148 6100
149 6200
201 6000
102 9000
145 7000
146 7000
9 rows selected.
子查询
何为子查询?
通俗的来讲,想要查询的结果必须依赖其他查询就是子查询
单行子查询
1.查询都有谁比’Abel’的工资高
注意
- 子查询要包含在括号内。
- 将子查询放在比较条件的右侧。
- 单行操作符对应单行子查询,多行操作符对应多行子查询。
SQL> select last_name from employees
where salary > (select salary from employees where last_name='Abel');
输出:
LAST_NAME
--------------------
Hartstein
Higgins
King
Kochhar
De Haan
Greenberg
Russell
Partners
Errazuriz
Ozer
10 rows selected.
2.查询最低工资大于50号部门最低工资的部门id和其最低工资
该语句包含了 :分组查询、组函数、以及having关键字
select department_id,min(salary) from employees group by department_id having min(salary) > (select min(salary) from employees where department_id = 50);
输出:
DEPARTMENT_ID MIN(SALARY)
------------- -----------
100 6900
30 2500
7000
20 6000
70 10000
90 17000
110 8300
40 6500
80 6100
10 4400
60 4200
11 rows selected.
多行子查询
- 返回多行
- 使用多行比较操作符
操作符 | 含义 |
---|---|
IN | 等于列表中的任意一个 |
ANY | 和子查询返回的某一个值比较 |
ALL | 和子查询返回的所有值比较 |
1.返回其它部门中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id 以及salary
select last_name,salary,job_id from employees
where salary < any (select salary from employees where job_id='IT_PROG')
and job_id <> 'IT_PROG';
输出:
LAST_NAME SALARY JOB_ID
------------------------------ ---------
Hutton 8800 SA_REP
....
76 rows selected.
2.返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id 以及salary
select last_name,salary,job_id from employees
where salary < all (select salary from employees where job_id='IT_PROG')
and job_id <> 'IT_PROG';
输出:
LAST_NAME SALARY JOB_ID
-------------------- --------
Philtanker 2200 ST_CLERK
Olson 2100 ST_CLERK
....
44 rows selected.
注意:上面的事76行记录,下面的事44行记录
3.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资
select employee_id,last_name,salary
from employees e1
where salary > (
select avg(salary)
from employees e2
where e1.department_id = e2.department_id
group by department_id
)
输出:
EMPLOYEE_ID LAST_NAME SALARY
------------------------------------------
193 Everett 3900
.....
38 rows selected.
4.查询管理者是King的员工姓名和工资
select last_name,salary
from employees
where manager_id in (
select employee_id
from employees
where last_name = 'King'
)
输出:
LAST_NAME SALARY
----------------------------
Cambrault 11000
Zlotkey 10500
......
14 rows selected.