你的位置:首页 > 数据库

[数据库]Oracle用法、函数备忘记录

Listagg

select * from emp

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 

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

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

select *

from sql1

union all

select *

from sql2

pivot unpivot

行列转换,见

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