oracle蛋疼的sql

1：截取某字段最后一位 substr(字段名, -1)

select substr('abcdefg', -1) from dual

2：字符串拼接 wm_concat

select substr(name, -1) from user

3： oracle合并行为一列 最后想要的效果：

001        n1;n2;n3;n4;n5
002        m1
003        t1;t2;t3;t4;t5;t6

myslq：selecct group_concat(name),pk_id from task group by name;

oracle:

select t.*,
       (
        select max(sys_connect_by_path(q,';')) result from 
               (select no,q,rn,lead(rn) over(partition by no order by rn) rn1 
               from (select no,q,row_number() over(order by no,q desc) rn from test)
               )
        start with no = t.no and rn1 is null connect by rn1 = prior rn
       ) value
from (select distinct no from test)  t
 

4： 拆分某列数据为多行

SELECT DISTINCT ID, REGEXP_SUBSTR (字段名, '[^,]+', 1, LEVEL) 别名, STUSEX FROM T_STU CONNECT BY LEVEL <= LENGTH (字段名) - LENGTH (REPLACE(字段名, ',', '')) + 1 ORDER BY ID;

转载于:https://my.oschina.net/marlon520/blog/1863857