源贴:
http://topic.csdn.net/t/20030516/18/1794414.html
http://expert.csdn.net/Expert/topicview.asp?id=632928
//////////////////////////////////////////set nocount on
if object_id('tempdb.dbo.#YourResultTable') is not null drop table #YourResultTable
create table #YourResultTable (id1 int,id2 varchar(5), value varchar(10))
insert #YourResultTable (id1,id2,value) values(8,'a','nice')
insert #YourResultTable (id1,id2,value) values(8,'a','niiice')
insert #YourResultTable (id1,id2,value) values(8,'a','ice')
insert #YourResultTable (id1,id2,value) values(1,'e','cool')
insert #YourResultTable (id1,id2,value) values(1,'e','nice')
insert #YourResultTable (id1,id2,value) values(1,'b','wow')
insert #YourResultTable (id1,id2,value) values(1,'b','wodw')
insert #YourResultTable (id1,id2,value) values(2,'a','cool')
insert #YourResultTable (id1,id2,value) values(2,'a','wow')
insert #YourResultTable (id1,id2,value) values(3,'c','cool')
insert #YourResultTable (id1,id2,value) values(3,'c','nice')
insert #YourResultTable (id1,id2,value) values(4,'a','nice')
select * from #YourResultTable
declare @z varchar(100),@id1 int,@id2 varchar(5)
if object_id('tempdb.dbo.#ProcessTable') is not null drop table #ProcessTable
create table #ProcessTable (id1 int,id2 varchar(5),value varchar(100), ProcessStep int identity(1,1))
insert #ProcessTable(id1,id2,value)
select id1,id2, value
from #YourResultTable
order by id1,id2,value
update #ProcessTable
set @z = value = case when @id1=id1 and @id2=id2 then @z+',' else '' end + value
,@id1 = id1
,@id2 = id2
from #ProcessTable
--select * from #ProcessTable
select id1,id2, value
from (select max(ProcessStep) MaxStep from #ProcessTable group by id1,id2) x
join #ProcessTable t
on ProcessStep = MaxStep
问题:
上面的写法有问题吗?
我有一个数据集,约5W行,在用上面的写法错了,在汇总字符串合并时,有时行,有时不行.
后来发觉
create table #ProcessTable (id1 int,id2 varchar(5),value varchar(100), ProcessStep int identity(1,1))
insert #ProcessTable(id1,id2,value)
select id1,id2, value
from #YourResultTable
order by id1,id2,value 这句是有误的,
在运行
insert #ProcessTable(id1,id2,value)
select id1,id2, value
from #YourResultTable
order by id1,id2,value
时,order by id1,id2,value 根本不起效!!!!,
系统在存储表时,他还是会按照他自已的顺序(可能是根据表key)来顺序写的,
这样,就会造成下面的update 语句错误.
不知大家有没有遇到这样的问题,或知道他出错的原因,还是我的分析错了?
http://topic.csdn.net/t/20030516/18/1794414.html
http://expert.csdn.net/Expert/topicview.asp?id=632928
//////////////////////////////////////////set nocount on
if object_id('tempdb.dbo.#YourResultTable') is not null drop table #YourResultTable
create table #YourResultTable (id1 int,id2 varchar(5), value varchar(10))
insert #YourResultTable (id1,id2,value) values(8,'a','nice')
insert #YourResultTable (id1,id2,value) values(8,'a','niiice')
insert #YourResultTable (id1,id2,value) values(8,'a','ice')
insert #YourResultTable (id1,id2,value) values(1,'e','cool')
insert #YourResultTable (id1,id2,value) values(1,'e','nice')
insert #YourResultTable (id1,id2,value) values(1,'b','wow')
insert #YourResultTable (id1,id2,value) values(1,'b','wodw')
insert #YourResultTable (id1,id2,value) values(2,'a','cool')
insert #YourResultTable (id1,id2,value) values(2,'a','wow')
insert #YourResultTable (id1,id2,value) values(3,'c','cool')
insert #YourResultTable (id1,id2,value) values(3,'c','nice')
insert #YourResultTable (id1,id2,value) values(4,'a','nice')
select * from #YourResultTable
declare @z varchar(100),@id1 int,@id2 varchar(5)
if object_id('tempdb.dbo.#ProcessTable') is not null drop table #ProcessTable
create table #ProcessTable (id1 int,id2 varchar(5),value varchar(100), ProcessStep int identity(1,1))
insert #ProcessTable(id1,id2,value)
select id1,id2, value
from #YourResultTable
order by id1,id2,value
update #ProcessTable
set @z = value = case when @id1=id1 and @id2=id2 then @z+',' else '' end + value
,@id1 = id1
,@id2 = id2
from #ProcessTable
--select * from #ProcessTable
select id1,id2, value
from (select max(ProcessStep) MaxStep from #ProcessTable group by id1,id2) x
join #ProcessTable t
on ProcessStep = MaxStep
问题:
上面的写法有问题吗?
我有一个数据集,约5W行,在用上面的写法错了,在汇总字符串合并时,有时行,有时不行.
后来发觉
create table #ProcessTable (id1 int,id2 varchar(5),value varchar(100), ProcessStep int identity(1,1))
insert #ProcessTable(id1,id2,value)
select id1,id2, value
from #YourResultTable
order by id1,id2,value 这句是有误的,
在运行
insert #ProcessTable(id1,id2,value)
select id1,id2, value
from #YourResultTable
order by id1,id2,value
时,order by id1,id2,value 根本不起效!!!!,
系统在存储表时,他还是会按照他自已的顺序(可能是根据表key)来顺序写的,
这样,就会造成下面的update 语句错误.
不知大家有没有遇到这样的问题,或知道他出错的原因,还是我的分析错了?
select cast(value as varchar(7000))...
into #aaa
from ..来做.
if object_id('tempdb.dbo.#YourResultTable') is not null drop table #YourResultTable
create table #YourResultTable (id1 int,id2 varchar(5), value varchar(10))
insert #YourResultTable (id1,id2,value) values(8,'a','nice')
insert #YourResultTable (id1,id2,value) values(8,'a','niiice')
insert #YourResultTable (id1,id2,value) values(8,'a','ice')
insert #YourResultTable (id1,id2,value) values(1,'e','cool')
insert #YourResultTable (id1,id2,value) values(1,'e','nice')
insert #YourResultTable (id1,id2,value) values(1,'b','wow')
insert #YourResultTable (id1,id2,value) values(1,'b','wodw')
insert #YourResultTable (id1,id2,value) values(2,'a','cool')
insert #YourResultTable (id1,id2,value) values(2,'a','wow')
insert #YourResultTable (id1,id2,value) values(3,'c','cool')
insert #YourResultTable (id1,id2,value) values(3,'c','nice')
insert #YourResultTable (id1,id2,value) values(4,'a','nice')
select * from #YourResultTable
declare @z varchar(100),@id1 int,@id2 varchar(5)
if object_id('tempdb.dbo.#ProcessTable') is not null drop table #ProcessTable select id1,id2, value,ProcessStep=identity(int,1,1)
from #YourResultTable
order by id1,id2,value
update #ProcessTable
set @z = value = case when @id1=id1 and @id2=id2 then @z+',' else '' end + value
,@id1 = id1
,@id2 = id2
from #ProcessTable
--select * from #ProcessTable
select id1,id2, value
from (select max(ProcessStep) MaxStep from #ProcessTable group by id1,id2) x
join #ProcessTable t
on ProcessStep = MaxStep
if object_id('tempdb.dbo.#YourResultTable') is not null drop table #YourResultTable
create table #YourResultTable (id1 int,id2 varchar(5), value varchar(10))
insert #YourResultTable (id1,id2,value) values(8,'a','nice')
insert #YourResultTable (id1,id2,value) values(8,'a','niiice')
insert #YourResultTable (id1,id2,value) values(8,'a','ice')
insert #YourResultTable (id1,id2,value) values(1,'e','cool')
insert #YourResultTable (id1,id2,value) values(1,'e','nice')
insert #YourResultTable (id1,id2,value) values(1,'b','wow')
insert #YourResultTable (id1,id2,value) values(1,'b','wodw')
insert #YourResultTable (id1,id2,value) values(2,'a','cool')
insert #YourResultTable (id1,id2,value) values(2,'a','wow')
insert #YourResultTable (id1,id2,value) values(3,'c','cool')
insert #YourResultTable (id1,id2,value) values(3,'c','nice')
insert #YourResultTable (id1,id2,value) values(4,'a','nice')
select * from #YourResultTable
declare @z varchar(100),@id1 int,@id2 varchar(5)
if object_id('tempdb.dbo.#ProcessTable') is not null drop table #ProcessTable select id1,id2, value,ProcessStep=identity(int,1,1) into #ProcessTable
from #YourResultTable
order by id1,id2,value
update #ProcessTable
set @z = value = case when @id1=id1 and @id2=id2 then @z+',' else '' end + value
,@id1 = id1
,@id2 = id2
from #ProcessTable
--select * from #ProcessTable
select id1,id2, value
from (select max(ProcessStep) MaxStep from #ProcessTable group by id1,id2) x
join #ProcessTable t
on ProcessStep = MaxStep