2.select * from 表 a where id in(select top 10 id from 表 where 分组字段=a.分组字段)
1、create function dbo.bigger (@f1 float,@f2 float) returns float as begin if @f1>@f2 return @f1 return @f2 end go select dbo.bigger(col1,dbo.bigger(col2,dbo.bigger(col3,col4))) from table
--查询处理(要用动态SQL语句,因为考虑到你的列是不固定的,为方便调用,可以改为存储过程) declare @i int,@s1 varchar(8000),@s2 varchar(8000),@s3 varchar(8000)select id=identity(int,1,1),name into #t from syscolumns where object_id('tb')=id and name<>'姓名' select @s1='',@s2='',@s3='',@i=@@rowcountwhile @i>0 begin select @s1=',@'+cast(@i as varchar)+' varchar(8000)'+@s1 ,@s2=',@'+cast(@i as varchar)+'=''select 姓名,a='+name+' from tb'''+@s2 ,@s3=case @i when 1 then '@'+cast(@i as varchar) else '+'' union all ''+@'+cast(@i as varchar) end+@s3 from #t where id=@i set @i=@i-1 endselect @s1=substring(@s1,2,8000) ,@s2=substring(@s2,2,8000) exec('declare '+@s1+' select '+@s2+' exec(''select 姓名,值=max(a) from(''+'+@s3+'+'') a group by 姓名'') ')
--下面是数据测试--测试数据 create table tb(姓名 varchar(10),字段a int,字段b int,字段c int,字段d int,字段e int,字段f int) insert into tb select 'li',6, 8, 2,8,7,5 union all select 'zhang',8, 9, 2,1,4,3 union all select 'wang',3, 8, 9,4,2,1 go--查询处理(要用动态SQL语句,为方便调用,可以改为存储过程) declare @i int,@s1 varchar(8000),@s2 varchar(8000),@s3 varchar(8000)select id=identity(int,1,1),name into #t from syscolumns where object_id('tb')=id and name<>'姓名' select @s1='',@s2='',@s3='',@i=@@rowcountwhile @i>0 begin select @s1=',@'+cast(@i as varchar)+' varchar(8000)'+@s1 ,@s2=',@'+cast(@i as varchar)+'=''select 姓名,a='+name+' from tb'''+@s2 ,@s3=case @i when 1 then '@'+cast(@i as varchar) else '+'' union all ''+@'+cast(@i as varchar) end+@s3 from #t where id=@i set @i=@i-1 endselect @s1=substring(@s1,2,8000) ,@s2=substring(@s2,2,8000) exec('declare '+@s1+' select '+@s2+' exec(''select 姓名,值=max(a) from(''+'+@s3+'+'') a group by 姓名'') ')go --删除测试环境 drop table tb,#t/*--测试结果 姓名 值 ---------- ----------- li 8 wang 9 zhang 9(所影响的行数为 3 行) --*/
(@f1 float,@f2 float)
returns float
as
begin
if @f1>@f2
return @f1 return @f2
end
go
select dbo.bigger(col1,dbo.bigger(col2,dbo.bigger(col3,col4))) from table
姓名 字段a 字段b 字段c 字段d 字段e 字段f ....
li 6 8 2 8 7 5 ....
zhang 8 9 2 1 4 3 ....
wang 3 8 9 4 2 1 ....
........
我要求各条记录中的最值上面的结果就会是
li 8
zhang 9
wang 9
......
能不能解决
declare @i int,@s1 varchar(8000),@s2 varchar(8000),@s3 varchar(8000)select id=identity(int,1,1),name into #t from syscolumns
where object_id('tb')=id and name<>'姓名'
select @s1='',@s2='',@s3='',@i=@@rowcountwhile @i>0
begin
select @s1=',@'+cast(@i as varchar)+' varchar(8000)'+@s1
,@s2=',@'+cast(@i as varchar)+'=''select 姓名,a='+name+' from tb'''+@s2
,@s3=case @i when 1 then '@'+cast(@i as varchar)
else '+'' union all ''+@'+cast(@i as varchar) end+@s3
from #t where id=@i
set @i=@i-1
endselect @s1=substring(@s1,2,8000)
,@s2=substring(@s2,2,8000)
exec('declare '+@s1+'
select '+@s2+'
exec(''select 姓名,值=max(a) from(''+'+@s3+'+'') a group by 姓名'')
')
create table tb(姓名 varchar(10),字段a int,字段b int,字段c int,字段d int,字段e int,字段f int)
insert into tb
select 'li',6, 8, 2,8,7,5
union all select 'zhang',8, 9, 2,1,4,3
union all select 'wang',3, 8, 9,4,2,1
go--查询处理(要用动态SQL语句,为方便调用,可以改为存储过程)
declare @i int,@s1 varchar(8000),@s2 varchar(8000),@s3 varchar(8000)select id=identity(int,1,1),name into #t from syscolumns
where object_id('tb')=id and name<>'姓名'
select @s1='',@s2='',@s3='',@i=@@rowcountwhile @i>0
begin
select @s1=',@'+cast(@i as varchar)+' varchar(8000)'+@s1
,@s2=',@'+cast(@i as varchar)+'=''select 姓名,a='+name+' from tb'''+@s2
,@s3=case @i when 1 then '@'+cast(@i as varchar)
else '+'' union all ''+@'+cast(@i as varchar) end+@s3
from #t where id=@i
set @i=@i-1
endselect @s1=substring(@s1,2,8000)
,@s2=substring(@s2,2,8000)
exec('declare '+@s1+'
select '+@s2+'
exec(''select 姓名,值=max(a) from(''+'+@s3+'+'') a group by 姓名'')
')go
--删除测试环境
drop table tb,#t/*--测试结果
姓名 值
---------- -----------
li 8
wang 9
zhang 9(所影响的行数为 3 行)
--*/