Oracle Between用法
SELECT *
FROM AAA
WHERE create_date BETWEEN TO_DATE ('2019/01/01', 'YYYY/MM/DD')
AND TO_DATE ('2019/12/31 23:59:59','YYYY/MM/DD HH24:MI:SS');
Oracle 合併欄位
select wm_concat(column) from table;
Listagg():
轉自
https://www.itread01.com/content/1547124326.html
一般用法
with temp as(
select 'China' nation ,'Guangzhou' city from dual union all
select 'China' nation ,'Shanghai' city from dual union all
select 'China' nation ,'Beijing' city from dual union all
select 'USA' nation ,'New York' city from dual union all
select 'USA' nation ,'Bostom' city from dual union all
select 'Japan' nation ,'Tokyo' 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, '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
