Oracle 常用函数总结-----(持续更新)

(一)分组聚合两种写法

listagg 函数

SELECT
    deptno,
    listagg (ename, ',') WITHIN GROUP (ORDER BY ename)
FROM
    emp
GROUP BY
    deptno;

运行结果

wm_concat 函数

SELECT
    deptno,
    wm_concat (ename)
FROM
    emp
GROUP BY
    deptno;

运行结果

(二)decode函数

定义 decode(col , val ,’val1’,’val2’)

当列 col 值为val 是显示 val1 ,否则显示val2

select * from dept;

select deptno ,dname ,loc ,decode(loc , 'NEW YORK','纽约','非纽约') from dept;

运行结果

(二) case when 函数

CASE WHEN condition THEN result [WHEN … THEN …] … [ELSE result] END

select ename , sal , 
case when sal >3000 then '一等'
when sal <=3000 and sal >2000 then '二等'
when sal <=2000 and sal >1000 then '三等'
else '四等' 
end 
as lev 
from emp ;

运行结果