常用的一些sql

--根据某一列中包括的逗号将一行数据变多行

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;