--测试数据 create table #temp ([id] int,[col1] char(10),[col2] char(10),[col3] char(10)) insert into #temp select 1 [id],'a1' [col1],'b1' [col2],'c1' [col3] union all select 1,'d1','e1','f1' union all select 1,'g1','h1','i1' union all select 2 ,'a2','b2','c2' union all select 2,'d2','e2','f2' union all select 2,'g2','h2','i2' union all select 3,'a3','b3','c3' union all select 3,'d3','e3','f3' union all select 3,'g3','h3','i3' go--处理 select gid=identity(int),* into # from #temp order by id declare @s varchar(8000),@i varchar(10) select top 1 @s='',@i=max(gid)-min(gid) from # group by id order by max(gid)-min(gid) desc while @i>0 select @i=@i-1, @s=',col1=max(case a.gid-b.gid when '+@i+' then col1 end)' +',col2=max(case a.gid-b.gid when '+@i+' then col2 end)' +',col3=max(case a.gid-b.gid when '+@i+' then col3 end)' +@s exec('select a.id'+@s+' from # a,(select id,gid=min(gid) from # group by id)b where a.id=b.id group by a.id') drop table # godrop table #temp/*--结果 id col1 col2 col3 col1 col2 col3 ----------- ---------- ---------- ---------- ---------- ---------- ---------- 1 a1 b1 c1 d1 e1 f1 2 a2 b2 c2 d2 e2 f2 3 a3 b3 c3 d3 e3 f3 --*/
declare @s varchar(8000) set @s = ''select @s = @s + ',col1 = max(case when num ='+rtrim(num)+' then col1 end)' + ',col2 = max(case when num ='+rtrim(num)+' then col2 end)' + ',col3 = max(case when num ='+rtrim(num)+' then col3 end)' from (select count(b.id) as num from #temp a, #temp b where a.id = b.id and a.col1>=b.col1 group by a.id,a.col1,a.col2,a.col3) c group by num order by num set @s = 'select id'+@s+' from (select a.*,count(b.id) as num from #temp a, #temp b where a.id = b.id and a.col1>=b.col1 group by a.id,a.col1,a.col2,a.col3) c group by id order by id'exec(@s)
多谢老大,如果id对应的条数不为3条,是不确定的,但每个id对应的条数一样多,有办法处理么?
无论id对应的条数为多少条,也无法id对应的条数是否一样多,我的方法均能处理
厉害啊,虽然不是俺的问题,但是俺也迫切想知道 可是...... 嘿嘿,没看明白,GID是什么啊? select gid=identity(int),* into # from #temp order by id这句里的#from又是什么啊?gid=identity(int),*这个又是什么啊?
这篇帖子对你的思路肯定有帮助
create table #temp ([id] int,[col1] char(10),[col2] char(10),[col3] char(10))
insert into #temp select 1 [id],'a1' [col1],'b1' [col2],'c1' [col3]
union all select 1,'d1','e1','f1'
union all select 1,'g1','h1','i1'
union all select 2 ,'a2','b2','c2'
union all select 2,'d2','e2','f2'
union all select 2,'g2','h2','i2'
union all select 3,'a3','b3','c3'
union all select 3,'d3','e3','f3'
union all select 3,'g3','h3','i3'
go--处理
select gid=identity(int),* into # from #temp order by id
declare @s varchar(8000),@i varchar(10)
select top 1 @s='',@i=max(gid)-min(gid)
from # group by id
order by max(gid)-min(gid) desc
while @i>0
select @i=@i-1,
@s=',col1=max(case a.gid-b.gid when '+@i+' then col1 end)'
+',col2=max(case a.gid-b.gid when '+@i+' then col2 end)'
+',col3=max(case a.gid-b.gid when '+@i+' then col3 end)'
+@s
exec('select a.id'+@s+'
from # a,(select id,gid=min(gid) from # group by id)b
where a.id=b.id
group by a.id')
drop table #
godrop table #temp/*--结果
id col1 col2 col3 col1 col2 col3
----------- ---------- ---------- ---------- ---------- ---------- ----------
1 a1 b1 c1 d1 e1 f1
2 a2 b2 c2 d2 e2 f2
3 a3 b3 c3 d3 e3 f3
--*/
set @s = ''select
@s = @s + ',col1 = max(case when num ='+rtrim(num)+' then col1 end)'
+ ',col2 = max(case when num ='+rtrim(num)+' then col2 end)'
+ ',col3 = max(case when num ='+rtrim(num)+' then col3 end)'
from
(select
count(b.id) as num
from
#temp a,
#temp b
where
a.id = b.id and a.col1>=b.col1
group by
a.id,a.col1,a.col2,a.col3) c
group by
num
order by
num
set @s = 'select id'+@s+' from (select
a.*,count(b.id) as num
from
#temp a,
#temp b
where
a.id = b.id and a.col1>=b.col1
group by
a.id,a.col1,a.col2,a.col3) c
group by
id
order by id'exec(@s)
可是......
嘿嘿,没看明白,GID是什么啊?
select gid=identity(int),* into # from #temp order by id这句里的#from又是什么啊?gid=identity(int),*这个又是什么啊?