select id,max(成绩) as 最大成绩 from (select id,a as 成绩 from table union all select id,b as 成绩 from table union all select id,c as 成绩 from table union all select id,d as 成绩 from table union all select id,e as 成绩 from table ) agroup by id
参考下:--test if object_id('test') is not null drop table test create table test(col1 int,col2 int,col3 int) go insert test select 1,4,7 union all select 2,5,8 union all select 3,6,9--执行 select case when ( case when max(col1)>max(col2) then max(col1) else max(col2) end)>max(col3) then (case when max(col1)>max(col2) then max(col1) else max(col2) end) else max(col3) end from test--结果 /* 9 */
/*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:16if 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 gocreate 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:16if 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 Goif 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 個資料列受到影響) */本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/fredrickhu/archive/2009/10/16/4682241.aspx
---try select id,max(成绩) as 成绩 from (select id , a as 成绩 from 表 union all select id , b as 成绩 from 表 union all select id , c as 成绩 from 表 union all select id , d as 成绩 from 表 union all select id , e as 成绩 from 表) tb group by id
from
(select id,a as 成绩 from table
union all
select id,b as 成绩 from table
union all
select id,c as 成绩 from table
union all
select id,d as 成绩 from table
union all
select id,e as 成绩 from table
) agroup by id
if object_id('test') is not null drop table test
create table test(col1 int,col2 int,col3 int)
go
insert test
select 1,4,7 union all
select 2,5,8 union all
select 3,6,9--执行
select
case when (
case when max(col1)>max(col2) then max(col1) else max(col2) end)>max(col3)
then (case when max(col1)>max(col2) then max(col1) else max(col2) end) else max(col3) end
from test--结果
/*
9
*/
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:16if 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
gocreate 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:16if 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
Goif 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 個資料列受到影響)
*/本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/fredrickhu/archive/2009/10/16/4682241.aspx
---try
select id,max(成绩) as 成绩 from
(select id , a as 成绩 from 表
union all
select id , b as 成绩 from 表
union all
select id , c as 成绩 from 表
union all
select id , d as 成绩 from 表
union all
select id , e as 成绩 from 表) tb group by id