Oracle、达梦、SQL server或者MySQL字符串列拆分成行(列转行)的几种方式

Oracle、达梦、SQL server或者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

 

SQL server列转行

create table test(
	id int identity(1,1) primary key,
	name varchar(50),
	age int
)
insert into test values('刘备',40),('关羽',40),('张飞',36),('诸葛亮',32)
select * from test;

　　SQL server 2017+

select age, string_agg(name,',') as names from test group by age
-- 按Id倒叙排列
select age, string_agg(name,',') within group(order by id desc) as names from test group by age

　　SQL server 2017以下

SELECT
    age,
    names = (STUFF((SELECT ',' + name FROM test WHERE age = A.age FOR xml path('')),1,1,''))
FROM test A
GROUP by age