Java39: 資料庫三(Oracle)

標籤：oracle   資料庫   管理   

Oracle 進階查詢

1 集合運算

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

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

    minus 減去 前面的結果減去後面的結果

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

依託於該文法，我們可以將一個表形結構的以樹的順序列出來

提供一個偽列 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進階分組函數

rollup 函數 小計

rollup 函數多一行 小計

對於分組的列為null

對於聚集合函式為求 小計

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);

統計rollup(x,y) 統計第一個x 不統計y

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

cube 統計所有的列

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

rollup 和cube 就是匯總的結果

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);

如果需要查詢的結果只有小計 可以用cube 和 grouping sets 用grouping sets 的效率高於cube 和rollup

次序函數

    既能排序又能排名

rank() over( orader by xx)

重複就會削去下一個編號

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

dense_rank() over(orader by xx)

重複不削去編號

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

先排序在rownum  比rownum 簡單

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

本文出自 “浪漫的偷笑” 部落格，請務必保留此出處http://lmdtx.blog.51cto.com/6942028/1836807

Java39: 資料庫三(Oracle)