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