SQL SERVER：高效列转行方法对比——OUTERAPPLY vs. substring

在SQL SERVER数据库中，处理数据时常常需要将一列含有逗号分隔的字符串转换为行形式，这对于数据清洗和分析尤为重要。本文主要探讨了两种不同的列转行方法，并对它们的性能进行了对比。 第一种方法是利用XML技术结合`CONVERT`函数和`XML.nodes()`，首先将多值字段合并为一个XML字符串，然后通过`nodes()`函数提取每个子节点的值。例如，代码片段如下： ```sql SELECT A.ID, A.wfxwType, A.JCRQ, A.zgdwid, B.wfxwValue FROM ( SELECT StrXml = CONVERT(XML, '<root><v>' + REPLACE(t1.wfxwValue, ',', '</v><v>') + '</v></root>'), t1.ID, t1.wfxwType, t1.JCRQ, t1.zgdwid FROM ( -- ... 数据源查询 ... ) t1 ) A OUTER APPLY ( SELECT wfxwValue = N.v.value('.', 'nvarchar(40)') FROM A.StrXml.nodes('/root/v') N(v) ) B ``` 这种方法的优点在于效率高，处理41万条数据只需约8秒，这归功于`OUTERAPPLY`的高效性。然而，它受限于`XML`和`nodes()`操作的复杂性，可能对字段长度有一定要求。 第二种方法则是通过`SUBSTRING`函数逐个截取字符串，当遇到逗号时停止截取，形成新的行。代码如下： ```sql SELECT t1.ID, t1.wfxwType, SUBSTRING(t1.wfxwValue, number, CHARINDEX(',', t1.wfxwValue + ',', number) - number) FROM ( -- ... 数据源查询 ... ) t1 ``` 尽管这种方法可以实现目标，但性能较差，处理同样数据集耗时200秒，这是因为`SUBSTRING`函数对于大数据量的处理效率较低。此外，这种方法还受到字段长度（不超过2047字节）的限制，这是由`master..spt_values`中的字符长度限制所决定的。 总结来说，选择哪种方法取决于具体需求和性能敏感性。如果对性能要求较高且数据符合XML处理的限制，第一种方法更为适用；如果对性能不太敏感或数据长度较小，第二方法可以考虑，但需要注意其处理速度较慢以及长度限制。在实际应用中，应根据项目需求、数据库规模和资源条件综合评估选择最合适的列转行策略。