--根据某一列中包括的逗号将一行数据变多行
select a,c
from (with test as (select 'abc' a,'1,2,3' c from dual e)
select a,substr(t.ca,
instr(t.ca, ',', 1, c.lv) + 1,
instr(t.ca, ',', 1, c.lv + 1) -
(instr(t.ca, ',', 1, c.lv) + 1)) AS c
from (select
a,
',' || c || ',' AS ca,
length(c || ',') - nvl(length(REPLACE(c, ',')), 0) AS cnt
FROM test) t,
(select LEVEL lv from dual CONNECT BY LEVEL <= 100) c
where c.lv <= t.cnt);
--sql分组取第一条
select *
from (select emp.*,row_number() over(partition by deptno order by rownum) cn from emp)
where cn = 1;