先看下面这段代码, 它将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表达式,它可以把很复杂的嵌套查询分解为简单的多步查询。
分享到:
相关推荐
Labview编写的代码, 搜索拆分字符串
四则运算解析器 解析字符串运算式 代码简单 新手福利
sqlserver实现字符拆解成表格的形式
蓝桥杯培训教程 一、 逻辑推理 二、排序 三、 图形(矩阵) 四、 数字变幻 五、 数字组合与拆解 六、 字符串 七、 数制转换 八、 排列组合 九、 其它 十、 数据结构
爱普生R270拆解全过程
行业文档-设计装置-一种3D零件拆解组装平台
教育科研-学习工具-一种3D零件拆解组装平台.zip
ipad2的拆解过程全记录,ipad2的拆解过程全记录
js处理json格式的插入、修改、删除,以及字符串的比较等常用操作,下面有五个示例,感兴趣的朋友可以学习下
差异到字符串 将一组差异转换为字符串。 您可以使用生成更改。 基本例子 var diffs2string = require ( 'diffs-to-string' ) var changes = [ [ { country : 'germany' , capital : null } , { country : '...
2013年9月20日上市的全新iphone系列,完全拆解全过程。
ThinkpadE40拆解全过程.doc
hive sql 拆解字段.docx
柴油发动机拆解、组装工艺设计.doc
电动汽车拆解(1-5全).pdf
JS中判断字符串中出现次数最多的字符及出现的次数的简单实例 [removed] var str = 'qwertyuilo.,mnbvcsarrrrrrrrtyuiop;l,mhgfdqrtyuio;.cvxsrtyiuo'; var json = {}; //遍历str拆解其中的每一个字符将其某个...
生产部门创建拆解工单任务,然后对拆解工单进行下达,既然要拆解肯定是接下来到仓库取领我们上面例子的需要拆解的电脑,接下来到拆解产线进行拆解,对于拆解下来的拆解散件进行检测是否存在故障,如果不存在故障相应...
本文实例讲述了PHP实现截取中文字符串不...于是写了以下这段代码,判断如果中英文混合字符串中的汉字字节数为奇数,则少截取一个字节,保证汉字显示完整。 <?php $text = 1欢迎访问sina新浪播客; $value = substr
盾构机组装拆解安全专项施工方案.pptx
ThinkpadT60机器拆解过程,方便更换配件,清理机器,维修。