原表:
id Cookie_ID OnSite_Search_String
1 1 x
2 1 y
3 2 a
4 2 b
5 2 c想得到如下结果:
Cookie_ID OnSite_Search_String
1 x,y
2 a,b,c代码如下:with x(Cookie_ID,cnt,list,id,length)
as
(
select Cookie_ID,COUNT(*) over(PARTITION by Cookie_ID),
cast(OnSite_Search_String as nvarchar(256)),id,1
from ##search_20120523
union all
select x.Cookie_ID,x.cnt,cast(x.list+','+s.OnSite_Search_String as nvarchar(256)),s.id,x.length+1
from ##search_20120523 s,x
where s.Cookie_ID=x.Cookie_ID and x.id>s.id
)
select Cookie_ID,list
from x
where length=cnt
order by Cookie_ID
OPTION(MAXRECURSION 0)现在的问题是,如果数据量为1000,能迅速得到结果,如果数据量10000以上,无论多长时间也跑不出来,像死循环了一样,请问是什么问题?
id Cookie_ID OnSite_Search_String
1 1 x
2 1 y
3 2 a
4 2 b
5 2 c想得到如下结果:
Cookie_ID OnSite_Search_String
1 x,y
2 a,b,c代码如下:with x(Cookie_ID,cnt,list,id,length)
as
(
select Cookie_ID,COUNT(*) over(PARTITION by Cookie_ID),
cast(OnSite_Search_String as nvarchar(256)),id,1
from ##search_20120523
union all
select x.Cookie_ID,x.cnt,cast(x.list+','+s.OnSite_Search_String as nvarchar(256)),s.id,x.length+1
from ##search_20120523 s,x
where s.Cookie_ID=x.Cookie_ID and x.id>s.id
)
select Cookie_ID,list
from x
where length=cnt
order by Cookie_ID
OPTION(MAXRECURSION 0)现在的问题是,如果数据量为1000,能迅速得到结果,如果数据量10000以上,无论多长时间也跑不出来,像死循环了一样,请问是什么问题?
select id,stuff((select ','+stri from tb where id = t.id for xml path('')),1,1,'') stri
from tb t
group by id