＋设为首页 ＋添加到收藏夹
有一个表table:
字段1,
字段2,
字段3,
字段4,
字段5.....字段29，全为int型，我如何取这个表里的最大值、最小值？
取到最大值、最小值后我如何回过头来定位是那个字段？
you can use table:sysobject
select max(字段) as tmpmax from tablename
select min(字段) as tmpmin from tablename
select * into #临时表 from (
select 字段1 名,max(字段1) 大值,min(字段1) 小值 from [table]
union all
select 字段2,max(字段2),min(字段2) from [table]
union all
select 字段3,max(字段3),min(字段3) from [table]
.......) tem
select 名,大值 from #临时表 where 大值=(select max(大值) from #临时表)
select 名,小值 from #临时表 where 小值=(select min(小值) from #临时表)
go
drop table #临时表
你在查询分析器执行下面的语句，就能得到你的结果
---创建例表 t
create table t
(c1 int ,c2 int ,c3 int ,c4 int ,c5 int)
insert into t values(2,465,445,321,345)
insert into t values(21,321,4532,45,7658)
insert into t values(32,2,777,66,555)
insert into t values(32,4,88,66,543)
--利用系统表得到字段，构造语句，原理同大力
declare @ varchar(2000)
set @=select * into #t1 from ( select
select @=@+name+ as 字段,max(+name+) as n_max ,min(+name+) as n_min from t union select from syscolumns where object_name(id)=t
set @=left(@,len(@)-len(union select ))+) as a
execute (@+ select * from #t1
select 字段,n_max from #t1 where n_max=(select max(n_max) from #t1)
select 字段,N_min from #t1 where n_min=(select min(n_min) from #t1))
--将例表删除，不占你的地方啦。：）
drop table t
---
如果执行成功，你该知道你的怎末做了吧？
噢,对了, 你还想知道在哪个字段, 再改进一下:
create table table_a (字段名 varchar(50),字段值 int)
insert table_a select 字段1名,字段1 from table
insert table_a select 字段2名,字段2 from table
...
insert table_a select 字段29名,字段29 from table
declare @maxfname varchar(50), @minfname varchar(50)
declare @max int, @min int
select @max = max(字段值), @min = min(字段值) from table_a
select @maxfname = 字段名 from table_a where 字段值 = @max
select @minfname = 字段名 from table_a where 字段值 = @min
或者建一个如下的存储过程来处理：
Create proc GetMaxMin
As
declare @fldCount int
declare @fldStr varchar(8000)
set @fldStr =
select @fldCount=count(*) from syscolumns col,sysobjects obj
where col.id = obj.id
and obj.name = MLOther
while @fldCount > 0
begin
select @fldStr = @fldStr + select min(字段+cast(@fldCount as varchar(2))+) minn,max(字段+cast(@fldCount as varchar(2))+) maxn from TableName
if @fldCount > 1
select @fldStr = @fldStr + union all
print @fldStr
select @fldCount = @fldCount - 1
print @fldCount
end
exec(select min(minn), max(maxn) from ( + @fldStr + ) A)
go
执行：
exec GetMaxMin
