拆解组装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
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
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表达式，它可以把很复杂的嵌套查询分解为简单的多步查询。