多列取最大值或者最小值

/*

lvl1  lvl2   lvl3    lvl4    lvl

4      3     4      1       

3      2     2      1   

2      2     3      4

4      4     3      4

3      1     2      2

怎么写代码去比较lvl1、lvl2、lvl3、lvl4 对应每行的值，取其中最小的，将其值添加到lvl列里

运行结果应该是

lvl

1

1

2

3

1

*/

-方法(一)函数法

-->Title:Generatingtest 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/*)','int')
FROM @T AS A
    CROSS APPLY(SELECT x =(SELECT A.* FOR XML PATH('row'),TYPE)) AS B;
   
SELECT * FROM @T;
 

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

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:Generatingtest 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:Generatingtest 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]
                  unionall select [lvl2]
                  unionall select [lvl3]
                  unionall select [lvl4])T)
from #t

-- 方法六   两两比较

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

from: http://bbs.csdn.net/topics/320098453