case_when用法

--仓储系统
          1   2   3   4   ...12
A         1   4   8
B         2   5   9
C         3   6   10
create table warehouse(
       wname varchar2(20),
       month_in number(4),
       reserves  number(10)
)
select * from warehouse


select wname,sum(reserves) from warehouse
group by wname


select wname,max(reserves) from warehouse
group by wname


select wname as 仓库名,
sum(case month_in when 1 then reserves else 0 end) as 一月,
sum(case month_in when 2 then reserves else 0 end) as 二月,
sum(case month_in when 3 then reserves else 0 end) as 三月,
sum(case month_in when 4 then reserves else 0 end) as 四月,
sum(case month_in when 5 then reserves else 0 end) as 五月,
sum(case month_in when 6 then reserves else 0 end) as 六月,
sum(case month_in when 7 then reserves else 0 end) as 七月,
sum(case month_in when 8 then reserves else 0 end) as 八月,
sum(case month_in when 9 then reserves else 0 end) as 九月,
sum(case month_in when 10 then reserves else 0 end) as 十月,
sum(case month_in when 11 then reserves else 0 end) as 十一月,
sum(case month_in when 12 then reserves else 0 end) as 十二月
from warehouse
group by wname


--超过边界有提示
select wname as 仓库名,
sum(case month_in when 1 then (case when reserves>10 then -10 else reserves end) else 0 end) as 一月,
sum(case month_in when 2 then (case when reserves>10 then -10 else reserves end) else 0 end) as 二月,
sum(case month_in when 3 then reserves else 0 end) as 三月,
sum(case month_in when 4 then reserves else 0 end) as 四月,
sum(case month_in when 5 then (case when reserves>10 then -10 else reserves end) else 0 end) as 五月,
sum(case month_in when 6 then reserves else 0 end) as 六月,
sum(case month_in when 7 then reserves else 0 end) as 七月,
sum(case month_in when 8 then reserves else 0 end) as 八月,
sum(case month_in when 9 then reserves else 0 end) as 九月,
sum(case month_in when 10 then reserves else 0 end) as 十月,
sum(case month_in when 11 then reserves else 0 end) as 十一月,
sum(case month_in when 12 then reserves else 0 end) as 十二月
from warehouse
group by wname