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(','+@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/**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(',',','+@s,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<=@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-->结果/**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 行受影响)**/--方法7：行列转化，把一行转化为一列，然后再min或max(作者：josy)-->测试数据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 -->语句select lvl1,lvl2,lvl3,lvl4,lvl=(select min(t.a) from (select lvl1 as a union select lvl2 union select lvl3 union 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 行受影响)*/链接：http://topic.csdn.net/u/20120924/14/a48bd46f-5404-469f-8186-679906feb421.html http://blog.csdn.net/navy887/article/details/4682433 http://blog.csdn.net/josy/article/details/3603018
