Oracle 高級(jí)查詢

創(chuàng)新互聯(lián)專注于永靖企業(yè)網(wǎng)站建設(shè),成都響應(yīng)式網(wǎng)站建設(shè)公司,商城開(kāi)發(fā)。永靖網(wǎng)站建設(shè)公司,為永靖等地區(qū)提供建站服務(wù)。全流程定制開(kāi)發(fā)，專業(yè)設(shè)計(jì)，全程項(xiàng)目跟蹤，創(chuàng)新互聯(lián)專業(yè)和態(tài)度為您提供的服務(wù)

1 集合運(yùn)算

    union 并集 把兩張表合成一張表

    intersect 交集 一樣的留下，不一樣的不要

    minus 減去 前面的結(jié)果減去后面的結(jié)果

create table emp3 as 
select * from emp2 where deptno=20;
create table emp4 as 
select * from emp2 where deptno=30;

alter table emp3 rename to emp20;
alter table emp4 rename to emp30;

select * from emp20;
select * from emp30;

select * from emp20 union select * from emp30;

select * from emp30 intersect select * from emp2;

select * from emp2 minus select * from emp30;

2 connect by 和start with

依托于該語(yǔ)法，我們可以將一個(gè)表形結(jié)構(gòu)的以樹(shù)的順序列出來(lái)

提供一個(gè)偽列 level

level

找到頭
select ename from emp2 where mgr is null;
select empno,ename,mgr from emp start with ename='KING' connect by prior empno=mgr;

select level,empno,ename,mgr from emp start with ename=(select ename from emp2 where mgr is null) connect by prior empno=mgr;


select * from (select level lv,empno,ename,mgr from emp start with ename=(select ename from emp2 where mgr is null) connect by prior empno=mgr) where lv=2;

3高級(jí)分組函數(shù)

rollup 函數(shù) 小計(jì)

rollup 函數(shù)多一行 小計(jì)

對(duì)于分組的列為null

對(duì)于聚集函數(shù)為求 小計(jì)

select job,sum(sal) from emp GROUP BY rollup(job);

select job,sum(sal),round(avg(sal)),max(sal),count(empno)from emp group by rollup(job);

統(tǒng)計(jì)rollup(x,y) 統(tǒng)計(jì)第一個(gè)x 不統(tǒng)計(jì)y

select dname,job,sum(sal) from emp inner join dept using(deptno) group by rollup(dname,job);

cube 統(tǒng)計(jì)所有的列

select dname,job,sum(sal) from emp inner join dept using(deptno) froup by cube(dname,job) order by dname;

rollup 和cube 就是匯總的結(jié)果

grouping 和grouping sets 

select grouping(dname),dname ,grouping(job),job,sum(sal) from emp inner join dept using(deptno) group by rollup(dname,job) order by dname;

select * from (select grouping(dname),dname ,grouping(job) N,job,sum(sal) from emp inner join dept using(deptno) group by rollup(dname,job) order by dname) where dname='SALES' and N=1 ;

select grouping(dname),dname,grouping(job),job,sum(sal) from emp inner join dept using(deptno) group by cube(dname,job) order by dname;

只查詢匯總的行 總的匯總也不要

select dname,job,sum(sal) from emp inner join dept using(deptno) group by grouping sets(dname,job);

如果需要查詢的結(jié)果只有小計(jì) 可以用cube 和 grouping sets 用grouping sets 的效率高于cube 和rollup

排名函數(shù)

    既能排序又能排名

rank() over( orader by xx)

重復(fù)就會(huì)削去下一個(gè)編號(hào)

select rank() over(order by sal desc),ename,sal from emp;

dense_rank() over(orader by xx)

重復(fù)不削去編號(hào)

select dense_rank() over(order by sal desc),ename,sal from emp;

先排序在rownum  比rownum 簡(jiǎn)單

select row_number() over(order by sal desc),ename from emp;