建表和插入测试数据:
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for exam
-- ----------------------------
DROP TABLE IF EXISTS `exam`;
CREATE TABLE `exam` (
`name` varchar(255) DEFAULT NULL,
`subject` varchar(255) DEFAULT NULL,
`score` double(255,0) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of exam
-- ----------------------------
INSERT INTO `exam` VALUES ('张三', '语文', '80');
INSERT INTO `exam` VALUES ('张三', '数学', '98');
INSERT INTO `exam` VALUES ('张三', '英语', '65');
INSERT INTO `exam` VALUES ('李四', '语文', '70');
INSERT INTO `exam` VALUES ('李四', '数学', '80');
INSERT INTO `exam` VALUES ('李四', '英语', '90');
查询的结果如下:
name subject score
张三 语文 80
张三 数学 98
张三 英语 65
李四 语文 70
李四 数学 80
李四 英语 90
(例子一)行转列:
sql语句:
select name as '姓名',
case when subject='语文' then score else 0 end as '语文',
case when subject='数学' then score else 0 end as '数学',
case when subject='英语' then score else 0 end as '英语'
from exam
结果如下
姓名 语文 数学 英语
张三 80 0 0
张三 0 98 0
张三 0 0 65
李四 70 0 0
李四 0 80 0
李四 0 0 90
不是想要的效果,我们需要加个分组,然后取最大的那个科目分数即可:
select name as '姓名',
max(case when subject='语文' then score else 0 end) as '语文',
max(case when subject='数学' then score else 0 end) as '数学',
max(case when subject='英语' then score else 0 end) as '英语'
from exam
group by name
结果:
姓名 语文 数学 英语
张三 80 98 65
李四 70 80 90
(例子二)使用case when then生成虚拟列统计,我们现在的科目数据里面每个人没有分文科和理科类别,如果我们想要按照文科和理科统计,应该怎么写?如下:
select name as '姓名',
case
when `subject`="数学" then '理科'
when `subject`="语文" then '文科'
when `subject`="英语" then '文科'
else '其他'
end as "科别"
,sum(score) '总分'
from exam
group by
name,
case
when `subject`="数学" then '理科'
when `subject`="语文" then '文科'
when `subject`="英语" then '文科'
else '其他'
end
结果:
姓名 科别 总分
张三 文科 145
张三 理科 98
李四 文科 160
李四 理科 80
符合我们的预期结果