sql - 如何将字符串值拆分为一列并返回结果表

Question

假设我们有下表:

id name   member
1  jacky  a;b;c
2  jason  e
3  kate   i;j;k
4  alex   null

现在我想使用sql或t-sql返回下表:

1 jacky a
1 jacky b
1 jacky c
2 jason e
3 kate  i
......

如何做到这一点？我使用的是 MSSQL、MYSQL 和 Oracle 数据库。

Answer 1

这是人们可以设计的最短和可读字符串到行拆分器，并且可以更快 也是。

选择纯 CTE 而不是函数的用例，例如当您不允许在数据库上创建函数时:-)

通过函数创建行生成器(也可以使用循环或CTE实现)仍然需要使用横向连接(DB2和Sybase有此功能，使用LATERAL关键字；在SQL Server中，这类似于CROSS APPLY和 OUTER APPLY)最终将函数生成的拆分行连接到主表。

纯 CTE 方法可能比函数方法更快。不过，速度指标在于分析，只需检查与其他解决方案相比的执行计划是否确实更快:

with Pieces(theId, pn, start, stop) AS
(
      SELECT id, 1, 1, charindex(';', member)
      from tbl

      UNION ALL

      SELECT id, pn + 1, stop + 1, charindex(';', member, stop + 1)
      from tbl 
      join pieces on pieces.theId = tbl.id 
      WHERE stop > 0
)
select 

      t.id, t.name, 

      word = 
         substring(t.member, p.start,             
           case WHEN stop > 0 THEN p.stop - p.start 
           ELSE 512 
           END) 

from tbl t
join pieces p on p.theId = t.id
order by t.id, p.pn 

输出:

ID  NAME    WORD
1   jacky   a
1   jacky   b
1   jacky   c
2   jason   e
3   kate    i
3   kate    j
3   kate    k
4   alex    (null)

基本逻辑源自此处:T-SQL: Opposite to string concatenation - how to split string into multiple records

现场测试:http://www.sqlfiddle.com/#!3/2355d/1