Oracle学习笔记2—分组/子查询

1.GROUP BY字句：分组

语法：
SELECT 4
FROM 1
[WHERE] 2
[GROUP BY] 3
[ORDER BY] 5

• 执行过程：group by 子句首先将一个整体的数据表格按照GROUP BY后面的条件分成若干个子表格，在根据每个子表格进行查询显示。

强调内容当使用GROUP BY子句时，SELECT子句中非组函数的列必须出现在GROUP BY子句中参加分组。

GROUP BY后面可以接多个参数（分组条件）；

运用技巧：在理解GROUP BY子句时，可以在每一个条件前面加上“每个”；

例：查询每个部门中每个职位的人数，显示：depatment_id,job_id,人数。

SELECT DEPARTMENT_ID,JOB_ID,COUNT(*)
FROM EMPLOYEES
GROUP BY （每个）DEPARTMENT_ID,（每个）JOB_ID
ORDER BY 1 ASC;

2.HAVING子句

过滤分组结果。使用HAVING子句时必须使用GROUP BY子句。

语法：
SELECT 5
FROM 1
[WHERE] 2
[GROUP BY] 3
[HAVING] 4
[ORDER BY] 6

• 当使用GROUP BY子句或DISTINCT关键字时，ORDER BY子句中排序的列必须在SELECT子句中出现；
• WHERE子句中不能使用组函数作为过滤条件；
• HAVING子句中可以使用组函数作为过滤条件
• 当条件可以放在WH；ERE子句中也可以放在HAVING子句中时，建议放在WHERE子句中可以提高查询效率。

例：查询平均大于10000的部门，显示：department_id,平均工资，结果根据工资降序排序。

SELECT DEPARTMENT_ID,AVG(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING AVG(SALARY)>10000
ORDER BY AVG(SALARY) DESC;

3.组函数嵌套

• 组函数嵌套时，必须使用GROUP BY子句
• 组函数嵌套时，SELECT子句中除了组函数嵌套以外，不能出现其它列。
• 组函数嵌套只能直接出现在SELECT子句中。

例：显示哪个部门的平均工资是最高的，显示这个部门的平均工资

SELECT MAX(AVG(SALARY)),AVG(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID;

4.子查询(嵌套查询)
语法：
SELECT 列名
FROM 表名
WHERE 条件 （SELECT 列名 FROM 表名 WHERE 条件）

• 从Oracle8i开始，除了GROUP BY子句外，其它子句都可以使用子查询。
• 无论子查询出现在哪个子句中，子查询必须放在一对小括号内。
• 如果子查询作为条件，建议将子查询放在运算符右面，可以提高查询效率。
• 如果子查询作为条件，子查询中列的个数与类型必须与主查询条件列的个数与类型保持一致。
• 除非执行TOP N操作，否则不能在子查询中使用ORDER BY子句。
• 普通子查询执行的顺序:先执行子查询，再执行主查询。

5.子查询的嵌套
子查询中包含子查询

例：查询与入职最早员工在同一部门工作的员工的

last_name,department_id
SELECT LAST_NAME,DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID
                FROM EMPLOYEES
                WHERE HIRE_DATE=(SELECT MIN(HIRE_DATE)
                         FROM EMPLOYEES));

6.HAVING子句与子查询
HAVING条件中包含子查询

例：查询平均工资大于公司平均工资的部门，显示:department_id,平均工资

SELECT DEPARTMENT_ID,AVG(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING AVG(SALARY)>(SELECT AVG(SALARY)
                    FROM EMPLOYEES);

SELECT子句与子查询:子查询只能返回一行一列(一个值)

7.FROM子句与子查询

语义：在FROM后面添加子查询，即外层查询的是子查询后形成的数据表。

• 伪列:创建表时，Oracle自动为表添加的列。
• ROWID:保存数据在硬盘或内存中的地址。
• ROWNUM:行号,只能小于或小于等于，或者等于1。否则返回0行数据。

例：查询收入最低的前5名员工的last_name,salary

SELECT LAST_NAME,SALARY
FROM (SELECT LAST_NAME,SALARY
      FROM EMPLOYEES
      ORDER BY SALARY ASC)
WHERE ROWNUM<=5;

8.多行子查询:ALL,ANY,IN

• all：满足所有子查询出来的数据项；
• any：满足任意一个子查询出来的数据项；
• in：满足与子查询出来的数据项相等的项。