ID maxA maxB maxC
1 3 5 4
2 1 5 8
3 3 2 1
4 6 4 9
5 5 6 1
6 3 7 5
7 9 1 2求每条记录中的最大值?输出结果如下:ID maxValue
1 5
2 8
3 3
4 9
5 6
6 7
7 9一条SQL语句,谢谢!
1 3 5 4
2 1 5 8
3 3 2 1
4 6 4 9
5 5 6 1
6 3 7 5
7 9 1 2求每条记录中的最大值?输出结果如下:ID maxValue
1 5
2 8
3 3
4 9
5 6
6 7
7 9一条SQL语句,谢谢!
when maxB > maxC and maxB > maxA then maxB
when maxC > maxA and maxC > maxB then maxC
end maxValue
from tb
from tb
insert into tb values(1, 3, 5, 4)
insert into tb values(2, 1, 5, 8)
insert into tb values(3, 3, 2, 1)
insert into tb values(4, 6, 4, 9)
insert into tb values(5, 5, 6, 1)
insert into tb values(6, 3, 7, 5)
insert into tb values(7, 9, 1, 2)
go--方法一、不考虑重复数据。
select id , case when maxA > maxB and maxA > maxC then maxA
when maxB > maxC and maxB > maxA then maxB
when maxC > maxA and maxC > maxB then maxC
end maxValue
from tb
/*
id maxValue
----------- -----------
1 5
2 8
3 3
4 9
5 6
6 7
7 9(所影响的行数为 7 行)
*/
--方法二、考虑重复数据。select id , max(maxValue) maxValue from
(
select id , maxValue = maxA from tb
union all
select id , maxValue = maxB from tb
union all
select id , maxValue = maxC from tb
) t
group by id
/*
id maxValue
----------- -----------
1 5
2 8
3 3
4 9
5 6
6 7
7 9(所影响的行数为 7 行)
*/drop table tb
insert into tb select
1,3,5,4 union all select
2,1,5,8 union all select
3,3,2,1 union all select
4,6,4,9 union all select
5,5,6,1 union all select
6,3,7,5 union all select
7,9,1,2
select ID,(case when (case when maxA > maxB then maxA else maxB end)>maxC
then (case when maxA > maxB then maxA else maxB end) else maxC end
) as maxvalue
from tbID maxvalue
----------- -----------
1 5
2 8
3 3
4 9
5 6
6 7
7 9