有Oracle特色的sql語句整理

我們知道每個RDBMS在sql方面都會存在自己的特色。那麼今天我們來看看Oracle有啥特色值得我們來在意。

特色1 ：
Oracle分析函數與開窗函數：
文法：

    FUNCTION_NAME  (<argument>,<argument>...)
    OVER           (<Partition-Clause><Order-by-Clause><Windowing Clause>)
例如：
   sum(sal) over (partition by deptno order by ename rows between。。。)
   其中，sum是函數名，
         Over（）是關鍵字，直接點就是給分析函數加條件,用於識別sum（）是彙總函式還是分析函數
說明：
（1）通常在做統計分析時我們都想儘可能多滴選擇出原始列和統計值列,但是這樣group by後面就必須跟隨更多的列,流量分析函數可以避免使用group by時選擇出來的列名必須出現在group by列表中的痛苦

（2）彙總函式用group by分組，每個分組返回一個統計值；分析函數用partition by分組，每組每行都可返回一個統計值。

（3）分析函數帶有一個開窗函數over（），含三個分析字句：
           分組（partition by）排序（order by）視窗（rows）

（4）兩個order by的區別：

        分析函數是在整個sql查詢後（sql語句的執行比較特殊）再進行的操作，也就是說，sql語句的order by也會影響分析函數的執行結果。

            A） 如果sql語句中的order by滿足分析函數分析時要求的排序，那麼sql語句的排序將先執行，分析函數在分析時就 不必再排序了。

            B） 如果sql語句中的order by不滿足分析函數分析時要求的排序，那麼sql語句中的排序將先執行。

（5）視窗就是分析函數分析時要處理的資料範圍：

           第一行是：unbounded preceding

           當前行是：current row

           最後一行是：unbounded following

      視窗字句不能單獨出現，必須有order by子句時才能出現。而出現order by子句時，不一定要有視窗子句，此時的視窗預設是第一行到最後一行；

           當省略視窗子句時：

           A）如果存在order by，則預設的視窗是unbounded preceding
And current row；

           B）如果同時省略order by，則預設的視窗是unbounded preceding and unbounded

following

例子：

1 統計每個部門工資最高的哪位？

select * from                                                                      

   (                                                                           

    select ename,sal,deptno,rank()over(partition by deptno order by sal desc) mm from emp

   )                                                                           

where mm=1

 

注意：
      1）.在求第一名成績的時候，不能用row_number()，因為如果同班有兩個並列第一， 

row_number()只返回一個結果         
      2）.rank()和dense_rank()的區別是：
     --rank()是跳躍排序，有兩個第二名時接下來就是第四名
     --dense_rank()l是連續排序，有兩個第二名時仍然跟著第三名

 
2  顯示各部門員工的工資，並附帶該部門的最高工資。

select deptno,empno,ename,sal,last_value(sal)over(partition by deptno order by sal rows

between unbounded preceding and unbounded following)

max_sal from emp;

1 2 3 4 下一頁