Oracle基础之组函数、子查询

--组函数(聚合函数)
--常用组函数
-- SUM():求总和
SELECT SUM(SALARY)
FROM EMPLOYEES;


-- AVG():求平均值
SELECT AVG(SALARY)
FROM EMPLOYEES;


-- MAX()/ MIN():最大/最小值
SELECT MAX(SALARY),MIN(SALARY)
FROM EMPLOYEES;


SELECT MAX(HIRE_DATE), MIN(HIRE_DATE)
FROM EMPLOYEES;


SELECT MAX(LAST_NAME), MIN(LAST_NAME)
FROM EMPLOYEES;


-- COUNT()
SELECT COUNT(*)--获得查询结果的行数
FROM EMPLOYEES;


SELECT COUNT(COMMISSION_PCT)-- 获得指定列中不为NULL的值的个数
FROM EMPLOYEES;


--组函数与NULL值:关系数据库中所有的组函数在计算时会自动忽略NULL值。
SELECT AVG( NVL(COMMISSION_PCT,0))
FROM EMPLOYEES;


--GROUP BY子句：分组
SELECT       4
FROM         1
[WHERE]      2
[GROUP BY]   3
[ORDER BY]   5


--查询每个部门的人数，显示：department_id,人数
SELECT DEPARTMENT_ID,COUNT(*)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
ORDER BY 1 ASC;


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


--查询每个职位的平均工资与人数，显示：job_id,平均工资，人数
SELECT JOB_ID,AVG(SALARY),COUNT(*)
FROM EMPLOYEES
GROUP BY JOB_ID;


--查询每个部门的人数，显示:department_name,人数
SELECT D.DEPARTMENT_NAME,COUNT(*)
FROM EMPLOYEES E JOIN DEPARTMENTS D ON E.DEPARTMENT_ID=D.DEPARTMENT_ID
GROUP BY D.DEPARTMENT_NAME;


SELECT D.DEPARTMENT_NAME,COUNT(E.EMPLOYEE_ID)
FROM EMPLOYEES E RIGHT JOIN DEPARTMENTS D ON E.DEPARTMENT_ID=D.DEPARTMENT_ID
GROUP BY D.DEPARTMENT_NAME;


--查询每个部门中每个职位的人数，显示：depatment_id,job_id,人数。
SELECT DEPARTMENT_ID,JOB_ID,COUNT(*)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID,JOB_ID
ORDER BY 1 ASC;


--HAVING子句： 过滤分组结果。使用HAVING子句时必须使用GROUP BY子句。
SELECT      5
FROM        1
[WHERE]     2
[GROUP BY]  3
[HAVING]    4
[ORDER BY]  6


--查询平均工资大于10000的部门，显示：department_id,平均工资，结果根据工资降序排序。
SELECT DEPARTMENT_ID,AVG(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING AVG(SALARY)>10000
ORDER BY AVG(SALARY) DESC;


--当使用GROUP BY子句或DISTINCT关键字时，ORDER BY子句中排序的列必须在SELECT子句中出现。


--WHERE子句中不能使用组函数作为过滤条件
--HAVING子句中可以使用组函数作为过滤条件


--当条件可以放在WHERE子句中也可以放在HAVING子句中时，建议放在WHERE子句中可以提高查询效率
--查询最低工资大于6000，并且job_id中不包含REP的职位信息，显示:job_id,最低工资
SELECT JOB_ID,MIN(SALARY)
FROM EMPLOYEES
WHERE JOB_ID NOT LIKE '%REP%'
GROUP BY JOB_ID
HAVING MIN(SALARY)>6000;


--组函数嵌套
--显示哪个部门的平均工资是最高的，显示这个部门的平均工资
SELECT MAX(AVG(SALARY)),MIN(AVG(SALARY))
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID;


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


--查询人数在3人及3人以上的部门，显示:department_id,人数
SELECT DEPARTMENT_ID,COUNT(*)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING COUNT(*)>=3;


--子查询(嵌套查询)
--查询哪些员工的工资大于176号员工的工资，显示：last_name,salary
SELECT LAST_NAME,SALARY
FROM EMPLOYEES
WHERE SALARY>(SELECT SALARY
              FROM EMPLOYEES
              WHERE EMPLOYEE_ID=176);


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


--普通子查询执行的顺序:先执行子查询，再执行主查询。
--子查询与组函数
--查询工资最高的员工的last_name,salary
SELECT LAST_NAME,SALARY
FROM EMPLOYEES
WHERE SALARY=(SELECT MAX(SALARY)
      FROM EMPLOYEES);


--子查询的嵌套
--查询与入职最早员工在同一部门工作的员工的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));


--HAVING子句与子查询
--查询平均工资大于公司平均工资的部门，显示:department_id,平均工资
SELECT DEPARTMENT_ID,AVG(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING AVG(SALARY)>(SELECT AVG(SALARY)
                    FROM EMPLOYEES);


--查询人数最多的部门，显示:department_id,人数
SELECT DEPARTMENT_ID,COUNT(*)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING COUNT(*)=(SELECT MAX(COUNT(*))
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID);


--SELECT子句与子查询:子查询只能返回一行一列(一个值)
--查询每个部门的人数，显示：department_id,人数，此部门人数占公司总人数的百分比
SELECT DEPARTMENT_ID,COUNT(*),(COUNT(*)/(SELECT COUNT(*) FROM EMPLOYEES))*100||'%' AS 百分比
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
ORDER BY DEPARTMENT_ID ASC;


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


--查询收入最低的前5名员工的last_name,salary
--Oracle中TOP N的操作
SELECT LAST_NAME,SALARY
FROM (SELECT LAST_NAME,SALARY
      FROM EMPLOYEES
      ORDER BY SALARY ASC)
WHERE ROWNUM<=5;


--SQL Server中TOP N的操作
SELECT TOP 5 LAST_NAME,SALARY
FROM EMPLOYEES
ORDER BY SALARY ASC;


--MySQL中TOP N的操作
SELECT LAST_NAME,SALARY
FROM EMPLOYEES
ORDER BY SALARY ASC
LIMIT 5,10;


--多行子查询:ALL,ANY,IN
--查询工资大于60号部门所有员工的工资的员工的last_name,salary,department_id
SELECT LAST_NAME,SALARY,DEPARTMENT_ID
FROM EMPLOYEES
WHERE SALARY>(SELECT MAX(SALARY)
      FROM EMPLOYEES
      WHERE DEPARTMENT_ID=60);


SELECT LAST_NAME,SALARY,DEPARTMENT_ID
FROM EMPLOYEES
WHERE SALARY>ALL(SELECT SALARY
         FROM EMPLOYEES
         WHERE DEPARTMENT_ID=60);


SELECT LAST_NAME,SALARY,DEPARTMENT_ID
FROM EMPLOYEES
WHERE SALARY>ANY(SELECT SALARY
         FROM EMPLOYEES
         WHERE DEPARTMENT_ID=60);


--只能判断是否相等
SELECT LAST_NAME,SALARY,DEPARTMENT_ID
FROM EMPLOYEES
WHERE SALARY IN (SELECT SALARY
         FROM EMPLOYEES
         WHERE DEPARTMENT_ID=60);


--成对子查询
--查询每个部门收入最高的员工的last_name,department_id,salary
SELECT LAST_NAME,DEPARTMENT_ID,SALARY
FROM EMPLOYEES
WHERE (NVL(DEPARTMENT_ID,0),SALARY) IN (SELECT NVL(DEPARTMENT_ID,0),MAX(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID);


-- 相关子查询(关联子查询):先执行主查询，再执行子查询
--查询哪些员工的工资大于其所在部门的平均工资，显示：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);


--集合运算：并集，交集，补集
--并集：UNION/UNION ALL
SELECT LAST_NAME AS A,SALARY
FROM EMPLOYEES
WHERE SALARY=(SELECT MAX(SALARY)
      FROM EMPLOYEES)
UNION
SELECT LAST_NAME AS B,SALARY
FROM EMPLOYEES
WHERE SALARY=(SELECT MIN(SALARY)
      FROM EMPLOYEES)
ORDER BY 2 ASC;


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


SELECT LAST_NAME,SALARY
FROM EMPLOYEES
UNION ALL
SELECT LAST_NAME,SALARY
FROM EMPLOYEES
UNION ALL
SELECT LAST_NAME,SALARY
FROM EMPLOYEES;


--交集：INTERSECT
SELECT L_R_ID
FROM LIBRARY
WHERE L_B_ID=449901
INTERSECT
SELECT L_R_ID
FROM LIBRARY
WHERE L_B_ID=112266;


--补集:MINUS
A:1 2 3 4 5 6
B:2 3 7
A - B = 1 4 5 6
B - A = 7

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

1：

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;

2：
SELECT R,LAST_NAME,SALARY
FROM (SELECT ROW_NUMBER() OVER(ORDER BY SALARY DESC) AS R,LAST_NAME,SALARY
      FROM EMPLOYEES)
WHERE R BETWEEN 6 AND 10;

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;