-- 行转列
CREATE TABLE `st_grade` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`stu_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`course` varchar(20) DEFAULT NULL,
`score` float(4,1) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
INSERT INTO `st_grade` VALUES ('1', '张三', '语文', '86.0')
,('2', '张三', '数学', '90.0')
,('3', '张三', '英语', '75.0')
,('4', '李四', '语文', '92.0')
,('5', '李四', '数学', '93.0')
,('6', '李四', '英语', '96.0')
,('7', '王五', '语文', '82.0')
,('8', '王五', '数学', '71.0')
,('9', '王五', '英语', '74.0');
select stu_name ,
max(case course when '语文' then score else 0 end ) 语文,
max(case course when '数学' then score else 0 end ) 数学,
max(case course when '英语' then score else 0 end ) 英语
from st_grade
group by stu_name;
-- 列转行
CREATE TABLE `st_grade1` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`stu_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`course1` float(4,1) DEFAULT NULL,
`course2` float(4,1) DEFAULT NULL,
`course3` float(4,1) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
insert into `st_grade1` (id,stu_name,course1,course2,course3) values(1,'张三', 86.0, 90.0, 75.0),(2,'李四', 92.0, 93.0, 96.0),(3,
'王五', 82.0, 71.0, 74.0);
select stu_name, '语文', course1 score from st_grade1
union select stu_name, '数学', course2 score from st_grade1
union select stu_name, '英语', course3 score from st_grade1
order by stu_name;
mysql行转列和列转行
最新推荐文章于 2023-07-12 21:47:21 发布