/*

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 個資料列受到影響)
*/

?

轉載： http://blog.csdn.net/navy887/archive/2009/10/16/4682433.aspx

SQL SERVER多列取最大或者最小值