建表先:
DROP TABLE IF EXISTS `temp`;
CREATE TABLE `temp` (
`year` varchar(10) DEFAULT NULL,
`month` varchar(10) DEFAULT NULL,
`amount` float(6,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `temp` VALUES ('1991', '1月', '1.10');
INSERT INTO `temp` VALUES ('1991', '2月', '1.20');
INSERT INTO `temp` VALUES ('1991', '3月', '1.30');
INSERT INTO `temp` VALUES ('1991', '4月', '1.40');
INSERT INTO `temp` VALUES ('1992', '1月', '2.50');
INSERT INTO `temp` VALUES ('1992', '2月', '3.60');
INSERT INTO `temp` VALUES ('1992', '3月', '3.70');
INSERT INTO `temp` VALUES ('1992', '4月', '3.80');
方法一:适用MySql数据库和Oracle数据库
case when用法
case colume
when condition then result
when condition then result
when condition then result
else result
end
SELECT `year`,
SUM(CASE `month` WHEN '1月' THEN amount ELSE 0 END) '1月',
SUM(CASE `month` WHEN '2月' THEN amount ELSE 0 END) '2月',
SUM(CASE `month` WHEN '3月' THEN amount ELSE 0 END) '3月',
SUM(CASE `month` WHEN '4月' THEN amount ELSE 0 END) '4月'
FROM temp
GROUP BY `year`;
方法二:适用Oracle数据库
decode()用法
decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)
SELECT `year`,
SUM(DECODE(`month`,'1月',1,0)) '1月',
SUM(DECODE(`month`,'2月',2,0)) '2月',
SUM(DECODE(`month`,'3月',3,0)) '3月',
SUM(DECODE(`month`,'4月',4,0)) '4月'
FROM temp
GROUP BY `year`;
方法三:适用Oracle数据库(11g)
SELECT * FROM temp pivot(MAX(amount) FOR Year in ('1月','2月','3月','4月'));