MySQL横竖表转换

shadow_zed

已于 2022-06-13 16:07:52 修改

阅读量633

点赞数

文章标签： mysql sql 数据库

于 2022-06-13 16:06:07 首次发布

原文链接：https://blog.csdn.net/qq_37171353/article/details/119393755

版权

竖表变横表

CREATE TABLE `student` (
  `id` int(11) NOT NULL,
  `sub` varchar(255) DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  `userid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '语文', '98', '1');
INSERT INTO `student` VALUES ('2', '数学', '99', '1');
INSERT INTO `student` VALUES ('3', '英语', '66', '1');
INSERT INTO `student` VALUES ('4', '语文', '198', '11');
INSERT INTO `student` VALUES ('5', '数学', '991', '11');
INSERT INTO `student` VALUES ('6', '英语', '661', '11');

横表变竖表

CREATE TABLE `student` (
  `id` int(11) NOT NULL,
  `chinese` int(11) DEFAULT NULL,
  `english` int(11) DEFAULT NULL,
  `math` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '98', '89');
INSERT INTO `student` VALUES ('2', '99', '99');
INSERT INTO `student` VALUES ('3', '66', '99');

select * from (
select id,"语文" as subject,chinese as score from student
UNION  
select id,"英语" as subject,english as score from student
UNION
select id,"数学" as subject,math as score from student
) tb ORDER BY id;