2. 分组查询&amp;&amp;子查询

2024-07-21 02:52:55

字体：大中小

来源：转载

供稿：网友

　分组查询

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 12000AC_ACCOUNT 8300 8300 8300 8300IT_PROG 9000 4200 5760 28800ST_MAN 8200 5800 7280 36400AD_ASST 4400 4400 4400 4400PU_MAN 11000 11000 11000 11000SH_CLERK 4200 2500 3215 64300AD_VP 17000 17000 17000 34000FI_ACCOUNT 9000 6900 7920 39600MK_MAN 13000 13000 13000 13000PR_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 1PU_CLERK 5SA_MAN 5MK_REP 1AD_PRES 1SA_REP 30HR_REP 1ST_CLERK 2019 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 70009 rows selected.

子查询

何为子查询？

通俗的来讲，想要查询的结果必须依赖其他查询就是子查询

单行子查询

1.查询都有谁比’Abel’的工资高

注意

子查询要包含在括号内。将子查询放在比较条件的右侧。单行操作符对应单行子查询，多行操作符对应多行子查询。SQL> select last_name from employees where salary > (select salary from employees where last_name='Abel');

输出：

LAST_NAME--------------------HartsteinHigginsKingKochharDe HaanGreenbergRussellPartnersErrazurizOzer10 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 420011 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.