实现效果
初始查询结果
目标查询结果
语句
-- case when
SELECT YEAR,
MAX(CASE MONTH WHEN '1' THEN AMOUNT END ) AS "1",
MAX(CASE MONTH WHEN '2' THEN AMOUNT END ) AS "2",
MAX(CASE MONTH WHEN '3' THEN AMOUNT END ) AS "3",
MAX(CASE MONTH WHEN '4' THEN AMOUNT END ) AS "4"
FROM SCORE
GROUP BY YEAR;
-- Oracle decode
SELECT YEAR,
MAX(DECODE(MONTH,'1',AMOUNT, null)) AS "1",
MAX(DECODE(MONTH,'2',AMOUNT, null)) AS "2",
MAX(DECODE(MONTH,'3',AMOUNT, null)) AS "3",
MAX(DECODE(MONTH,'4',AMOUNT, null)) AS "4"
FROM SCORE
GROUP BY YEAR;
数据
CREATE TABLE `SCORE` (
`YEAR` VARCHAR(10),
`MONTH` VARCHAR(10),
`AMOUNT` VARCHAR(10)
);
INSERT INTO `SCORE` VALUES ('1991', '1', '1.1');
INSERT INTO `SCORE` VALUES ('1991', '2', '1.2');
INSERT INTO `SCORE` VALUES ('1991', '3', '1.3');
INSERT INTO `SCORE` VALUES ('1991', '4', '1.4');
INSERT INTO `SCORE` VALUES ('1992', '1', '2.1');
INSERT INTO `SCORE` VALUES ('1992', '2', '2.2');
INSERT INTO `SCORE` VALUES ('1992', '3', '2.3');
INSERT INTO `SCORE` VALUES ('1992', '4', '2.4');