取最大值问题
有这样一张表格：
Table1：
ID Past Now Future
1 0 1 5
2 1 3 0
3 2 4 3
怎样取出ID和（Past、Now、Future）中的最大值，SQL语句怎么写啊？
ID Max
1 5
2 3
3 4 SQL
[解决办法]
给你这么一个语句，自己构造去吧
select max(id) from(select id=0 union all select 1 union all select 5)a
[解决办法]
create table Table1(ID int, Past int, Now int, Future int)
insert into table1 values(1 , 0 , 1 , 5)
insert into table1 values(2 , 1 , 3 , 0)
insert into table1 values(3 , 2 , 4 , 3)
go
select id , case when Past >= Now and Past >=Future then Future
when Now >= past and Now >=Future then Now
when Future>=Past and Future >=Now then Future
end [Max]
from table1
/*
id Max
----------- -----------
1 5
2 3
3 4
（所影响的行数为 3 行）
*/
select id , max([max]) [max] from
(
select id , now [max] from table1
union all
select id , now [Now] from table1
union all
select id , Future [max] from table1
) t
group by id
/*
id Max
----------- -----------
1 5
2 3
3 4
（所影响的行数为 3 行）
*/
drop table table1
[解决办法]
select id,max(cl) as [Max] from(select id,past as cl from tb union all select id,[now]as cl from tb union all select id,future as cl from tb)t
[解决办法]
个人觉得用 select case when 方式吧，
好理解些，
呵呵
------解决方案--------------------
select id , case when Past >= Now and Past >=Future then Future --Past
when Now >= past and Now >=Future then Now
when Future>=Past and Future >=Now then Future
end [Max]
from table1
2楼写得很好哦，不过第一个when 后面应该是past吧
[解决办法]
if OBJECT_ID('test') is not null
drop table test
go
create table test(id int,past int,[now] int,future int)
insert into test
select 1, 0, 1, 5 union
select 2, 1, 3, 0 union
select 3, 2, 4, 3
;with sel as(
select id,past as value from test union all
select ID,[now] from test union all
select id,future from test
)
select ID,MAX(value) [max] from sel group by id
[解决办法]
select
ID,(
select max(t1.MaxID) from (
select Past as MaxID union all
select [Now] as MaxID union all
select Future as MaxID) t1
) as maxID
from Table1
[解决办法]
select ID,MAX(aa) as maxID from Table1 t1 unpivot
( aa for MaxID in([past],[now],[future])) t2
group by t2.ID