Oracle楂樼骇鏌ヨ涔婳VER (PARTITION BY ..)
涓€銆乺ank()/dense_rank() over(partition by ...order by ...)

鐜板湪瀹㈡埛鏈夎繖鏍蜂竴涓渶姹傦紝鏌ヨ姣忎釜閮ㄩ棬宸ヨ祫鏈€楂樼殑闆囧憳鐨勪俊鎭紝鐩镐俊鏈変竴瀹歰racle搴旂敤鐭ヨ瘑鐨勫悓瀛﹂兘鑳藉啓鍑轰笅闈㈢殑SQL璇彞锛?

 select e.ename, e.job, e.sal, e.deptno  
  from scott.emp e,  
       (select e.deptno, max(e.sal) sal from scott.emp e group by e.deptno) me  
 where e.deptno = me.deptno  
   and e.sal = me.sal; 

鍦ㄦ弧瓒冲鎴烽渶姹傜殑鍚屾椂锛屽ぇ瀹跺簲璇ヤ範鎯€х殑鎬濊€冧竴涓嬫槸鍚﹁繕鏈夊埆鐨勬柟娉曘€傝繖涓槸鑲畾鐨勶紝灏辨槸浣跨敤鏈皬鑺傛爣棰樹腑rank() over(partition by...)鎴杁ense_rank() over(partition by...)璇硶锛孲QL鍒嗗埆濡備笅锛?

select e.ename, e.job, e.sal, e.deptno  
  from (select e.ename,  
               e.job,  
               e.sal,  
               e.deptno,  
               rank() over(partition by e.deptno order by e.sal desc) rank  
          from scott.emp e) e  
 where e.rank = 1;  

select e.ename, e.job, e.sal, e.deptno  
  from (select e.ename,  
               e.job,  
               e.sal,  
               e.deptno,  
               dense_rank() over(partition by e.deptno order by e.sal desc) rank  
          from scott.emp e) e  
 where e.rank = 1;  

涓轰粈涔堜細寰楀嚭璺熶笂闈㈢殑璇彞涓€鏍风殑缁撴灉鍛紵杩欓噷琛ュ厖璁茶В涓€涓媟ank()/dense_rank() over(partition by e.deptno order by e.sal desc)璇硶銆?
over:  鍦ㄤ粈涔堟潯浠朵箣涓娿€?
partition by e.deptno:  鎸夐儴闂ㄧ紪鍙峰垝鍒嗭紙鍒嗗尯锛夈€?
order by e.sal desc:  鎸夊伐璧勪粠楂樺埌浣庢帓搴忥紙浣跨敤rank()/dense_rank() 鏃讹紝蹇呴』瑕佸甫order by鍚﹀垯闈炴硶锛?
rank()/dense_rank():  鍒嗙骇
鏁翠釜璇彞鐨勬剰鎬濆氨鏄細鍦ㄦ寜閮ㄩ棬鍒掑垎鐨勫熀纭€涓婏紝鎸夊伐璧勪粠楂樺埌浣庡闆囧憳杩涜鍒嗙骇锛屸€滅骇鍒€濈敱浠庡皬鍒板ぇ鐨勬暟瀛楄〃绀猴紙鏈€灏忓€间竴瀹氫负1锛夈€?
閭ｄ箞rank()鍜宒ense_rank()鏈変粈涔堝尯鍒憿锛?
rank():  璺宠穬鎺掑簭锛屽鏋滄湁涓や釜绗竴绾ф椂锛屾帴涓嬫潵灏辨槸绗笁绾с€?
dense_rank():  杩炵画鎺掑簭锛屽鏋滄湁涓や釜绗竴绾ф椂锛屾帴涓嬫潵浠嶇劧鏄浜岀骇銆?

灏忎綔涓氾細鏌ヨ閮ㄩ棬鏈€浣庡伐璧勭殑闆囧憳淇℃伅銆?

浜屻€乵in()/max() over(partition by ...)

鏌ヨ闆囧憳淇℃伅鐨勫悓鏃剁畻鍑洪泧鍛樺伐璧勪笌閮ㄩ棬鏈€楂?鏈€浣庡伐璧勭殑宸銆傝繖涓繕鏄瘮杈冪畝鍗曪紝鍦ㄧ涓€鑺傜殑groupby璇彞鐨勫熀纭€涓婅繘琛屼慨鏀瑰涓嬶細

select e.ename,  
         e.job,  
         e.sal,  
         e.deptno,  
         e.sal - me.min_sal diff_min_sal,  
         me.max_sal - e.sal diff_max_sal  
    from scott.emp e,  
         (select e.deptno, min(e.sal) min_sal, max(e.sal) max_sal  
            from scott.emp e  
           group by e.deptno) me  
   where e.deptno = me.deptno  
   order by e.deptno, e.sal;   

涓婇潰鎴戜滑鐢ㄥ埌浜唌in()鍜宮ax()锛屽墠鑰呮眰鏈€灏忓€硷紝鍚庤€呮眰鏈€澶у€笺€傚鏋滆繖涓や釜鏂规硶閰嶅悎over(partition by ...)浣跨敤浼氭槸浠€涔堟晥鏋滃憿锛熷ぇ瀹剁湅鐪嬩笅闈㈢殑SQL璇彞锛?

select e.ename,  
       e.job,  
       e.sal,  
       e.deptno,  
       nvl(e.sal - min(e.sal) over(partition by e.deptno), 0) diff_min_sal,  
       nvl(max(e.sal) over(partition by e.deptno) - e.sal, 0) diff_max_sal  
  from scott.emp e;   

杩欎袱涓鍙ョ殑鏌ヨ缁撴灉鏄竴鏍风殑锛屽ぇ瀹跺彲浠ョ湅鍒癿in()鍜宮ax()瀹為檯涓婃眰鐨勮繕鏄渶灏忓€煎拰鏈€澶у€硷紝鍙笉杩囨槸鍦╬artition by鍒嗗尯鍩虹涓婄殑銆?

涓夈€乴ead()/lag() over(partition by ... order by ...)
涓浗浜虹埍鏀€姣旓紝濂介潰瀛愶紝闂诲悕涓栫晫銆傚鎴锋洿鏄ソ杩欎竴鍙ｏ紝鍦ㄥ拰鏈€楂?鏈€浣庡伐璧勬瘮杈冨畬涔嬪悗杩樿寰椾笉杩囩樉锛岃繖娆″氨鎻愬嚭浜嗕竴涓瘮杈冨彉鎬佺殑闇€姹傦紝璁＄畻涓汉宸ヨ祫涓庢瘮鑷繁楂樹竴浣?浣庝竴浣嶅伐璧勭殑宸銆傝繖涓渶姹傜‘瀹炶鎴戝緢鏄负闅撅紝鍦╣roupby璇彞涓笉鐭ラ亾搴旇鎬庝箞鍘诲疄鐜般€備笉杩囥€傘€傘€傘€傜幇鍦ㄦ垜浠湁浜唎ver(partition by ...)锛屼竴鍒囩湅璧锋潵鏄偅涔堢殑绠€鍗曘€傚涓嬶細

select e.ename,  
       e.job,  
       e.sal,  
       e.deptno,  
       lead(e.sal, 1, 0) over(partition by e.deptno order by e.sal) lead_sal,  
       lag(e.sal, 1, 0) over(partition by e.deptno order by e.sal) lag_sal,  
       nvl(lead(e.sal) over(partition by e.deptno order by e.sal) - e.sal,  
           0) diff_lead_sal,  
       nvl(e.sal - lag(e.sal) over(partition by e.deptno order by e.sal), 0) diff_lag_sal  
  from scott.emp e;   

鎴戜滑杩樻槸鏉ヨ瑙ｄ竴涓嬩笂闈㈢敤鍒扮殑涓や釜鏂版柟娉曞惂銆?
lead(鍒楀悕,n,m):  褰撳墠璁板綍鍚庨潰绗琻琛岃褰曠殑<鍒楀悕>鐨勫€硷紝娌℃湁鍒欓粯璁ゅ€间负m锛涘鏋滀笉甯﹀弬鏁皀,m锛屽垯鏌ユ壘褰撳墠璁板綍鍚庨潰绗竴琛岀殑璁板綍<鍒楀悕>鐨勫€硷紝娌℃湁鍒欓粯璁ゅ€间负null銆?
lag(鍒楀悕,n,m)