怎么取出表中每行的最大值

/*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:Generating test data-->Author:wufeng4552-->Date :2009-10-16 09:58:16if not object_id('Tempdb..#t') is null    drop table #tGoCreate table #t([lvl1] int,[lvl2] int,[lvl3] int,[lvl4] int,[lvl] int)Insert #tselect 4,3,4,1,null union allselect 3,2,2,1,null union allselect 2,2,3,4,null union allselect 4,4,3,4,null union allselect 3,1,2,2,nullGoif object_id('UF_minget')is not null drop function UF_mingetgocreate function UF_minget(@col1 int,@col2 int,@col3 int,@col4 int)returns intas  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)  endgoupdate t set [lvl]=dbo.UF_minget([lvl1],[lvl2],[lvl3],[lvl4])from #t tselect * from #t/*lvl1        lvl2        lvl3        lvl4        lvl----------- ----------- ----------- ----------- -----------4           3           4           1           13           2           2           1           12           2           3           4           24           4           3           4           33           1           2           2           1(5 個資料列受到影響)*/--方法二  MSSQL2005 XML PATH---------------------------------------  Author : liangCK 梁爱兰--  Comment: 小梁 爱 兰儿--  Date   : 2009-10-16 09:57:38---------------------------------------> 生成测试数据: @TDECLARE @T TABLE (lvl1 int,lvl2 int,lvl3 int,lvl4 int,lvl int)INSERT INTO @TSELECT 4,3,4,1,null UNION ALLSELECT 3,2,2,1,null UNION ALLSELECT 2,2,3,4,null UNION ALLSELECT 4,4,3,4,null UNION ALLSELECT 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;/*lvl1        lvl2        lvl3        lvl4        lvl----------- ----------- ----------- ----------- -----------4           3           4           1           13           2           2           1           12           2           3           4           24           4           3           4           33           1           2           2           1(5 行受影响)*/--方法(三) 作者 (四方城) if object_id('[tb]') is not null drop table [tb]gocreate table [tb]([lvl1] int,[lvl2] int,[lvl3] int,[lvl4] int,[lvl] int)insert [tb]select 4,3,4,1,null union allselect 3,2,2,1,null union allselect 2,2,3,4,null union allselect 4,4,3,4,null union allselect 3,1,2,2,nullgocreate 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(',[email protected],id+1,charindex(',',',[email protected]+',',id+1)-id-1) as int))         from @ where substring(',[email protected],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/**lvl1        lvl2        lvl3        lvl4        lvl----------- ----------- ----------- ----------- -----------4           3           4           1           13           2           2           1           12           2           3           4           24           4           3           4           33           1           2           2           1(5 行受影响)**/--方法(四)-->Title:Generating test data-->Author:wufeng4552-->Date :2009-10-16 09:58:16if not object_id('Tempdb..#t') is null    drop table #tGoCreate table #t([lvl1] int,[lvl2] int,[lvl3] int,[lvl4] int,[lvl] int)Insert #tselect 4,3,4,1,null union allselect 3,2,2,1,null union allselect 2,2,3,4,null union allselect 4,4,3,4,null union allselect 3,1,2,2,nullGoif object_id('UF_minget')is not null drop function UF_mingetgocreate function UF_minget(@s varchar(200))returns intas  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(',',',[email protected],number)=number)  endgoselect   [lvl1],  [lvl2],  [lvl3],  [lvl4],  [lvl]=dbo.UF_minget(ltrim([lvl1])+','+ltrim([lvl2])+','+ltrim([lvl3])+','+ltrim([lvl4]))from #T/*lvl1        lvl2        lvl3        lvl4        lvl----------- ----------- ----------- ----------- -----------4           3           4           1           13           2           2           1           12           2           3           4           24           4           3           4           33           1           2           2           1*/--方法(五)-->Title:Generating test data-->Author:wufeng4552-->Date :2009-10-16 09:58:16if not object_id('Tempdb..#t') is null    drop table #tGoCreate table #t([lvl1] int,[lvl2] int,[lvl3] int,[lvl4] int,[lvl] int)Insert #tselect 4,3,4,1,null union allselect 3,2,2,1,null union allselect 2,2,3,4,null union allselect 4,4,3,4,null union allselect 3,1,2,2,nullGoselect [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/*lvl1        lvl2        lvl3        lvl4        lvl----------- ----------- ----------- ----------- -----------4           3           4           1           13           2           2           1           12           2           3           4           24           4           3           4           33           1           2           2           1(5 個資料列受到影響)*/--方法六 作者:josy (樹哥)-->测试数据  if object_id('[tb]') is not null drop table [tb]gocreate table [tb]([lvl1] int,[lvl2] int,[lvl3] int,[lvl4] int,[lvl] int)insert [tb]select 4,3,4,1,null union allselect 3,2,2,1,null union allselect 2,2,3,4,null union allselect 4,4,3,4,null union allselect 3,1,2,2,nullgo-->函数：返回两个数中值较小的数if object_id('F_GetMin')is not null drop function F_GetMingoCREATE FUNCTION F_GetMin(     @arg1   AS   int,     @arg2   AS   int   )   RETURNS   int   AS    BEGIN     RETURN CASE              WHEN @arg1<[email protected] 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-->结果/**lvl1        lvl2        lvl3        lvl4        lvl----------- ----------- ----------- ----------- -----------4           3           4           1           13           2           2           1           12           2           3           4           24           4           3           4           33           1           2           2           1(5 行受影响)**/