【mysql经典题目】行转列

最新推荐文章于 2023-04-27 10:10:49 发布

l23456789o

最新推荐文章于 2023-04-27 10:10:49 发布

阅读量143

点赞数

分类专栏：数据库

原文链接：http://www.cnblogs.com/h07061108/p/mysql_questions.html#3806338

版权

数据库专栏收录该内容

49 篇文章 0 订阅

订阅专栏

参考：http://www.cnblogs.com/h07061108/p/mysql_questions.html#3806338

实现如下效果

CREATE TABLE IF NOT EXISTS tb_amount(
   `Id` INT NOT NULL AUTO_INCREMENT,
   `Year` CHAR(4),
   `Month` CHAR(2),
   `Amount` DECIMAL(5,2),
   PRIMARY KEY(`Id`)
);

INSERT INTO `tb_amount`(`Year`, `Month`, `Amount`) VALUES('1991', '1', '1.1');
INSERT INTO `tb_amount`(`Year`, `Month`, `Amount`) VALUES('1991', '2', '1.2');
INSERT INTO `tb_amount`(`Year`, `Month`, `Amount`) VALUES('1991', '3', '1.3');
INSERT INTO `tb_amount`(`Year`, `Month`, `Amount`) VALUES('1991', '4', '1.4');
INSERT INTO `tb_amount`(`Year`, `Month`, `Amount`) VALUES('1992', '1', '2.1');
INSERT INTO `tb_amount`(`Year`, `Month`, `Amount`) VALUES('1992', '2', '2.2');
INSERT INTO `tb_amount`(`Year`, `Month`, `Amount`) VALUES('1992', '3', '2.3');
INSERT INTO `tb_amount`(`Year`, `Month`, `Amount`) VALUES('1992', '4', '2.4');

SELECT `Year`,
(SELECT Amount FROM   tb_amount m WHERE `Month`=1   AND m.`Year`=tb_amount.`Year`) AS m1,
(SELECT Amount FROM   tb_amount m WHERE `Month`=2   AND m.`Year`=tb_amount.`Year`) AS m2,
(SELECT Amount FROM   tb_amount m WHERE `Month`=3   AND m.`Year`=tb_amount.`Year`) AS m3,
(SELECT Amount FROM   tb_amount m WHERE `Month`=4   AND m.`Year`=tb_amount.`Year`) AS m4
FROM tb_amount  GROUP BY `Year`;

或者



select year,
max(case month when 1 then amount else 0 end ) m1,
max(case month when 2 then amount else 0 end) m2,
max(case month when 3 then amount else 0 end) m3,
max(case month when 4 then amount else 0 end) m4
from tb_amount
group by year;