重开一帖：构造路径

表01：
TID ITEM
100 c
100 f
100 a
100 m
100 p
200 f
200 b
200 c
300 b
300 p
300 c
400 f
400 a
400 m
400 p

表02：
TID ITEM PATH
100 c c
100 f c+f
100 a c+f+a
100 m c+f+a+m
100 p c+f+a+m+p
200 f f
200 b f+b
200 c f+b+c
300 b b
300 p b+p
300 c b+p+c
400 f f
400 a f+a
400 m f+a+m
400 p f+a+m+p


问题：我想给表01增加一个字段PATH,使之变成表02.
之前开过类似的一个帖子，只是这次有点不大一样。每个用户（TID）所购买商品（ITEM）在PATH的第一行中照抄，接下去每一行依次叠加，直到遍历完这个用户所购买的最后一个商品。其余用户循环这个过程。

注：表01的原数据量比较大，需要考虑SQL语句性能。

你把之前查询表1为表2的SQL执行的结果集作为临时表，利用这个临时表更新。

SQL code

create table tb(TID int,ITEM varchar(2))
insert into tb
select 100,'c' union
select 100,'f' union
select 100,'a' union
select 100,'m' union
select 100,'p' union
select 200,'f' union
select 200,'a' union
select 200,'b' union
select 200,'m' 

;with cte as
(
select no=ROW_NUMBER() over(partition by TID order by getdate()),* from tb
)

select TID,ITEM,
      [path]=case when no=1  then ITEM
      else 'ROOT'+(select '+'+ITEM from cte where TID=a.TID and no<a.no for XML path(''))
      end
from cte a      

/*
TID         ITEM path
----------- ---- ---------------
100         a    a
100         c    ROOT+a
100         f    ROOT+a+c
100         m    ROOT+a+c+f
100         p    ROOT+a+c+f+m
200         a    a
200         b    ROOT+a
200         f    ROOT+a+b
200         m    ROOT+a+b+f     

之前那个脚本在数据量大的时候效率很慢，并且我也想代码简介一些

SQL code

create table tb(TID int,ITEM varchar(2))
insert into tb
select 100,'c' union
select 100,'f' union
select 100,'a' union
select 100,'m' union
select 100,'p' union
select 200,'f' union
select 200,'a' union
select 200,'b' union
select 200,'m'
go

alter table tb add [path] varchar(4000)
go

;with cte as
(
select no=ROW_NUMBER() over(partition by TID order by getdate()),* from tb
),cta as
(
select TID,ITEM,
      [path]=case when no=1  then ITEM
      else 'ROOT'+(select '+'+ITEM from cte where TID=a.TID and no<a.no for XML path(''))
      end
from cte a 
)

update tb
set [path] = t.[path]
from cta t
where tid=t.tid and item=t.item

这次在PATH中不需要出现"ROOT".

SQL code

create table t01(TID int,ITEM varchar(10))
insert into t01 select 100,'c'
insert into t01 select 100,'f'
insert into t01 select 100,'a'
insert into t01 select 100,'m'
insert into t01 select 100,'p'
insert into t01 select 200,'f'
insert into t01 select 200,'b'
insert into t01 select 200,'c'
insert into t01 select 300,'b'
insert into t01 select 300,'p'
insert into t01 select 300,'c'
insert into t01 select 400,'f'
insert into t01 select 400,'a'
insert into t01 select 400,'m'
insert into t01 select 400,'p'
go
;with c1 as(
select *,n=row_number()over(partition by tid order by (select 1)) from t01
),c2 as(
select tid,convert(varchar(30),item)item,n from c1 where n=1
union all
select a.tid,convert(varchar(30),b.ITEM+'+'+a.ITEM),a.n from c1 a inner join c2 b on a.tid=b.tid and a.n=b.n+1
)select tid,item from c2 order by tid
/*
tid         item
----------- ------------------------------
100         c
100         c+f
100         c+f+a
100         c+f+a+m
100         c+f+a+m+p
200         f
200         f+b
200         f+b+c
300         b+p
300         b+p+c
300         b
400         f
400         f+a
400         f+a+m
400         f+a+m+p

(15 行受影响)

*/
go
drop table t01

递归查询，不知道效率怎么样 我先试试 先谢啦！

顺序不对:
原表01数据：
TID ITEM
300 b
300 p
300 c

结果需要保持原来的顺序：
TID ITEM PATH
300 b b
300 p b+p
300 c b+p+c