[转载]SQL 将一个字段内用逗号分隔的内容分成多条记录 – 岩子 – 博客园.
由于业务需求,我们可能会把一串以分割符字符串数据放到一个字段,如我们在客户端处理拆分是很简单的,不过这样做效果不太好,怎么用SQL SERVER 2008 来解决这件事件哪?
方案1
用SQL SERVER XML 功能来解决
<span class= "pun" > ----&</span><span class="pln">gt</span><span class="pun">;</span><span class="pln"> </span><span class="pun">生成测试数据:</span><span class="pln"> </span><span class="lit">@T</span><span class="pln"> DECLARE </span><span class= "lit" >@T</span><span class= "pln" > TABLE </span><span class= "pun" >(</span><span class= "pln" >id INT </span><span class= "pun" >,</span><span class= "typ" >Col1</span><span class= "pln" > VARCHAR </span><span class= "pun" >(</span><span class= "lit" >50</span><span class= "pun" >),</span><span class= "typ" >Col2</span><span class= "pln" > VARCHAR </span><span class= "pun" >(</span><span class= "lit" >50</span><span class= "pun" >))</span><span class= "pln" > INSERT INTO </span><span class= "lit" >@T</span><span class= "pln" > SELECT </span><span class= "lit" >1</span><span class= "pun" >,</span><span class= "str" > '卸货费' </span><span class= "pun" >,</span><span class= "str" > '运输车队业务, 客服接单业务' </span><span class= "pln" > UNION ALL SELECT </span><span class= "lit" >2</span><span class= "pun" >,</span><span class= "str" > '报关费' </span><span class= "pun" >,</span><span class= "str" > '报关业务、客服接单业务' </span><span class= "pln" > </span><span class= "pun" > ----</span><span class="pln">SQL</span><span class="pun">查询如下:</span><span class="pln"> SELECT T</span><span class= "pun" >.</span><span class= "pln" >id</span><span class= "pun" >,</span><span class= "pln" > A</span><span class= "pun" >.</span><span class= "pln" >x</span><span class= "pun" >.</span><span class= "pln" >value</span><span class= "pun" >(</span><span class= "str" > '.' </span><span class= "pun" >,</span><span class= "str" > 'varchar(10)' </span><span class= "pun" >)</span><span class= "pln" > AS code</span><span class= "pun" >,</span><span class= "pln" > B</span><span class= "pun" >.</span><span class= "pln" >x</span><span class= "pun" >.</span><span class= "pln" >value</span><span class= "pun" >(</span><span class= "str" > '.' </span><span class= "pun" >,</span><span class= "str" > 'varchar(10)' </span><span class= "pun" >)</span><span class= "pln" > AS code2 FROM </span><span class= "pun" >(</span><span class= "pln" > SELECT </span><span class= "pun" >*,</span><span class= "pln" >doc </span><span class= "pun" >=</span><span class= "pln" > CONVERT </span><span class= "pun" >(</span><span class= "pln" >xml</span><span class= "pun" >,</span><span class= "str" > '' </span><span class= "pun" >+</span><span class= "pln" > REPLACE </span><span class= "pun" >(</span><span class= "pln" >code</span><span class= "pun" >,</span><span class= "str" > ',' </span><span class= "pun" >,</span><span class= "str" > '' </span><span class= "pun" >)+</span><span class= "str" > '' </span><span class= "pun" >),</span><span class= "pln" > doc2</span><span class= "pun" >=</span><span class= "pln" > CONVERT </span><span class= "pun" >(</span><span class= "pln" >xml</span><span class= "pun" >,</span><span class= "str" > '' </span><span class= "pun" >+</span><span class= "pln" > REPLACE </span><span class= "pun" >(</span><span class= "pln" >code2</span><span class= "pun" >,</span><span class= "str" > ',' </span><span class= "pun" >,</span><span class= "str" > '' </span><span class= "pun" >)+</span><span class= "str" > '' </span><span class= "pun" >)</span><span class= "pln" > FROM </span><span class= "lit" >@T</span><span class= "pln" > </span><span class= "pun" >)</span><span class= "pln" > AS T CROSS APPLY doc</span><span class= "pun" >.</span><span class= "pln" >nodes</span><span class= "pun" >(</span><span class= "str" > '//v' </span><span class= "pun" >)</span><span class= "pln" > AS A</span><span class= "pun" >(</span><span class= "pln" >x</span><span class= "pun" >)</span><span class= "pln" > CROSS APPLY doc2</span><span class= "pun" >.</span><span class= "pln" >nodes</span><span class= "pun" >(</span><span class= "str" > '//v' </span><span class= "pun" >)</span><span class= "pln" > AS B</span><span class= "pun" >(</span><span class= "pln" >x</span><span class= "pun" >)</span><span class= "pln" > </span> |
方案2
用SQL SERVER WITH 递归查询
<span class= "pln" > WITH roy AS </span><span class= "pun" >(</span><span class= "pln" > SELECT </span><span class= "typ" >Col1</span><span class= "pln" > </span><span class= "pun" >,</span><span class= "pln" > </span><span class= "typ" >COl2</span><span class= "pln" > </span><span class= "pun" >=</span><span class= "pln" > CAST </span><span class= "pun" >(</span><span class= "pln" > LEFT </span><span class= "pun" >(</span><span class= "typ" >Col2</span><span class= "pun" >,</span><span class= "pln" > CHARINDEX</span><span class= "pun" >(</span><span class= "str" > ',' </span><span class= "pun" >,</span><span class= "pln" > </span><span class= "typ" >Col2</span><span class= "pln" > </span><span class= "pun" >+</span><span class= "pln" > </span><span class= "str" > ',' </span><span class= "pun" >)</span><span class= "pln" > </span><span class= "pun" >-</span><span class= "pln" > </span><span class= "lit" >1</span><span class= "pun" >)</span><span class= "pln" > AS NVARCHAR</span><span class= "pun" >(</span><span class= "lit" >100</span><span class= "pun" >))</span><span class= "pln" > </span><span class= "pun" >,</span><span class= "pln" > </span><span class= "typ" >Split</span><span class= "pln" > </span><span class= "pun" >=</span><span class= "pln" > CAST </span><span class= "pun" >(</span><span class= "pln" >STUFF</span><span class= "pun" >(</span><span class= "typ" >COl2</span><span class= "pln" > </span><span class= "pun" >+</span><span class= "pln" > </span><span class= "str" > ',' </span><span class= "pun" >,</span><span class= "pln" > </span><span class= "lit" >1</span><span class= "pun" >,</span><span class= "pln" > CHARINDEX</span><span class= "pun" >(</span><span class= "str" > ',' </span><span class= "pun" >,</span><span class= "pln" > </span><span class= "typ" >Col2</span><span class= "pln" > </span><span class= "pun" >+</span><span class= "pln" > </span><span class= "str" > ',' </span><span class= "pun" >),</span><span class= "pln" > </span><span class= "str" > '' </span><span class= "pun" >)</span><span class= "pln" > AS NVARCHAR</span><span class= "pun" >(</span><span class= "lit" >100</span><span class= "pun" >))</span><span class= "pln" > FROM </span><span class= "typ" >Tab</span><span class= "pln" > UNION ALL SELECT </span><span class= "typ" >Col1</span><span class= "pln" > </span><span class= "pun" >,</span><span class= "pln" > </span><span class= "typ" >COl2</span><span class= "pln" > </span><span class= "pun" >=</span><span class= "pln" > CAST </span><span class= "pun" >(</span><span class= "pln" > LEFT </span><span class= "pun" >(</span><span class= "typ" >Split</span><span class= "pun" >,</span><span class= "pln" > CHARINDEX</span><span class= "pun" >(</span><span class= "str" > ',' </span><span class= "pun" >,</span><span class= "pln" > </span><span class= "typ" >Split</span><span class= "pun" >)</span><span class= "pln" > </span><span class= "pun" >-</span><span class= "pln" > </span><span class= "lit" >1</span><span class= "pun" >)</span><span class= "pln" > AS NVARCHAR</span><span class= "pun" >(</span><span class= "lit" >100</span><span class= "pun" >))</span><span class= "pln" > </span><span class= "pun" >,</span><span class= "pln" > </span><span class= "typ" >Split</span><span class= "pln" > </span><span class= "pun" >=</span><span class= "pln" > CAST </span><span class= "pun" >(</span><span class= "pln" >STUFF</span><span class= "pun" >(</span><span class= "typ" >Split</span><span class= "pun" >,</span><span class= "pln" > </span><span class= "lit" >1</span><span class= "pun" >,</span><span class= "pln" > CHARINDEX</span><span class= "pun" >(</span><span class= "str" > ',' </span><span class= "pun" >,</span><span class= "pln" > </span><span class= "typ" >Split</span><span class= "pun" >),</span><span class= "pln" > </span><span class= "str" > '' </span><span class= "pun" >)</span><span class= "pln" > AS NVARCHAR</span><span class= "pun" >(</span><span class= "lit" >100</span><span class= "pun" >))</span><span class= "pln" > FROM </span><span class= "typ" >Roy</span><span class= "pln" > WHERE split </span><span class= "pun" >&</span><span class= "pln" >gt</span><span class= "pun" >;</span><span class= "pln" > </span><span class= "str" > '' </span><span class= "pln" > </span><span class= "pun" >)</span><span class= "pln" > SELECT </span><span class= "typ" >COl1</span><span class= "pln" > </span><span class= "pun" >,</span><span class= "pln" > </span><span class= "typ" >COl2</span><span class= "pln" > FROM roy ORDER BY </span><span class= "typ" >COl1</span><span class= "pln" > OPTION </span><span class= "pun" >(</span><span class= "pln" > MAXRECURSION </span><span class= "lit" >0</span><span class= "pln" > </span><span class= "pun" >)</span><span class= "pln" > </span> |