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)