IdeaGrace博客

# 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);
插入数据后结果为：

交叉表查询语句：
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，以行显示，显示结果：

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