oracle：函数

列转行：

select  listagg(r.name,',') within group (order by (r.id))  from test1 r ,test2 ur where r.id = ur.rol_id;

wm_cancat函数行转列后，不会按照原有查询结果排序。listagg函数行转列后，会按照原有查询结果顺序排列。

  如果考虑到需要行转列，并且保持分组后顺序不变可以使用listagg来完成。

with temp as(
select '中国' nation ,'江苏' city from dual union all
select '中国' nation ,'上海' city from dual union all
select '中国' nation ,'北京' city from dual union all
select '美国' nation ,'纽约' city from dual union all
select '美国' nation ,'波士顿' city from dual union all
select '日本' nation ,'东京' city from dual
)
select nation,listagg(city,',') within GROUP (order by city) as Cities
from temp
group by nation;

with temp as(
select 500 population, '中国' nation ,'江苏' city from dual union all
select 1500 population, '中国' nation ,'上海' city from dual union all
select 500 population, '中国' nation ,'北京' city from dual union all
select 1000 population, '美国' nation ,'纽约' city from dual union all
select 500 population, '美国' nation ,'波士顿' city from dual union all
select 500 population, '日本' nation ,'东京' city from dual
)
select population,
nation,
city,
listagg(city,',') within GROUP (order by city) over (partition by nation) rank
from temp

加法 

 select sysdate,add_months(sysdate,12) from dual;        --加1年

 select sysdate,add_months(sysdate,1) from dual;        --加1月

 select sysdate,to_char(sysdate+7,'yyyy-mm-dd HH24:MI:SS') from dual;   --加1星期

 select sysdate,to_char(sysdate+1,'yyyy-mm-dd HH24:MI:SS') from dual;   --加1天

 select sysdate,to_char(sysdate+1/24,'yyyy-mm-dd HH24:MI:SS') from dual;  --加1小时

 select sysdate,to_char(sysdate+1/24/60,'yyyy-mm-dd HH24:MI:SS') from dual;  --加1分钟

 select sysdate,to_char(sysdate+1/24/60/60,'yyyy-mm-dd HH24:MI:SS') from dual;  --加1秒

减法

 select sysdate,add_months(sysdate,-12) from dual;        --减1年

 select sysdate,add_months(sysdate,-1) from dual;        --减1月

 select sysdate,to_char(sysdate-7,'yyyy-mm-dd HH24:MI:SS') from dual;   --减1星期

 select sysdate,to_char(sysdate-1,'yyyy-mm-dd HH24:MI:SS') from dual;   --减1天

 select sysdate,to_char(sysdate-1/24,'yyyy-mm-dd HH24:MI:SS') from dual;  --减1小时

 select sysdate,to_char(sysdate-1/24/60,'yyyy-mm-dd HH24:MI:SS') from dual;  --减1分钟

 select sysdate,to_char(sysdate-1/24/60/60,'yyyy-mm-dd HH24:MI:SS') from dual;  --减1秒