叨叨两句

1. ~

SQL习题002

1

CREATE TABLE tb_amount1 (  year varchar2(4) DEFAULT NULL,  month number(2) DEFAULT NULL,  amount number(3,2) DEFAULT NULL); insert  into tb_amount1(year,month,amount) values ('2011',1,1.10);insert  into tb_amount1(year,month,amount) values ('2011',2,1.20);insert  into tb_amount1(year,month,amount) values ('2011',3,1.30);insert  into tb_amount1(year,month,amount) values ('2011',4,1.40);insert  into tb_amount(year,month,amount) values ('2012',1,2.10);insert  into tb_amount(year,month,amount) values ('2012',2,2.20);insert  into tb_amount(year,month,amount) values ('2012',3,2.30);insert  into tb_amount(year,month,amount) values ('2012',4,2.40);

-- 1. 以 1 为例, 将 1月份的数据竖起来select year,case month when 1 then amount end m1 from tb_amount; -- 2. 将空行全都取掉select year,sum(case month when 1 then amount end) m1 from tb_amount group by year; -- 3. 结果select year,       sum(case month when 1 then amount end) m1,       sum(case month when 2 then amount end) m2,       sum(case month when 3 then amount end) m3,       sum(case month when 4 then amount end) m4 from tb_amount group by year;