select id,max(i) from (select id,i1 as i from tb union select id,i2 from tb union select id,i3 from tb union select id,i4 from tb union .........) t group by id
要是是SQL2005或者以上版本的话。 1000行那就用动态拼接成脚本 然后exec
------------------------- select top 1000 'select id,i'+ cast(Row_Number() over(order by t1.id) as varchar)+ ' from t union all' from sysobjects t1,sysobjects t2 这样对吧,在去掉最后的union all 有没有不用动态sql,一条句子就能写出来的
declare @sql varchar(8000),@sql_all varchar(8000) set @sql='' select @sql='select id,'+name+'as name from tb union' from sys.all_columns where object_id=(select name from sys.tables where name='tb') and name!='id' set @sql=SUBSTRING(@sql,0,LEN(@sql)-5) set @sql_all='select a.id,max(a.name) from ('+@sql+') as a group by a.id' exec(@sql_all)
应该没有可用的函数吧 如果真的是1000列的话 可以考虑写一个function
-- 用 union 的效率并不高,要是这样的话,就应该用 union all
-- 数据多的话,用 union all应该比用 union 要快,但是:最好的方法还是自定义一个函数去比较! select id, max(imax) as max_i from (select id,i1 as imax from tb union all select id,i2 from tb union all select id,i3 from tb union all select id,i4 from tb union all select id,i5 from tb union all select id,i6 from tb union all select id,i7 from tb union all select id,i8 from tb union all select id,i9 from tb union all select id,i10 from tb ) t group by id
declare @sql varchar(8000) select @sql=isnull(@sql+' union all ','')+'select id,'+c.name+' as maxdd from tb a where not exists (select id from tb b where a.'+c.name+'<b.'+d.name+')' from syscolumns c join syscolumns d on c.id=d.id where c.id=OBJECT_ID(N'tb') select @sql exec('select top 1 id,maxdd from ('+@sql+') s order by maxdd desc')
from (select id,i1 as i from tb union
select id,i2 from tb union
select id,i3 from tb union
select id,i4 from tb union
.........) t
group by id
1000行那就用动态拼接成脚本 然后exec
select top 1000
'select id,i'+ cast(Row_Number() over(order by t1.id) as varchar)+ ' from t union all'
from sysobjects t1,sysobjects t2 这样对吧,在去掉最后的union all
有没有不用动态sql,一条句子就能写出来的
declare @sql varchar(8000),@sql_all varchar(8000)
set @sql=''
select @sql='select id,'+name+'as name from tb union'
from sys.all_columns
where object_id=(select name from sys.tables where name='tb') and name!='id'
set @sql=SUBSTRING(@sql,0,LEN(@sql)-5)
set @sql_all='select a.id,max(a.name) from ('+@sql+') as a group by a.id'
exec(@sql_all)
可以考虑写一个function
-- 用 union 的效率并不高,要是这样的话,就应该用 union all
select id, max(imax) as max_i
from (select id,i1 as imax from tb union all
select id,i2 from tb union all
select id,i3 from tb union all
select id,i4 from tb union all
select id,i5 from tb union all
select id,i6 from tb union all
select id,i7 from tb union all
select id,i8 from tb union all
select id,i9 from tb union all
select id,i10 from tb ) t
group by id
select @sql=isnull(@sql+' union all
','')+'select id,'+c.name+' as maxdd from tb a where
not exists (select id from tb b where a.'+c.name+'<b.'+d.name+')' from syscolumns c join syscolumns d on c.id=d.id where c.id=OBJECT_ID(N'tb')
select @sql
exec('select top 1 id,maxdd from ('+@sql+') s order by maxdd desc')