Sql Server Some Question --Case...Whne...(1)

--- 问题----
1）给出你的问题的SQL脚本和样例数据，如：
/*
create table t(
year int not null, --年度
s1 int, -- 一季度销售额
s2 int, -- 二季度销售额
s3 int, -- 三季度销售额
s4 int -- 四季度销售额
)

insert into t values(2001,120,130,100,140)
insert into t values(2002,180,130,180,140)
insert into t values(2002,180,190,180,190)
insert into t values(2002,180,130,170,130)
insert into t values(2002,190,130,190,130)
*/

select * from t
year        s1          s2          s3          s4         
----------- ----------- ----------- ----------- -----------
2001        120         130         100         140
2002        180         130         180         140
2003        180         190         180         190
2004        180         130         170         130
2005        190         130         190         130

（2）问题描述，如：
要得到各年度销售额最大的季度（同一年中销售额出现两个相同最大的，取最大季度），

（3）期望结果，如：
year        Max Season Season Qty 
----------- ---------- -----------
2001        4          140
2002        3          180
2003        4          190
2004        1          180
2005        3          190

-----解决方案----
select * from t

select year,'Max Season'=case
when s1>s2 and s1>s3 and s1>s4 then '1'
when s2>=s1 and s2>s3 and s2>s4 then '2'
when s3>=s1 and s3>=s2 and s3>s4 then '3'
when s4>=s1 and s4>=s2 and s4>=s3 then '4'                     
end,
'Season Qty'=case
when s1>s2 and s1>s3 and s1>s4 then s1
when s2>=s1 and s2>s3 and s2>s4 then s2
when s3>=s1 and s3>=s2 and s3>s4 then s3
when s4>=s1 and s4>=s2 and s4>=s3 then s4                     
end
from t

运行结果：
year        s1          s2          s3          s4         
----------- ----------- ----------- ----------- -----------
2001        120         130         100         140
2002        180         130         180         140
2003        180         190         180         190
2004        180         130         170         130
2005        190         130         190         130

(5 row(s) affected)

year        Max Season Season Qty 
----------- ---------- -----------
2001        4          140
2002        3          180
2003        4          190
2004        1          180
2005        3          190

(5 row(s) affected)