ORACLE Unit04 SQL（高级查询）

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

SELECT e.ename,m.ename,d.loc FROM emp e,emp m,dept d WHERE e.mgr=m.empno AND m.deptno=d.deptno AND e.ename='SMITH'SELECT e.ename,m.ename,d.loc FROM emp e JOIN emp m ON e.mgr=m.empno JOIN dept d ON m.deptno=d.deptno WHERE e.ename='SMITH'

子查询

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

查看谁的工资高于CLARK？

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

查看与SMITH同部门的员工?

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

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

SELECT ename,sal FROM emp WHERE sal>(SELECT AVG(sal) FROM emp)

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

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

CREATE TABLE employee AS SELECT e.empno,e.ename,e.sal,e.job,e.deptno,d.dname,d.loc FROM emp e,dept d WHERE e.deptno=d.deptno(+)DESC employee SELECT * FROM employee

DML中使用子查询

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

UPDATE emp SET sal=sal*1.1 WHERE deptno=(SELECT deptnoFROM empWHERE ename='SMITH')SELECT * FROM emp

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

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

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

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

EXISTS关键字

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

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

HAVING中使用子查询

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

SELECT MIN(sal),deptno FROM emp GROUP BY deptno HAVING MIN(sal)>(SELECT MIN(sal) FROM empWHERE deptno=30)

FROM子句中使用子查询

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

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

SELECT e.ename,e.sal,e.deptno FROM emp e,(SELECT AVG(sal) avg_sal,deptnoFROM empGROUP BY deptno) t WHERE e.deptno=t.deptno AND e.sal>t.avg_salSELECT e.ename, e.sal, (SELECT d.dname FROM dept d WHERE d.deptno = e.deptno) dname FROM emp e

分页查询

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

ROWNUM:伪列

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

取6-10的员工？

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

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

SELECT * FROM(SELECT ROWNUM rn,t.*FROM(SELECT ename,sal,deptnoFROM empORDER BY sal DESC) t) WHERE rn BETWEEN 6 AND 10SELECT * FROM(SELECT ROWNUM rn,t.*FROM(SELECT ename,sal,deptnoFROM empORDER BY sal DESC) tWHERE ROWNUM<=10) WHERE rn>=6

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

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

DECODE函数

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

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

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

排序函数

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

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

查看每个部门的工资排名

SELECT ename,sal,deptno,ROW_NUMBER() OVER(PARTITION BY deptnoORDER BY sal DESC) rank FROM emp

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

SELECT ename,sal,deptno,RANK() OVER(PARTITION BY deptnoORDER BY sal DESC) rank FROM emp

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

SELECT ename,sal,deptno,DENSE_RANK() OVER(PARTITION BY deptnoORDER BY sal DESC) rank FROM empSELECT year_id,month_id,day_id,sales_value FROM sales_tab ORDER BY year_id,month_id,day_id

每天营业额?

SELECT year_id,month_id,day_id,SUM(sales_value) FROM sales_tab GROUP BY year_id,month_id,day_id ORDER BY year_id,month_id,day_id

每月营业额?

SELECT year_id,month_id,SUM(sales_value) FROM sales_tab GROUP BY year_id,month_id ORDER BY year_id,month_id

每年营业额?

SELECT year_id,SUM(sales_value) FROM sales_tab GROUP BY year_id ORDER BY year_id

总共营业额?

SELECT SUM(sales_value) FROM sales_tab

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

SELECT year_id,month_id,day_id,SUM(sales_value) FROM sales_tab GROUP BY year_id,month_id,day_id UNION ALL SELECT year_id,month_id,NULL,SUM(sales_value) FROM sales_tab GROUP BY year_id,month_id UNION ALL SELECT year_id,NULL,NULL,SUM(sales_value) FROM sales_tab GROUP BY year_id UNION ALL SELECT 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_tab GROUP 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_tab GROUP 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_tab GROUP BY GROUPING SETS((year_id,month_id,day_id),(year_id,month_id) ) ORDER BY year_id,month_id,day_id