# 取一条记录中，几个数值的最小值,该如何解决

www.MyException.Cn  网友分享于：2013-01-25  浏览：30次

select   a,b,c
----------------

------解决方案--------------------
declare @t table(a int,b int,c int)
insert into @t select 4,3,5
insert into @t select 5,6,4
insert into @t select 6,5,4
insert into @t select 7,2,0
insert into @t select 8,1,7

select (case when a <=b and a <=c then a when b <=a and b <=c then b else c end) as [min] from @t

/*
min
-----------
3
4
4
0
1
*/
------解决方案--------------------

select a,b,c
----------------

select id , min(col) 最小 from
(
select id = 1 , a col from tb
union all
select id = 2 , b col from tb
union all
select id = 3 , c col from tb
) t
group by id
------解决方案--------------------
--自定义函数
create function fun_min(@col varchar(200)) returns int as
begin
declare @ta table(a int)
declare @i int
set @col=@col+ ', '
set @i=charindex( ', ',@col)
while @i> 0
begin
insert @ta select left(@col,@i-1)
set @col=stuff(@col,1,@i, ' ')
set @i=charindex( ', ',@col)
end
return (select min(a) from @ta)
end
-------调用
select 最小值=dbo.fun_min(rtrim(a)+ ', '+rtrim(b)+ ', '+rtrim(c)) from 表a
select 最小值=dbo.fun_min(rtrim(a)+ ', '+rtrim(b)+ ', '+rtrim(c)+ ', '+rtrim(d)) from 表a
------解决方案--------------------

select min(aa) as small from
(
select a as aa from table1
union
select b as aa from table1
union
select c as aa from table1
) bb
------解决方案--------------------
create table a (a int,b int,c int)
insert a select 1,6,3
union all select 23,11,54
union all select 54,4,3
go
alter table a add id int identity
go
declare @a varchar(1000)

select @a=isnull(@a+ ' union all select id, ', ' ')+name+ ' from a ' from syscolumns where id=object_id( 'a ') and name <> 'id ' order by colid
select @a= 'select a.a,a.b,a.c,aa.a min from(select id,min(a) a from( select id, '+@a+ ') aa group by id) aa inner join a on aa.id=a.id '
exec( @a)
go
alter table a drop column id

--relult
/*

（所影响的行数为 3 行）

a b c min
----------- ----------- ----------- -----------
1 6 3 1
23 11 54 11
54 4 3 3
*/