综合

MSSQL多列取最大或者最小值

2019-09-13 19:22:06来源:励志吧0次阅读

MSSQL多列取最大或者最小值

SQL code

/*

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

*/

心肌梗塞早期的症状
宝宝发烧39度
丁桂薏芽健脾凝胶能治腹泻吗
6个月宝宝发烧
分享到: