2. 分组查询&&子查询

　分组查询

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.