SQL整理二

显示下表年度为季度,算出总计
表income
+----+------+---------+-----------+
| id | year | quarter | amount    |
+----+------+---------+-----------+
|  1 | 2004 |       1 |   2328.00 | 
|  2 | 2004 |       2 |   3822.00 | 
|  3 | 2004 |       3 |   7071.00 | 
|  4 | 2004 |       4 |   8931.00 | 
|  5 | 2005 |       1 |   2633.00 | 
|  6 | 2005 |       2 |   3910.00 | 
|  7 | 2005 |       3 | 237193.00 | 
|  8 | 2005 |       4 | 567444.00 | 
|  9 | 2006 |       1 |  12313.00 | 
+----+------+---------+-----------+
结果:
+------+----------+---------+-----------+-----------+----------+
| year | 1d       | 2d      | 3d        | 4d        |  SUM     |
+------+----------+---------+-----------+-----------+----------|
| 2004 |  2328.00 | 3822.00 |   7071.00 |   8931.00 | 22152.00 |
| 2005 |  2633.00 | 3910.00 | 237193.00 | 567444.00 | 811180.00|
| 2006 | 12313.00 |    NULL |      NULL |      NULL | 12313.00 |
+------+----------+---------+-----------+-----------+----------+
SQL:
+-------------------------------------------------------------+
CREATE TABLE IF NOT EXISTS `income` (
  `id` int(11) NOT NULL auto_increment,
  `year` varchar(4) default NULL,
  `quarter` int(2) default NULL,
  `amount` decimal(15,2) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=gbk AUTO_INCREMENT=10 ;

INSERT INTO `income` VALUES (1, '2004', 1, 2328.00);
INSERT INTO `income` VALUES (2, '2004', 2, 3822.00);
INSERT INTO `income` VALUES (3, '2004', 3, 7071.00);
INSERT INTO `income` VALUES (4, '2004', 4, 8931.00);
INSERT INTO `income` VALUES (5, '2005', 1, 2633.00);
INSERT INTO `income` VALUES (6, '2005', 2, 3910.00);
INSERT INTO `income` VALUES (7, '2005', 3, 237193.00);
INSERT INTO `income` VALUES (8, '2005', 4, 567444.00);
INSERT INTO `income` VALUES (9, '2006', 1, 12313.00);
+-------------------------------------------------------------+
问题：通过income表数据生成结果表信息
方法一：
+-------------------------------------------------------------------------+
|select  a.year,1d,2d,3d,4d,sum from (select distinct year from income) a | 
|left join                                                                |
|(select year,amount as 1d,id from income where quarter=1 group by year)d1| 
|on a.year=d1.year                                                        |
|left join 
|(select year,amount as 2d,id from income where quarter=2 group by year)d2| 
|on a.year=d2.year                                                        |
|left join                                                                | 
|(select year,amount as 3d,id from income where quarter=3 group by year)d3| 
|on a.year=d3.year                                                        |
|left join                                                                | 
|(select year,amount as 4d,id from income where quarter=4 group by year)d4| 
|on a.year=d4.year                                                        |
|left join                                                                | 
|(select year,sum(amount) as sum,id from income  group by year)s          | 
|on a.year=s.year                                                         |
+-------------------------------------------------------------------------+
方法二：
+-------------------------------------------------------+
|select year,                                           |
|sum(case when quarter=1 then amount else 0 end) 1d,    | 
|sum(case when quarter=2 then amount else null end) 2d, |
|sum(case when quarter=3 then amount else null end) 3d, |
|sum(case when quarter=4 then amount else null end) 4d, |
|sum(amount) sum                                        |
|from income group by year;                             |
+-------------------------------------------------------+