行轉列

• sql腳本

CREATE TABLE `score` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(40) COLLATE utf8mb4_bin DEFAULT NULL,
  `subject` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  `score` int(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

-- ----------------------------
-- Records of score
-- ----------------------------
BEGIN;
INSERT INTO `score` VALUES (1, '張三', '語文', 80);
INSERT INTO `score` VALUES (2, '張三', '數學', 85);
INSERT INTO `score` VALUES (3, '張三', '英語', 90);
INSERT INTO `score` VALUES (4, '李四', '語文', 60);
INSERT INTO `score` VALUES (5, '李四', '數學', 50);
INSERT INTO `score` VALUES (6, '李四', '英語', 70);
COMMIT;

• 代碼實現

-- 行轉列
select NAME,
MAX(CASE SUBJECT WHEN '語文' then score end ) '語文',
MAX(CASE SUBJECT WHEN '數學' THEN score END) '數學',
MAX(CASE SUBJECT WHEN '英語' THEN score END) '英語'
from score GROUP BY NAME

• 結果
  

列轉行

• sql腳本

CREATE TABLE `score2` (
  `id` int(255) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  `yw_score` int(5) DEFAULT NULL,
  `sx_score` int(5) DEFAULT NULL,
  `yy_score` int(5) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

-- ----------------------------
-- Records of score2
-- ----------------------------
BEGIN;
INSERT INTO `score2` VALUES (1, '張三', 80, 90, 85);
INSERT INTO `score2` VALUES (2, '李四', 70, 50, 80);
COMMIT;

• 代碼實現

-- 自定義常量,先執行定義變量的代碼，再執行列轉行的代碼
set @num:=0;

select @num
-- 列轉行
select @num:=@num +1 'id', NAME, '語文' as 'subject', yw_score as SCORE from score2
UNION
select @num:=@num +1 'id', NAME, '數學' as 'subject', sx_score as SCORE from score2
UNION
select @num:=@num +1 'id', NAME ,'英語' as 'subject', yy_score as SCORE from score2