日期:2014-05-16 浏览次数:20675 次
--> 建立数据表#temp --数据表#temp里有'notext'字段记录(一共有2578448行数据,但每一行的数据都是无序排列的)
if object_id('tempdb.dbo.#temp') is not null drop table #temp
go
create table #temp([sid] int,[webname] nvarchar(14),[uname] nvarchar(4),[qi] int,[notext] nvarchar(20),[blue] int,[bei] int)
insert #temp
select 2578451 ,'taobao','森林雪峰','2014039','01 24 12 32 28 13','13','1' union all
select 2578445 ,'taobao','森林雪峰','2014039','02 03 16 24 21 31','15','1' union all
select 2578447 ,'taobao','森林雪峰','2014039','02 05 10 11 21 24','15','1' union all
select 2578443 ,'taobao','森林雪峰','2014039','02 07 14 22 23 30','11','1' union all
select 2578442 ,'taobao','森林雪峰','2014039','03 13 22 25 31 32','09','1' union all
select 2578454 ,'taobao','森林雪峰','2014039','04 05 13 23 21 30','04','1' union all
select 2578453 ,'taobao','森林雪峰','2014039','08 04 16 26 29 28','11','1' union all
select 2578444 ,'taobao','森林雪峰','2014039','08 09 13 16 26 30','14','1' union all
select 2578446 ,'taobao','森林雪峰','2014039','10 11 12 02 24 29','15','1' union all
select 2578455 ,'taobao','森林雪峰','2014039','11 01 02 16 21 32','04','1' union all
select 2578456 ,'taobao','森林雪峰','2014039','16 17 06 03 24 30','02','1' union all
select 2578449 ,'taobao','森林雪峰','2014039','18 19 20 21 29 02','13','1' union all
select 2578452 ,'taobao','森林雪峰','2014039','26 27 28 02 05 12','11','1' union all
select 2578450 ,'taobao','森林雪峰','2014039','29 30 09 02 21 16','13','1' union all
select 2578448 ,'taobao','森林雪峰','2014039','31 32 07 24 14 02','15','1'
--------------生成数据--------------------------
--> 测试数据
--楼主代码
select DISTINCT
stuff((select ','+''''+notext+'''' from #temp ORDER BY notext
for xml path('')),1,1,'') 'notext'
from #temp
----------------以下结果是不对的----------------------------
'01 24 12 32 28 13','02 03 16 24 21 31','02 05 10 11 21 24','02 07 14 22 23 30','03 13 22 25 31 32','04 05 13 23 21 30','08 04 16 26 29 28','08 09 13 16 26 30
sid webname uname qi notext blue bei
2578451 taobao 森林雪峰 2014039 01 12 13 24 28 32 13 1
2578445 taobao 森林雪峰 2014039 02 03 16 21 24 31 15 1
2578447 taobao 森林雪峰 2014039 02 05 10 11 21 24 15 1
2578443 taobao 森林雪峰 2014039 02 07 14 22 23 30 11 1
2578442 taobao 森林雪峰 2014039 03 13 22 25 31 32 9 1
2578454 taobao 森林雪峰 2014039 04 0