create table t(
year number(4) not null, --年度
s1 number, -- 一季度销售额
s2 number, -- 二季度销售额
s3 number, -- 三季度销售额
s4 number -- 四季度销售额
);
insert into t values(2001,120,130,100,140);
insert into t values(2002,180,130,180,140);
select * from t;
YEAR S1 S2 S3 S4
---------- ---------- ---------- ---------- ----------
2001 120 130 100 140
2002 180 130 180 140(2)问题描述,如:
要得到各年度销售额最大的季度(同一年中销售额出现两个相同最大的,取最大季度),(3)期望结果,如:
year max_season
------ ----------------
2001 4
2002 3
请问这个sql语句该怎么?
year number(4) not null, --年度
s1 number, -- 一季度销售额
s2 number, -- 二季度销售额
s3 number, -- 三季度销售额
s4 number -- 四季度销售额
);
insert into t values(2001,120,130,100,140);
insert into t values(2002,180,130,180,140);
select * from t;
YEAR S1 S2 S3 S4
---------- ---------- ---------- ---------- ----------
2001 120 130 100 140
2002 180 130 180 140(2)问题描述,如:
要得到各年度销售额最大的季度(同一年中销售额出现两个相同最大的,取最大季度),(3)期望结果,如:
year max_season
------ ----------------
2001 4
2002 3
请问这个sql语句该怎么?
from
(
select t1.year max(t1.value) max_value
from
(
select year, s1 as value, "1" as season from t
union
select year, s2 as value, "2" as season from t
union
select year, s3 as value, "3" as season from t
union
select year, s4 as value, "4" as season from t
) t1
group by t1.year
) t2,
(
select year, s1 as value, "1" as season from t
union
select year, s2 as value, "2" as season from t
union
select year, s3 as value, "3" as season from t
union
select year, s4 as value, "4" as season from t
) t3
where
t2.year = t3.year
group by t2.year
http://blog.csdn.net/precipitant/archive/2005/08/03/445022.aspx