SQL 查询数据横向展示结果

实现效果#

初始查询结果#

目标查询结果#

语句#

-- 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');