ORACLE Unit04 SQL（高级查询）

查看SMITH的上司在那个城市工作?

SELECT e.ename,m.ename,d.locFROM emp e,emp m,dept dWHERE e.mgr=m.empnoAND m.deptno=d.deptnoAND e.ename='SMITH'SELECT e.ename,m.ename,d.locFROM emp e JOIN emp mON e.mgr=m.empnoJOIN dept dON m.deptno=d.deptnoWHERE e.ename='SMITH'

子查询

子查询是一条查询语句，它是嵌套在其他SQL语句之中的，目的是为外层的SQL语句提供数据。
DDL,DML,DQL都可以使用子查询。

查看谁的工资高于CLARK？

SELECT ename,salFROM empWHERE sal>(SELECT sal FROM emp           WHERE ename='CLARK')

查看与SMITH同部门的员工?

SELECT ename,deptnoFROM empWHERE deptno=(SELECT deptno              FROM emp              WHERE ename='SMITH')

查看谁的工资高于公司平均工资?

SELECT ename,salFROM empWHERE sal>(SELECT AVG(sal) FROM emp)

DDL中使用子查询，可以根据子查询的结果集当作表快速创建出来。

创建一张表，包含员工的工号，名字，工资，职位，部门号，部门名以及所在地
该表中的数据来自emp,dept

CREATE TABLE employeeASSELECT e.empno,e.ename,e.sal,       e.job,e.deptno,d.dname,       d.locFROM emp e,dept dWHERE e.deptno=d.deptno(+)DESC employeeSELECT * FROM employee

DML中使用子查询

将SMITH所在部门的员工工资提高10%

UPDATE empSET sal=sal*1.1WHERE deptno=(SELECT deptno              FROM emp              WHERE ename='SMITH')SELECT * FROM emp

查看与MANAGER同部门的其他职位员工

SELECT ename,job,deptnoFROM empWHERE deptno IN(SELECT deptno FROM emp                WHERE job='MANAGER')AND job<>'MANAGER'

查看比20和30号部门员工工资都高的员工信息?

SELECT ename,salFROM empWHERE sal>ALL(SELECT sal FROM emp              WHERE deptno IN(20,30))

EXISTS关键字

EXISTS用在过滤条件中，其后跟一个子查询，只要该子查询可以查询出至少一条记录，那么EXISTS就认为满足条件。

SELECT deptno, dname FROM dept dWHERE EXISTS       (SELECT * FROM emp e       WHERE d.deptno = e.deptno)

HAVING中使用子查询

查看部门的最低薪水，前提是该部门最低薪水要高于30号部门的最低薪水。

SELECT MIN(sal),deptnoFROM empGROUP BY deptnoHAVING MIN(sal)>(SELECT MIN(sal)                  FROM emp                 WHERE deptno=30)

FROM子句中使用子查询

通常一个多列子查询是当作一张表看待出现在FROM子句中的。

查看谁的工资高于其所在部门的平均工资?

SELECT e.ename,e.sal,e.deptnoFROM emp e,(SELECT AVG(sal) avg_sal,                   deptno            FROM emp            GROUP BY deptno) tWHERE e.deptno=t.deptnoAND e.sal>t.avg_salSELECT   e.ename, e.sal,   (SELECT d.dname FROM dept d    WHERE d.deptno = e.deptno) dnameFROM emp e

分页查询

当查询的数据量非常大的时候，会导致系统资源消耗大，传输速度慢，用户实际也无需这么多数据量。为此，通常的操作是”分段查询”即分页。
由于标准SQL中没有对分页语法进行规定，所以分页查询是”方言”，不同数据库分页查询的语句不同。

ROWNUM:伪列

ROWNUM不存在于任何表，但是任何表都可以查询该字段，该字段的值是查询结果集中每条记录的行号。
ROWNUM的值是动态生成的，在查询过程中进行，每当可以从表中查询出一条记录，ROWNUM字段的值就是该记录的行号，从1开始，逐次递增。
在使用ROWNUM对结果集编行号的过程中不要使用ROWNUM做>1以上数字的判断，否则将得不到任何结果。

取6-10的员工？

SELECT *FROM (SELECT ROWNUM rn,ename,sal,             job,deptno      FROM emp      )WHERE rn BETWEEN 6 AND 10

查看公司中工资排名的6-10

SELECT *FROM(SELECT ROWNUM rn,t.*     FROM(SELECT ename,sal,deptno          FROM emp          ORDER BY sal DESC) t)WHERE rn BETWEEN 6 AND 10SELECT *FROM(SELECT ROWNUM rn,t.*     FROM(SELECT ename,sal,deptno          FROM emp          ORDER BY sal DESC) t     WHERE ROWNUM<=10)WHERE rn>=6

page:页码
pageSize:每页的条目数

start:(page-1)*pageSize+1end:pageSize*page

DECODE函数

SELECT   ename, job, sal,  DECODE(job,           'MANAGER',sal*1.2,         'ANALYST',sal*1.1,         'SALESMAN',sal*1.05,          sal) bonusFROM emp

将职位是MANAGER与ANALYST看作一组，其他职位看作另一组，分别统计人数。

SELECT COUNT(*),jobFROM empGROUP BY jobSELECT COUNT(*),       DECODE(job,              'MANAGER','VIP',              'ANALYST','VIP',              'OTHER')FROM empGROUP BY DECODE(job,              'MANAGER','VIP',              'ANALYST','VIP',              'OTHER')

排序函数

排序函数可以按照给定字段对结果集分组然后在组内排序，并生成组内编号。

ROW_NUMBER生成组内连续且唯一的数字

查看每个部门的工资排名

SELECT  ename,sal,deptno, ROW_NUMBER() OVER(  PARTITION BY deptno  ORDER BY sal DESC ) rankFROM emp

RANK函数:生成组内不连续也不唯一的数字

SELECT ename,sal,deptno, RANK() OVER(  PARTITION BY deptno  ORDER BY sal DESC ) rankFROM emp

DENSE_RANK:生成组内连续但不唯一的数字

SELECT ename,sal,deptno, DENSE_RANK() OVER(  PARTITION BY deptno  ORDER BY sal DESC ) rankFROM empSELECT year_id,month_id,day_id,sales_valueFROM sales_tabORDER BY year_id,month_id,day_id

每天营业额?

SELECT year_id,month_id,day_id,SUM(sales_value)FROM sales_tabGROUP BY year_id,month_id,day_idORDER BY year_id,month_id,day_id

每月营业额?

SELECT year_id,month_id,SUM(sales_value)FROM sales_tabGROUP BY year_id,month_idORDER BY year_id,month_id

每年营业额?

SELECT year_id,SUM(sales_value)FROM sales_tabGROUP BY year_idORDER BY year_id

总共营业额?

SELECT SUM(sales_value)FROM sales_tab

查看每天，每月，每年以及所有营业额?

SELECT year_id,month_id,day_id,SUM(sales_value)FROM sales_tabGROUP BY year_id,month_id,day_idUNION ALLSELECT year_id,month_id,NULL,SUM(sales_value)FROM sales_tabGROUP BY year_id,month_idUNION ALLSELECT year_id,NULL,NULL,SUM(sales_value)FROM sales_tabGROUP BY year_idUNION ALLSELECT NULL,NULL,NULL,SUM(sales_value)FROM sales_tab

高级分组函数

GROUP BY ROLLUP(a,b,c)
等同于:
GROUP BY a,b,c
UNION ALL
GROUP BY a,b
UNION ALL
GROUP BY a
UNION ALL
全表

SELECT year_id,month_id,day_id,       SUM(sales_value)FROM sales_tabGROUP BY     ROLLUP(year_id,month_id,day_id)

CUBE()函数

每种参数组合都进行一次分组，并在一个结果集
显示
分组次数为2的参数个数次方
GROUP BY CUBE(a,b,c)
abc
ab
ac
bc
a
b
c
全表

SELECT year_id,month_id,day_id,       SUM(sales_value)FROM sales_tabGROUP BY     CUBE(year_id,month_id,day_id)ORDER BY year_id,month_id,day_id

GROUPING SETS()

可以指定分组方式，然后将这些分组统计的结果并在一个结果集里显示

查看每天与每月的营业额?

SELECT year_id,month_id,day_id,       SUM(sales_value)FROM sales_tabGROUP BY     GROUPING SETS(     (year_id,month_id,day_id),     (year_id,month_id)     )ORDER BY year_id,month_id,day_id