逗号分隔id串列转行

 

1.oracle数据库的

 

select wm_concat(a.name) from table1 a where a.id in (
select
  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 ids
from (select
          ',' || ids|| ',' AS ca,
          length(ids|| ',') - nvl(length(REPLACE(ids, ',')), 0) AS cnt
     FROM table2 where hhoid = '10720150610000001') t,
  (select LEVEL lv from dual CONNECT BY LEVEL <= 10) c
where c.lv <= t.cnt
)

 

select bb.hhoid,wm_concat(aa.name) from table1 aa,(
select t.hhoid,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 ids
from ( select k.hhoid,',' || k.ids || ',' as ca, length(k.ids || ',') - nvl(length(replace(k.ids, ',')), 0) as cnt
from table2 k ) t,(select level lv from dual connect by level <= 10) c 
where c.lv <= t.cnt
)bb where aa.id = bb.ids and bb.hhoid = '10720150610000001'
group by bb.hhoid

 

 

 

 

 

 

 2.mysql

f1为 表table1的主键，f2为逗号分隔的字符串

 

select a.f1 ff1, substring_index(substring_index(a.f2,',',b.help_topic_id+1),',',-1)  ff2
from table1 a join mysql.help_topic b
on b.help_topic_id < (length(a.f2) - length(replace(a.f2,',',''))+1)
order by a.f1;