日期:2014-05-17 浏览次数:20516 次
/* 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: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 /* lvl1 lvl2 lvl3 lvl4 lvl ----------- ----------- ----------- ----------- ----------- 4 3 4 1 1 3 2 2 1 1 2 2 3 4 2 4 4 3 4 3 3 1 2 2 1 (5 個資料列受到影響) */ --方法二 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; /* lvl1 lvl2 lvl3 lvl4 lvl ----------- ----------- ----------- ----------- ----------- 4 3 4 1 1 3 2 2 1 1 2 2 3 4 2 4 4 3 4 3 3 1 2 2 1 (5 行受影响) */ --方法(三) 作者 (四方城) 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 /** lvl1 lvl2 lvl3 lvl4 lvl ----------- ----------- ----------- ----------- ----------- 4 3 4 1 1 3 2 2 1