SQL Server uses the maximum or minimum values for multiple columns

/*

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
How to WriteCodeCompare the values of each line corresponding to lvl1, lvl2, lvl3, and lvl4, take the smallest value, and add the value to the LVL column.
The running result should be
LVL
1
1
2
3
1

*/

-- Method (1) Function

--> Title: generating test data
--> Author: wufeng4552
--> Date: 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
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

(Five data columns are affected)

*/

-- Method 2 mssql2005 XML Path

-------------------------------------
-- Author: liangck Liang ailan
-- Comment: alimail
-- Date: 2009-10-16 09:57:38
-------------------------------------

--> Generate Test Data: @ 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 query:

Update a set
LVL = B. X. Value ('min (// row/*) ', 'int ')
From @ T as
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 rows affected)

*/
-- Method (3) Author (Sifang City)

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

--> Query
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 rows affected)
**/

-- Method (4)

--> Title: generating test data
--> Author: wufeng4552
--> Date: 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

*/

-- Method (5)

--> Title: generating test data
--> Author: wufeng4552
--> Date: 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

(Five data columns are affected)
*/

 

Reprinted: http://blog.csdn.net/navy887/archive/2009/10/16/4682433.aspx