由于最近在开发过程中,需要在Oracle中取横向栏位的最大值,在此介绍几个Oracle的函数给大家:
具体内容请内博客内文,如果对各位有所帮助,请留下痕迹!
Oracle纵向取最大值函数为:max(),最小值函数为:min()
横向取最大值函数为:greatest(),最小值函数为:least()
求某几列中最大值
select greatest(col1,col2,col3...) from tablename;
求某几列中最小值
select least(col1,col2,col3...) from tablename;
SQL Server中取横向最大值和最小值的语法比较复杂,纵向方法与Oracle是一样的,在此不作介绍:
请参考: http://topic.csdn.net/t/20060404/18/4662908.html
create function dbo.f_max(
@col1 int, @col2 int, @col3 int, @col4 int, @col5 int
)returns int
as
begin
return((select max(col) from(
select col=@col1 union
select @col2 union
select @col3 union
select @col4 union
select @col5)))
end
go
create function dbo.f_min(
@col1 int, @col2 int, @col3 int, @col4 int, @col5 int
)returns int
as
begin
return((select min(col) from(
select col=@col1 union
select @col2 union
select @col3 union
select @col4 union
select @col5)))
end
go
-- 调用函数实现处理
select dbo.f_max(a1, a2, a3, a4, a5),
dbo.f_min(a1, a2, a3, a4, a5)
from tb
