Oracle用法、函数备忘记录

select LISTAGG(ename,‘-‘) within group (order by deptno desc) from emp;

可以看到功能类似wm_concat，可以自定义连接符，区别：

LISTAGG? : 11g2才提供的函数，不支持distinct,拼接长度不能大于4000,函数返回为varchar2类型，最大长度为4000.

和wm_concat相比，listagg可以执行排序。例如
select deptno, listagg(ename,‘;‘) within group(order by ename) enames from emp group by deptno;

with table as

?SQL Code?

With table as 类似创建一个临时表，只可以查询一次，之后就被销毁，同时可以创建多个临时table，比如：

with sql1 as

(select to_char(a) s_name from test_tempa),

sql2 as

(select to_char(b) s_name

from test_tempb

where not exists (select s_name from sql1 where rownum = 1))

select *

from sql1

union all

select *

from sql2

pivot unpivot

行列转换，见

Oracle行转列、列转行的Sql语句总结

Oracle用法、函数备忘记录

标签：