OracleSQL的高级查询、分页查询讲解

1.子查询

子查询是一条SELECT语句，但它是嵌套在其他SQL语句中的，为的是给该SQL提供数据以支持其执行操作。

查看谁的工资高于CLARK？

select ename,sal from emp where sal>(select sal from emp where ename=’clark’)

查看与CLARK 同职位的员工？

select ename,job from emp where job=(select job from emp where ename=’clark’)

查看与CLARK 同部门的员工？

select ename,deptno from emp where deptno=(select deptno from emp where ename=’clark’)

创建表employee，表中字段为：

empno,ename,job,sal,deptno,dname,loc

数据为现有表中emp和dept对应的数据

create table employee as

select e.empno,e.ename,e.job,e.sal,e.deptno,d.dname,d.loc

from emp e,dept d

where e.deptno=d.deptno(+)

 

创建表时若子查询中的字段有别名则该表对应的字段就使用该别名作为其字段名，当子查询中一个字段含有函数或表达式，那么该字段必须给别名

create table employee as

select e.empno id,e.ename name,e.job,e.sal,e.deptno,d.dname,d.loc

from emp e,dept d

where e.deptno=d.deptno(+)

将CLARK所在部门的所有员工删除

delete from employee where deptno=(select deptnp from employee where name=’clark’)

2.子查询的类型

子查询常用于SELECT 语句中，子查询根据查询结果集的不同分为：

单行单列子查询：常用于过滤条件，可以配合=，>,>=,<,<=使用

多行单列子查询：常用于过滤条件，由于查询出多个值，在判断=时要用IN，

判断>,>=等操作要配合ANY，AOR

多行多列子查询：常当作一张表来看待。

查询与salesman同部门的其他职位的员工：

select ename,job,deptno from emp where deptno in(

select deptno from emp where job=’salesman’

) and job<>’salesman’

查看比职位是clerk和salesman工资都高的员工

select ename,sal from emp where sal >ALL(

select sal from emp where job in(‘clerk’,’salesman’)

)

3.EXISTS关键字

exists后面跟一个子查询，当该子查询可以查询出一条记录时，则exists表达式成立并返回true。

select deptno,dname from dept d where exists(select *from emp e where  d.deptno=e.deptno)

查看每个部门的最低薪水是多少？前提是该部门的最低薪水要高于30号部门的最低薪水。

老办法：

(select min(sal),deptno from emp group by deptno )

(having min(sal>950))

新办法：

select min(sal),deptno from emp group by deptno having min(

select min(sal) from emp group by deptno

)

查看比自己所在部门平均工资高的员工？

老办法：

（select avg(sal),deptno from emp group by deptno）

(select e.ename,e.sal,e.deptno from emp e,ttt t where e.deptno=t.deptno and e.sal>t.avg(sal))

新办法：

select e.ename,e.sal,e.deptno from emp e,（select avg(sal) avg_sal,deptno from emp group by deptno） t where e.deptno=t.deptno and e.sal>t.avg_sal

在SELECT 子句中使用子查询，可以将查询的结果当作外层查询记录中的一个字段值显示

select e.ename,e.sal,(select d.dname from dept d where d.deptno=e.deptno) dep from emp e

4.分页查询

分页查询是将查询表中数据时分段查询，而不是一次性将所有数据查询出来。有时查询的数据量非常庞大，这回导致系统资源消耗大，相应速度长，数据冗余严重。为此当遇到这种情况时一般使用分页查询解决。数据库基本都支持分页，但是不同数据库语法不同（方言）。

ORACLE中的分页是基于伪列ROWNUM实现的。

ROWNUM不存在与任何一张表中，但是所有的表都可以查询该字段，该字段的值是随着查询自动生成的，该字段值即为该条记录的的行号，从1开始，逐条递增。

在使用ROWNUM对结果集进行编码的查询过程中不能使用ROWNUM做>1的数字判断，否则将查询不出任何数字。

select * from(select rownum rn,empno,ename,sal,job from emp) where rn between 6 and 10.

注意：先用rownum把号都编好，当成一张表

例子：查看工资排名的6-10(嵌套两套子查询：排序、编号、查询)

select *

from( select rownum rn, t.*

from (select empno,ename,sal from emp order by sal desc）t

)

where rn between 6 and 10

提高效率

select *

from(select rownum rn,t.*

from(select empno,ename,sal from emp order by sal desc) t

where rownum<=10)

where rn>=6

from(select rownum rn,t.*

from(select empno,ename,sal from emp order by sal desc) t

where rownum<=10)

where rn>=6

计算区间：

pageSize:每页显示IDE条目数

page：页数

star:(page-1)*pageSize+1

end:pageSize*page;

5.DECODE函数

select ename,job,sal,

decode(job,’MANAGER’,sal*1.2,

‘ANALYST’,sal*1.1,

‘SALESMAN’,sal*1.15,

sal

) bonus

        from emp;

和DECODE函数功能相似的有CASE语句

select ename,job,sal,

CASE job WHEN ’MANAGER’ THEN sal*1.2

WHEN‘ANALYST’THEN sal*1.1,

WHEN‘SALESMAN’THEN sal*1.15,

ELSE sal END

 bonus

        from emp;

DEOCE在GROUP BY分组中的应用可以将字段值不同的记录看做一组。

统计人数，将职位是“MANAGER”,“ANALYST”看做一组，其余职业看做另外一组分别统计人数。

select

count(*),decode(job,

'MANAGER','VIP',

'AVALYST','VIP',

'OTHER')

 from emp

 group by decode(job,

'MANAGER','VIP',

'AVALYST','VIP',

'OTHER')

6.排序函数

排序函数允许对结果集按照指定的字段分组，在组内再按照指定的字段排序，最终生成组内编号。

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

查看每个部门的工资排名？

select ename,sal,deptno,

row_number() over(

PARTITION BY deptno

ORDER BY sal DESC

  )rank

FROM emp

RANK函数，生成组内不连续也不唯一的数字，同组内排序字段值一样的记录，生成的数字一样。

查看每个部门的工资排名？

select ename,sal,deptno,

RANK() over(

PARTITION BY deptno

ORDER BY sal DESC)

  )rank

FROM emp

 

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

select ename,sal,deptno

DENSE_RANK() OVER(

PARTITION BY deptno

ORDER BY sal DESC

)rank

from emp

7.高级分组函数

合并职位是'MANAGER'的员工和薪水大于2500的员工集合，查看两种方式的结果差别
select ename,job,sal from emp 
	where job='MANAGER'
	UNION	
select ename,job,sal from emp
	where sal>2500;

select ename,job,sal from emp
	where job='MANAGER'
	UNION ALL
select ename,job,sal from emp
	where sal>2500;

交集
SELECT ename,job,sal from emp
	where job='MANAGER'
	INTERSECT
SELECT ename,job,sal,from emp
	where sal>2500;

差集
SELECT ename,job,sal, from emp
	where job='MANAGER'
	MINUS
SELECT ename,job,sal from emp
	where sal>=2500;

高级分组函数用在GROUP BY 子句中，每个高级分组函数都有一套分组策略。

ROLLUP()：分组原则，参数逐次递减，一直到所有参数都不要，每一种分组都统计一次结果，并且并在一个结果集显示。

CUBE():每种组合分一次组，分组次数：2的参数个数次方