Mysql5 交叉表查询

# Host: localhost    Database: test
# ------------------------------------------------------
# Server version 5.0.45-community-nt-log

#
# Table structure for table sale
#

DROP TABLE IF EXISTS `sale`;
CREATE TABLE `sale` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `year` int(11) NOT NULL,
  `quarter` int(11) NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;

#
# Dumping data for table sale
#

/*!40101 SET NAMES latin1 */;

INSERT INTO `sale` VALUES (1,2004,1,2328);
INSERT INTO `sale` VALUES (2,2004,2,3822);
INSERT INTO `sale` VALUES (3,2004,3,7071);
INSERT INTO `sale` VALUES (4,2004,4,8931);
INSERT INTO `sale` VALUES (5,2005,1,2633);
INSERT INTO `sale` VALUES (6,2005,2,3910);
INSERT INTO `sale` VALUES (7,2005,3,237193);
INSERT INTO `sale` VALUES (8,2005,4,567444);
INSERT INTO `sale` VALUES (9,2006,1,12313);
插入数据后结果为：

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

交叉表查询语句：
select a.year, 1d, 2d, 3d, 4d from
(select distinct year from sale) a left join
(select year, amount 1d from sale where quarter=1 group by year) a1d on a.year = a1d.year
left join (select year, amount 2d from sale where quarter=2 group by year) a2d on a2d.year=a.year
left join (select year, amount 3d from sale where quarter=3 group by year) a3d on a3d.year=a.year
left join (select year, amount 4d from sale where quarter=4 group by year) a4d on a4d.year=a.year
该语句查询某年的四个季度的amount，以行显示，显示结果：

year	1d	2d	3d	4d
2004	2328.00	3822.00	7071.00	8931.00
2005	2633.00	3910.00	237193.00	567444.00
2006	12313.00	NULL	NULL	NULL

实现定长列的查询（即quarter的最大取值为4，定长为4列）.