mysql 经典题目

 1 CREATE TABLE IF NOT EXISTS tb_amount(
 2    `Id` INT NOT NULL AUTO_INCREMENT,
 3    `Year` CHAR(4),
 4    `Month` CHAR(2),
 5    `Amount` DECIMAL(5,2),
 6    PRIMARY KEY(`Id`)
 7 );
 8 
 9 INSERT INTO `tb_amount`(`Year`, `Month`, `Amount`) VALUES(‘1991‘, ‘1‘, ‘1.1‘);
10 INSERT INTO `tb_amount`(`Year`, `Month`, `Amount`) VALUES(‘1991‘, ‘2‘, ‘1.2‘);
11 INSERT INTO `tb_amount`(`Year`, `Month`, `Amount`) VALUES(‘1991‘, ‘3‘, ‘1.3‘);
12 INSERT INTO `tb_amount`(`Year`, `Month`, `Amount`) VALUES(‘1991‘, ‘4‘, ‘1.4‘);
13 INSERT INTO `tb_amount`(`Year`, `Month`, `Amount`) VALUES(‘1992‘, ‘1‘, ‘2.1‘);
14 INSERT INTO `tb_amount`(`Year`, `Month`, `Amount`) VALUES(‘1992‘, ‘2‘, ‘2.2‘);
15 INSERT INTO `tb_amount`(`Year`, `Month`, `Amount`) VALUES(‘1992‘, ‘3‘, ‘2.3‘);
16 INSERT INTO `tb_amount`(`Year`, `Month`, `Amount`) VALUES(‘1992‘, ‘4‘, ‘2.4‘);
17 
18 SELECT `Year`,
19 (SELECT Amount FROM   tb_amount m WHERE `Month`=1   AND m.`Year`=tb_amount.`Year`) AS m1,
20 (SELECT Amount FROM   tb_amount m WHERE `Month`=2   AND m.`Year`=tb_amount.`Year`) AS m2,
21 (SELECT Amount FROM   tb_amount m WHERE `Month`=3   AND m.`Year`=tb_amount.`Year`) AS m3,
22 (SELECT Amount FROM   tb_amount m WHERE `Month`=4   AND m.`Year`=tb_amount.`Year`) AS m4
23 FROM tb_amount  GROUP BY `Year`;