Oracle用法、函數備忘記錄

標籤：

Listagg

select*from emp

select LISTAGG(ename,‘-‘)withingroup(orderby deptno desc)from emp;

可以看到功能類似wm_concat，可以自訂串連符，區別：

LISTAGG? : 11g2才提供的函數，不支援distinct,拼接長度不能大於4000,函數返回為varchar2類型，最大長度為4000.

和wm_concat相比，listagg可以執行排序。例如
select deptno, listagg(ename,‘;‘)withingroup(orderby ename) enames from emp groupby deptno;

with table as

?SQL Code?

1 2 3 4 5 6 7 8 9 10 11 12 13

?

with?temp?as( ??select?500?population,?‘China‘?nation?,‘Guangzhou‘?city?from?dual?union?all ??select?1500?population,?‘China‘?nation?,‘Shanghai‘?city?from?dual?union?all ??select?500?population,?‘China‘?nation?,‘Beijing‘?city?from?dual?union?all ??select?1000?population,?‘USA‘?nation?,‘New?York‘?city?from?dual?union?all ??select?500?population,?‘USA‘?nation?,‘Bostom‘?city?from?dual?union?all ??select?500?population,?‘Japan‘?nation?,‘Tokyo‘?city?from?dual? ) select?population, ???????nation, ???????city, ???????listagg(city,‘,‘)?within?GROUP?(order?by?city)?over?(partition?by?nation)?rank from?temp

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

wherenotexists(select s_name from sql1 whererownum=1))

select*

from sql1

unionall

select*

from sql2

pivot unpivot

行列轉換，見

Oracle行轉列、列轉行的Sql語句總結

Oracle用法、函數備忘記錄