MSSQL多列取最大或者最小值_轉載

iSQlServer發表於2009-10-22
SQL
/* 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 1 2 2 3 4 2 4 4 3 4 3 3 1 2 2 1 (5 行受影響) **/ --方法(四) -->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 /* 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 */ --方法(五) -->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 /* 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 個資料列受到影響) */

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-617124/，如需轉載，請註明出處，否則將追究法律責任。

相關文章