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
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
