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)