拆解组装SQL字符串全过程

先看下面这段代码, 它将sql字符串先分割为行集，做一定处理后再合并为单行：

use tempdb
go

if (object_id ('t_Item' ) is not null ) drop table t_item
go
if (object_id ('t_Buy' ) is not null ) drop table t_Buy
go
create table t_Item (Item_ID int , Item_Name varchar (10 ))
insert into t_Item select 1 , '面包' union select 2 , '衣服' union select 3 , '鞋子'
create table t_Buy (Person varchar (10 ), WantBuy varchar (10 ))
insert into t_Buy select '小张' , '1,2' union select '小王' , '1,2,3'

go
/*原始表数据
Person WantBuy
---------- ----------
小张 1,2
小王 1,2,3
*/

/*要求查询结果
Person WantBuy
---------- -----------------
小王 面包,衣服,鞋子
小张 面包,衣服
*/

select Person , WantBuy = cast (replace (WantBuy , '</v><v>' , ',' ) as xml ). value ('.' , 'varchar(max)' )
from (select distinct Person from t_Buy ) ta outer apply (
select WantBuy = (select WantBuy as v from
(
select Person , c . Item_Name as WantBuy from (
select Person , convert (xml , '<v>' + replace (WantBuy , ',' , '</v><v>' )+ '</v>' ) as WantBuy
from t_Buy )a outer apply
(
select t . c . value ('.' , 'varchar(max)' ) AS WantBuy from a . WantBuy . nodes ('//v' ) AS t (c )
)b inner join t_Item c on b. wantbuy = c . item_id
)d where person = ta . person for xml path ('' ))
)tb

如果这段代码对于你来说是小case 那你就可以忽略此文，去论坛灌水去了。如果你还不了解他的工作原理，请继续耐心看完下面的分解过程。你需要单独理解以下内容：

SQL CTE(Common Table Expressions:公共表达式)

http://msdn.microsoft.com/zh-cn/library/ms190766.aspx

apply 关键字的用法

http://msdn.microsoft.com/zh-cn/library/ms175156.aspx

XQuery查询

http://blog.csdn.net/jinjazz/archive/2009/08/13/4443585.aspx

For XML子句

http://msdn.microsoft.com/zh-cn/library/ms190922.aspx

下面把上述语句分解为五个步骤，最后一个步骤就是最后需要的结果。

use tempdb
go

if (object_id ('t_Item' ) is not null ) drop table t_item
go
if (object_id ('t_Buy' ) is not null ) drop table t_Buy
go
create table t_Item (Item_ID int , Item_Name varchar (10 ))
insert into t_Item select 1 , '面包' union select 2 , '衣服' union select 3 , '鞋子'
create table t_Buy (Person varchar (10 ), WantBuy varchar (10 ))
insert into t_Buy select '小张' , '1,2' union select '小王' , '1,2,3'

go

/*第一步把WantBuy转为xml
Person WantBuy
---------- ----------------------------
小张 <v>1</v><v>2</v>
小王 <v>1</v><v>2</v><v>3</v>
*/

;with t1 as
(
select Person , convert (xml , '<v>' + replace (a . WantBuy , ',' , '</v><v>' )+ '</v>' ) as WantBuy
from t_Buy a
)


/*第二步把WantBuy字段拆分为多行
Person WantBuy
---------- --------
小张 1
小张 2
小王 1
小王 2
小王 3
*/
, t2 as
(
select a . Person , b. WantBuy from t1 a outer apply
(
select t . c . value ('.' , 'varchar(max)' ) AS WantBuy from a . WantBuy . nodes ('//v' ) AS t (c )
)b
)
/*第三步把WantBuy字段转为物品的名称
person item_name
---------- ----------
小张 面包
小张 衣服
小王 面包
小王 衣服
小王 鞋子
*/
, t3 as
(
select a . person , b. item_name from t2 a inner join t_Item b on a . wantbuy = b. item_id
)
/*第四步把WantBuy字段按照人名来聚合
Person WantBuy
---------- ------------------------------------
小王 <v>面包</v><v>衣服</v><v>鞋子</v>
小张 <v>面包</v><v>衣服</v>
*/
, t4 as
(
select * from (select distinct Person from t_Buy )a outer apply
(
select WantBuy = (select Item_Name as v from t3 where person = a . person for xml path ('' ))
)b
)

/*第五步把XML字段转为逗号分割的普通字段
Person WantBuy
---------- -----------------
小王 面包,衣服,鞋子
小张 面包,衣服
*/
, t5 as
(
select Person , WantBuy = cast (replace (WantBuy , '</v><v>' , ',' ) as xml ). value ('.' , 'varchar(max)' ) from t4
)
select * from t5

我们这里不得不夸奖一下SQLServer2005的CTE表达式，它可以把很复杂的嵌套查询分解为简单的多步查询。