Oracle学习笔记3—子查询/集合

接上一篇

1.相关子查询
与普通子查询的区别：先执行主查询，再执行子查询

执行过程：执行主查询，然后从查询出来的数据逐条的放入子查询的语句中进行查询，然后找出符合主查询条件的数据。(个人理解+总结)

例：查询哪些员工的工资大于其所在部门的平均工资，显示：last_name,department_id,salary

SELECT LAST_NAME,DEPARTMENT_ID,SALARY
FROM EMPLOYEES E
WHERE SALARY>(SELECT AVG(SALARY)
              FROM EMPLOYEES
          WHERE DEPARTMENT_ID=E.DEPARTMENT_ID);

分析执行过程：
1.首先执行主查询，即：

SELECT LAST_NAME,DEPARTMENT_ID,SALARY
FROM EMPLOYEES E

结果如下：

2.将查询出来的结果逐条放入子查询中；

当选择第一条数据时，根据子查询的语义，找出department_id=90的平均工资，即：

SELECT AVG(SALARY)
FROM EMPLOYEES
WHERE DEPARTMENT_ID=90;

子查询结果为：

3.子查询结果出来后，在进行根据主查询where条件进行比较，找出符合条件的数据，即：
SALARY（此时是24000）>AVG(SALARY) （此时是19333.3333），
符合条件则保留、显示。
4.循环执行1-3步骤，直到主查询中所有数据项都执行了1-3的步骤。

每一条都执行上图的操作顺序。
———————————————————————————————————————————————

上图的标记也是相关子查询和普通子查询的不同之处。

2.集合运算：并集，交集，补集

（1）并集：UNION/UNION ALL

• 集合运算时，各个集合中列的个数与类型的顺序必须保持一致。
• 集合运算时，第一个集合可以决定列的别名。
• 集合运算时，最后一个集合可以决定排序规则。
• UNION:自动去重复值
• UNION ALL:不去重复值，在不考虑重复值的情况下，建议使用UNION ALL，效率高。

（2）交集：INTERSECT
查询连个集合的共有的数据。

（3）补集:MINUS

例：查询employees表中收入最高的6-10员工的last_name,salary

SELECT LAST_NAME,SALARY
FROM (SELECT LAST_NAME,SALARY
      FROM EMPLOYEES
      ORDER BY SALARY DESC)
WHERE ROWNUM<=10
MINUS
SELECT LAST_NAME,SALARY
FROM (SELECT LAST_NAME,SALARY
      FROM EMPLOYEES
      ORDER BY SALARY DESC)
WHERE ROWNUM<=5
ORDER BY 2 DESC;

3.三个序列函数

1.ROW_NUMBER() OVER(ORDER BY)——从1开始，为每一条分组记录返回一个数字；
2.RANK() OVER(ORDER BY)——Rank 是在每个分组内部进行排名的；
3.DENSE_RANK() OVER(ORDER BY)——连续排序，但是显示并列的排序。

例：

SELECT ROW_NUMBER() OVER(ORDER BY SALARY DESC) AS 名次1,
       RANK() OVER(ORDER BY SALARY DESC) AS 名次2,
       DENSE_RANK() OVER(ORDER BY SALARY DESC) AS 名次3,
       LAST_NAME,SALARY
FROM EMPLOYEES;

注意红色方框中的数据