Oracle 常用函數

1.LISTAGG

SELECT deptno,LISTAGG(ename,',')WITHIN GROUP(ORDER BY ename) AS employees
     FROM scott.emp GROUP BYdeptno;
對deptno欄位匯總，匯總後多個ename用','隔開

2.nvl/nvl2

--selectemployee_id,last_name,salary*12*(1+nvl(commission_pct,0)) from employees
//如果commission_pct存在，則為其自身，否則賦值為0；
--select employee_id,last_name,commission_pct,
nvl2(commission_pct,commission_pct+0.15,0.01) from employees
//如果commission_pct存在，則傳回值為commission_pct+0.15，否則為0.01
參數value2 value3可以是除了LONG類型之外的任意資料類型

3. case…when…then…when…then…else…end

select department_id,
case department_id
when 10 then salary*1.1
when 20 then salary*1.2
else salary*1.3 end as "new_sal" //賦別名
from employees where department_id in (10,20,30)

4.decode

selectemployee_id,decode(department_id,10,salary*1.1,20,salary*1.2,salary) new_sal
        from employees where department_idin(10,20,30)
//根據department_id進行判斷，如果為10，則其salary*1.1；如果為20，則其salary*1.2；其他salary不變，並賦別名--new_sal

5.to_char

select to_char(sysdate,'yyyy"年"mm"月"dd"日" HH:mi:ss') from dual
select to_char(1234567.89,'999,999,999.99') from dual

6.to_number

select to_number('1,234,567.89','999,999,999.99')+100from dual
//注意前後兩個數的取值範圍，後者一定要比前者大

7.|| 拼接符

select last_name || ' earns'||to_char(salary,'$99999')||' monthlym,but wants to earn'||
to_char(3*salary,'$99999') "Dream Salary" from employees
//控制台將會輸出一句話

8.group by…having

select department_id,min(salary) from employees
group by department_id
having min(salary) >

(select min(salary) from employees where department_id ='50' )

9.樹狀結構 start with connect by prior

參考: https://www.cnblogs.com/sunfie/p/5129716.html

select … from tablename

start with 條件1

connect by prior 條件2

where 條件3;

例: SELECT * FROM EMP

              STARTWITH EMPNO=7369

             CONNECT BY PRIOR EMPNO=MGR  --PID在前ID在後(向上查詢)

      簡單說來是將一個樹狀結構儲存在一張表裡，比如一個表中存在兩個欄位:org_id，parent_id，那麼通過表示每一條記錄的parent是誰，就可以形成一個樹狀結構，用上述文法的查詢可以取得這棵樹的所有記錄，其中：  

        條件1 是根結點的限定語句，當然可以放寬限定條件，以取得多個根結點，實際就是多棵樹。
        條件2 是串連條件，其中用PRIOR表示上一條記錄，比如 CONNECT BY PRIORorg_id = parent_id；就是說上一條記錄的org_id是本條記錄的parent_id，即本記錄的父親是上一條記錄。
       條件3 是過濾條件，用於對返回的所有記錄進行過濾。

10.組合函數Row_Number Over (Partition by (order by)) 組合函數

相同值的排在一起，分為一個塊，也就是分組，然後組內排序編號

11.小結函數 rollup 函數

select decode(grouping(job) + grouping(deptno), 1, '小計', 2, '總計', job) job,

decode(grouping(deptno),1,count(*)||'條',deptno)deptno

from emp

group by rollup(job, deptno);

12.分組後排序(類似 10 partition by)

Rank/dense_rank Over

Rank() Over(Partition By item_idOrder by fee Desc)