MSSQL多列取最大或者最小值

--方法(一) 函數法

-->Title:Generating test data

-->Author:wufeng4552

-->Date :2009-10-16 09:58:16

if not object_id('Tempdb..#t') is null

    drop table #t

Go

Create table #t([lvl1] int,[lvl2] int,[lvl3] int,[lvl4] int,[lvl] int)

Insert #t

select 4,3,4,1,null union all

select 3,2,2,1,null union all

select 2,2,3,4,null union all

select 4,4,3,4,null union all

select 3,1,2,2,null

Go

if object_id('UF_minget')is not null drop function UF_minget

go

create function UF_minget

(@col1 int,@col2 int,@col3 int,@col4 int)

returns int

as

  begin

     declare @t table(col int)

     insert @t select @col1 union all

               select @col2 union all

               select @col3 union all

               select @col4

     return(select min(col)from @t)

  end

go

update t set [lvl]=dbo.UF_minget([lvl1],[lvl2],[lvl3],[lvl4])

from #t t

select * from #t

--方法二  MSSQL2005 XML PATH

-------------------------------------

--  Author : liangCK 梁爱兰

--  Comment: 小梁 爱 兰儿

--  Date   : 2009-10-16 09:57:38

-------------------------------------

--> 生成测试数据: @T

DECLARE @T TABLE (lvl1 int,lvl2 int,lvl3 int,lvl4 int,lvl int)

INSERT INTO @T

SELECT 4,3,4,1,null UNION ALL

SELECT 3,2,2,1,null UNION ALL

SELECT 2,2,3,4,null UNION ALL

SELECT 4,4,3,4,null UNION ALL

SELECT 3,1,2,2,null

--SQL查询如下:

UPDATE A SET

    lvl = B.x.value('min(//row

--方法(三) 作者 (四方城)

if object_id('[tb]') is not null drop table [tb]

go

create table [tb]([lvl1] int,[lvl2] int,[lvl3] int,[lvl4] int,[lvl] int)

insert [tb]

select 4,3,4,1,null union all

select 3,2,2,1,null union all

select 2,2,3,4,null union all

select 4,4,3,4,null union all

select 3,1,2,2,null

go

create function getmin(@a varchar(8000))  

  returns int  

  as  

  begin declare @ table (id int identity,a char(1))  

      declare @t int  

      insert @ select top 8000 null from sysobjects a,sysobjects b  

      select @t=min(cast(substring(','+@a,id+1,charindex(',',','+@a+',',id+1)-id-1) as int))  

      from @ where substring(','+@a,id,8000) like ',_%'  

      return @t  

  end  

go

-->查询

select

  lvl1,

  lvl2,

  lvl3,

  lvl4,

  lvl=dbo.getmin(ltrim(lvl1)+','+ltrim(lvl2)+','+ltrim(lvl3)+','+ltrim(lvl4))

from tb

--方法(四)

-->Title:Generating test data

-->Author:wufeng4552

-->Date :2009-10-16 09:58:16

if not object_id('Tempdb..#t') is null

    drop table #t

Go

Create table #t([lvl1] int,[lvl2] int,[lvl3] int,[lvl4] int,[lvl] int)

Insert #t

select 4,3,4,1,null union all

select 3,2,2,1,null union all

select 2,2,3,4,null union all

select 4,4,3,4,null union all

select 3,1,2,2,null

Go

if object_id('UF_minget')is not null drop function UF_minget

go

create function UF_minget

(@s varchar(200))

returns int

as

  begin

  return(

    select col=min(substring(@s,number,charindex(',',@s+',',number)-number))

    from master..spt_values

    where type='p' and number<=len(@s+'a') and charindex(',',','+@s,number)=number)

  end

go

select

  [lvl1],

  [lvl2],

  [lvl3],

  [lvl4],

  [lvl]=dbo.UF_minget(ltrim([lvl1])+','+ltrim([lvl2])+','+ltrim([lvl3])+','+ltrim([lvl4]))

from #T

--方法(五)

-->Title:Generating test data

-->Author:wufeng4552

-->Date :2009-10-16 09:58:16

if not object_id('Tempdb..#t') is null

    drop table #t

Go

Create table #t([lvl1] int,[lvl2] int,[lvl3] int,[lvl4] int,[lvl] int)

Insert #t

select 4,3,4,1,null union all

select 3,2,2,1,null union all

select 2,2,3,4,null union all

select 4,4,3,4,null union all

select 3,1,2,2,null

Go

select [lvl1],

       [lvl2],

       [lvl3],

       [lvl4],

       [lvl]=(select min([lvl1])

              from (select [lvl1]

                  union all select [lvl2]

                  union all select [lvl3]

                  union all select [lvl4])T)

from #t

--方法六 作者:josy (樹哥)

-->测试数据 

if object_id('[tb]') is not null drop table [tb]

go

create table [tb]([lvl1] int,[lvl2] int,[lvl3] int,[lvl4] int,[lvl] int)

insert [tb]

select 4,3,4,1,null union all

select 3,2,2,1,null union all

select 2,2,3,4,null union all

select 4,4,3,4,null union all

select 3,1,2,2,null

go

-->函数：返回两个数中值较小的数

if object_id('F_GetMin')is not null drop function F_GetMin

go

CREATE FUNCTION F_GetMin

(  

  @arg1   AS   int,  

  @arg2   AS   int  

)  

RETURNS   int  

AS   

BEGIN  

  RETURN CASE  

           WHEN @arg1<=@arg2 THEN @arg1  

           WHEN @arg1>@arg2 THEN  @arg2  

           ELSE   NULL  

         END  

END  

GO  

-->查询

SELECT

  lvl1,

  lvl2,

  lvl3,

  lvl4,

  lvl=dbo.f_getmin(dbo.f_getmin(dbo.f_getmin(lvl1,lvl2),lvl3),lvl4) --函数嵌套

FROM

  tb

-->结果