巧用 MySQL Group By 和IF 解法

 

解决方案： 
分析建表： 

模拟建表SQL语句：

/*
Navicat MySQL Data Transfer

Source Server         : localhost_3306
Source Server Version : 50130
Source Host           : localhost:3306
Source Database       : tempdb

Target Server Type    : MYSQL
Target Server Version : 50130
File Encoding         : 65001

Date: 2017-03-11 18:19:08
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `t_time`
-- ----------------------------
DROP TABLE IF EXISTS `t_time`;
CREATE TABLE `t_time` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `year` varchar(255) DEFAULT NULL,
  `month` varchar(255) DEFAULT NULL,
  `amount` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of t_time
-- ----------------------------
INSERT INTO `t_time` VALUES ('1', '1991', '1', '1.1');
INSERT INTO `t_time` VALUES ('2', '1991', '2', '1.2');
INSERT INTO `t_time` VALUES ('3', '1991', '3', '1.3');
INSERT INTO `t_time` VALUES ('4', '1991', '4', '1.4');
INSERT INTO `t_time` VALUES ('5', '1992', '1', '2.1');
INSERT INTO `t_time` VALUES ('6', '1992', '2', '2.2');
INSERT INTO `t_time` VALUES ('7', '1992', '3', '2.3');
INSERT INTO `t_time` VALUES ('8', '1992', '4', '2.4');

Answer:

SELECT
year,
IF(t_time.`year`="1991",(SELECT t_time.amount FROM t_time WHERE t_time.`month`="1" LIMIT 0,1),(SELECT t_time.amount FROM t_time WHERE t_time.`month`="1" LIMIT 1,2)) AS M1,
IF(t_time.`year`="1991",(SELECT t_time.amount FROM t_time WHERE t_time.`month`="2" LIMIT 0,1),(SELECT t_time.amount FROM t_time WHERE t_time.`month`="2" LIMIT 1,2)) AS M2,
IF(t_time.`year`="1991",(SELECT t_time.amount FROM t_time WHERE t_time.`month`="3" LIMIT 0,1),(SELECT t_time.amount FROM t_time WHERE t_time.`month`="3" LIMIT 1,2)) AS M3,
IF(t_time.`year`="1991",(SELECT t_time.amount FROM t_time WHERE t_time.`month`="4" LIMIT 0,1),(SELECT t_time.amount FROM t_time WHERE t_time.`month`="4" LIMIT 1,2)) AS M3
FROM t_time
GROUP BY year

运行结果：