Oracle高级查询总结

高级查询总结

A．层次查询：start with……connec by prior……..

select lpad(' ',3*level)||ename,job,level from emp

start with mgr is null

connect by prior empno=mgr;

从根结点向下查，lpad()为左添加，level为第几层，prior为查找的方向；此句若省掉start with….则表示要遍历整个树型结构；若出现level，则后面一定要跟connect by

B.交叉报表(case when then end 与decode())

select ename,case when sal>=1500then sal*1.01

else sal*1.1

end工资

from emp;

select姓名,

max(case课程when'语文'then分数end) 语文,

max(case课程when'数学'then分数end) 数学,

max(case课程when'历史'then分数end) 历史

from学生group by姓名;------(交叉报表与函数一起使用)

select ename,sum(decode(sal,'sal',comm,null)) 奖金from emp

group by ename;--可实现分支

decode(条件,(值),(返回值),…………,(默认值)) 部门

select sal,decode(sign(sal-1500),-1,1.1*sal,0,1.1*sal,1,1.05*sal) from emp;

C.连接查询

1．等值：

select * from emp,dept where emp.deptno(+)=dept.deptno;

‘+’在左则以右表为主，反之亦然

2.非等值：where的运算符不是等号

3.自然连接:

select * from emp natural join dept

4.99乘法:

select * from emp e full join dept d

using (deptno) where deptno=10; --where必须放在using(on)的后面

D集合查询：

1．A Union B:消除重复行，有all则可以重复,默认第一列升序select ename,sal from deptno=20

union

select ename,sal from job=’CLERK’;

2．A intersect B:A与B的交集

select ename,sal from deptno=20

intersect

select ename,sal from job=’CLERK’;

3.A minus B:在A中减去既属于A又属于B的一部分

select ename,sal from deptno=20

minus

select ename,sal from job=’CLERK’;

单行子查询：

In：匹配子句查询结果任意一个即可

All: 必须符合子句查询结果的所有值

Any: 只要符合查询结果的任意一个即可

多行子查询：

(ename,job) 操作符（in,all,any）子句组合

分析函数

E cube与rollback的区别:(select5)

cube(deptno,job)/*等价*/grouping sets((deptno,job),(deptno),(job),()); rollback(deptno,job)/等价/grouping sets((deptno,job),(deptno),());

--partition by(分析分组函数)：

select emp.*,sum(sal)over(partition by deptno) from emp

/*window子句（窗口子句）*/--rows n preceding

select deptno,sal,sum(sal)over(order by sal rows2 preceding) 二行和from emp;

--range unbounded preceding(只对日期和数字有效 )

select deptno,sal,sum(sal)over(order by sal range unbounded preceding)from emp;

--rank(),dense_rank()

select deptno,ename,sal,rank()over(partition by deptno order by sal)跳,dense_rank()over(partition by deptno order by sal) 不跳--有波动总数据有可能减少from emp;

--row_number()按照分组从上到下排序

select deptno,ename,sal,row_number()over(partition by deptno order by sal)from emp

--lag(上一个) lead(下一个)

select deptno,ename,sal,

lag(sal)over(partition by deptno order by sal) 上一个,

lead(sal)over(partition by deptno order by sal) 下一个

from emp