Mikel
[转载]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 ALLSELECT </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">SELECTT</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 code2FROM </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 TCROSS 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 royAS </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 ALLSELECT </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 royORDER 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> |
