数据如下:时间, , 字段1, 字段2, 字段3, 字段4, 字段5, 字段6, 字段7, 字段8, ..... n个字段
2009-5-30 12:21:18, 20.10, 35.13, 22.21, 23.34, 28.10, 43.10, 23.10, 34.10, .....
2009-5-31 10:21:18, 28.12, 33.12, 23.24, 22.31, 21.13, 40.11, 24.11, 30.08, .....
.
.
这里有N个字段,
能用SQL语句查询出,
平均值最大的5个字段吗????ps: 字段是varchar的, 有可能是null
2009-5-30 12:21:18, 20.10, 35.13, 22.21, 23.34, 28.10, 43.10, 23.10, 34.10, .....
2009-5-31 10:21:18, 28.12, 33.12, 23.24, 22.31, 21.13, 40.11, 24.11, 30.08, .....
.
.
这里有N个字段,
能用SQL语句查询出,
平均值最大的5个字段吗????ps: 字段是varchar的, 有可能是null
第一步:
select avg(isnull(字段1,0)) as 字段1,
avg(isnull(字段2,0)) as 字段2,
avg(isnull(字段3,0)) as 字段3,
.......
avg(isnull(字段n,0)) as 字段n
into tb1 -----此处要插入实体表,不能插入临时表,为行列互换做基础。
from tb第二步
declare @sql varchar(8000)
select @sql = isnull(@sql + ' union all ' , '' ) + ' select 字段值 = ' + (Name ) +',字段名称 = '+ quotename(Name , '''') + ' from tb1 '
from syscolumns
where ID = object_id('tb1') --表名tb1,
order by colid asc
select @sql='Select top 5 * from ('+@sql+') a order by 字段值 desc'
exec(@sql)
declare @Mark int ,@i int
select @ = count(*),@i =0 from SYSCOLUMNS where id = object_id('LI')while @i < @
begin
select @sql = 'select top 5 LB., LB.FName from( '
select @sql = @sql + 'select avg(' + LO.[name] +') as Mark,' + '' + LO.[name] + '' + ' as Fname FROM LI WHERE ' +
'' + LO.[name] +'' + ' = (select top 1 [name] from (select top @i [name] from SYSCOLUMNS where id = object_id(''LI'') order by [name] desc ))) LB'
end exec @sql 这样就OK了。