Oracle或者MySQL字符串列拆分成行(列转行)的几种方式
本文连接:https://www.cnblogs.com/muphy/p/10781505.html
Oracle字符串拆分成行(列转行)的三种方式
--muphy
开发过程中经常会遇到将前台多个值用逗号连接一同传递到后台查询,这个用逗号连接的字符串分隔的每个字符串分别对应Oracle数据库表的不同行。
如下一个表table_test的内容如下:
name value
pa 5
pb 6
pc 8
需要查询分别与pa和pb相同的行,参数字符串为:
pi_names=”pa,pb”
如何查询呢,有以下三种方式(根据执行计划分析,效率由低到高排列):
1.使用Oracle Database 11g Release 2及更高版本时,可以使用递归with子查询:
with a(name, i) as (select regexp_substr(pi_names, '[^,]+') name, substr(pi_names || ',', instr(pi_names, ',') + 1) i from dual union all select regexp_substr(i, '[^,]+'), substr(i, instr(i, ',') + 1) from a where instr(i, ',') <> 0) select t.name, t.value from table_test t inner join a on a.name = t.name
2. 使用connect by语句实现之方式一:
with a as (select distinct regexp_substr(pi_names, '[^,]+', 1, level) name from dual connect by regexp_substr(pi_names, '[^,]+', 1, level) is not null ) select t.name, t.value from table_test t inner join a on a.name = t.name
3. 使用connect by语句实现之方式二:
with a as (select regexp_substr(pi_names, '[^,]+', 1, rownum) name from dual connect by rownum <= length(pi_names) - length(replace(pi_names, ',')) + 1) select t.name, t.value from table_test t inner join a on a.name = t.name
单纯的列转行常用sql语句如下
select regexp_substr(pi_names, '[^,]+', 1, rownum) name from dual connect by rownum <= length(pi_names) - length(replace(pi_names, ',')) + 1
MySQL列转行
借助 mysql.help_topic 实现,test表数据如下:
id device_id
1 xt,at
2 mt
select a.id, substring_index(substring_index(a.device_id,',',b.help_topic_id+1),',',-1) from test a join mysql.help_topic b on b.help_topic_id < (length(a.device_id) - length(replace(a.device_id,',',''))+1)
查询结果:
id device_id
1 xt
1 at
2 mt
本站文章如无特殊说明,均为本站原创,如若转载,请注明出处:Oracle或者MySQL字符串列拆分成行(列转行)的几种方式 - Python技术站