重开一帖：构造路径

Question

表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语句性能。

Answer 1

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

Answer 2

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     

Answer 3

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'
goalter 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

Answer 4

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

Answer 5

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

Answer 6

顺序不对:
原表01数据：
TID     ITEM
300 b
300 p
300 c结果需要保持原来的顺序：
TID     ITEM PATH
300 b b
300 p b+p
300 c b+p+c

Answer 7

这样可能更快些：
create table t01(TID int,ITEM varchar(20))
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
create table t02(TID int,ITEM varchar(20),n int)
select *,n=identity(int,1,1) into # from t01
insert into t02 select * from # where n in(select min(n) from # group by tid)while exists(select 1 from # a where n>(select max(n) from t02 where tid=a.tid))
insert into t02 
select a.tid,b.item+'+'+a.item,a.n from # a inner join t02 b on a.tid=b.tid and a.n=b.n+1
where not exists(select 1 from t02 where tid=b.tid and n>b.n)
select tid,item from t02 order by tid,item
/*
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,#,t02

Answer 8

顺序重排过了：
create table t01(TID int,ITEM varchar(20))
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
create table t02(TID int,ITEM varchar(20),n int)
select *,n=identity(int,1,1) into # from t01
insert into t02 select * from # where n in(select min(n) from # group by tid)while exists(select 1 from # a where n>(select max(n) from t02 where tid=a.tid))
insert into t02 
select a.tid,b.item+'+'+a.item,a.n from # a inner join t02 b on a.tid=b.tid and a.n=b.n+1
where not exists(select 1 from t02 where tid=b.tid and n>b.n)
select tid,item from t02 order by tid,item
/*
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
300         b+p
300         b+p+c
400         f
400         f+a
400         f+a+m
400         f+a+m+p(15 行受影响)*/
go
drop table t01,#,t02