month char(6),--月份
sell number(10,2)--月销售额
);
2 create or replace view v_sale as
select
substr(month,1,4) as "年",
sum(decode(substrb(month,5,2),'01',sell,0)) as "01",
sum(decode(substrb(month,5,2),'02',sell,0)) as "02",
sum(decode(substrb(month,5,2),'03',sell,0)) as "03",
sum(decode(substrb(month,5,2),'04',sell,0)) as "04",
sum(decode(substrb(month,5,2),'05',sell,0)) as "05",
sum(decode(substrb(month,5,2),'06',sell,0)) as "06",
sum(decode(substrb(month,5,2),'07',sell,0)) as "07",
sum(decode(substrb(month,5,2),'08',sell,0)) as "08",
sum(decode(substrb(month,5,2),'09',sell,0)) as "09",
sum(decode(substrb(month,5,2),'10',sell,0)) as "10",
sum(decode(substrb(month,5,2),'11',sell,0)) as "11",
sum(decode(substrb(month,5,2),'12',sell,0)) as "12"
from sale group by substr(month,1,4);
3 select * from v_sale;
4 decode函数语法
5 补充例子
create table student(id number(2),score number(3));
select decode(sign(score-85),1,'优秀',0,'优秀',(decode(sign(score-70),1,'良好',0,'良好',
decode(sign(score-60),1,'及格',0,'及格','不及格')
))) from student;