题目
有两张表,表一内容有“学号、名称、性别、生日、家庭住址”,表二内容有“学号、学科、考试时间、得分”。假如每年6月7日、8日为高考时间,请编写一条sql语句,统计2017年所有学生的高考总成绩以及各科得分情况。
sql语句
SELECT
b.NAME,
sum( CASE WHEN a.SUBJECT = '语文' THEN a.score ELSE 0 END ) AS '语文',
sum( CASE WHEN a.SUBJECT = '数学' THEN a.score ELSE 0 END ) AS '数学',
sum( CASE WHEN a.SUBJECT = '综合' THEN a.score ELSE 0 END ) AS '综合',
sum( CASE WHEN a.SUBJECT = '英语' THEN a.score ELSE 0 END ) AS '英语',
sum( a.score ) AS '总分'
FROM
record a
JOIN student b ON a.sid = b.id
WHERE
a.time = '2017-06-07'
OR a.time = '2017-06-08'
GROUP BY
b.NAME
结果:
解释:
使用group by
分组之后,如果不使用sum()
,而是直接使用case ……
来获取分数,那你只能看到一个学科有值,因为mysql
中只会显示分组的第一条数据,如果使用sum()
,那么将会在该分组内搜索所有符合条件的值,这样才是最好的选择,本次也没有使用关联和子表,居然完成了横行数据到竖行数据的转换,真实难以想象
建表语句
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`id` int(20) NOT NULL AUTO_INCREMENT,
`name` varchar(20),
`gender` char(1),
`birthday` date,
`address` varchar(50),
PRIMARY KEY (`id`)
);
INSERT INTO `student` VALUES (1, '张三', '男', '2009-11-01', '北京');
INSERT INTO `student` VALUES (2, '李华', '女', '2009-11-01', '天津');
DROP TABLE IF EXISTS `record`;
CREATE TABLE `record` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`sid` int(11),
`subject` varchar(20),
`time` date,
`score` int(5),
PRIMARY KEY (`id`)
);
INSERT INTO `record` VALUES (1, 1, '语文', '2017-06-07', 100);
INSERT INTO `record` VALUES (2, 1, '数学', '2017-06-07', 100);
INSERT INTO `record` VALUES (3, 1, '综合', '2017-06-08', 100);
INSERT INTO `record` VALUES (4, 1, '英语', '2017-06-08', 100);
INSERT INTO `record` VALUES (5, 2, '语文', '2017-06-07', 120);
INSERT INTO `record` VALUES (6, 2, '数学', '2017-06-07', 120);
INSERT INTO `record` VALUES (7, 2, '综合', '2017-06-08', 120);
INSERT INTO `record` VALUES (8, 2, '英语', '2017-06-08', 120);
INSERT INTO `record` VALUES (9, 1, '语文', '2017-06-05', 150);
INSERT INTO `record` VALUES (10, 2, '英语', '2017-06-05', 150);
注意: record表中的最后两行数据其实没有什么意义,算是两行干扰数据,看看最终结果会不会受到它们的干扰
真题演练
虽然不太一样,但是是类似问题,就是将横着的信息,变成竖着的信息,sql语句如下:
SELECT
a.khjg AS 开户机构,
sum( CASE WHEN a.zhzt = 0 THEN b.zhye * c.hl ELSE 0 END ) AS 正常账户余额之和,
sum( CASE WHEN a.zhzt = 2 THEN b.zhye * c.hl ELSE 0 END ) AS 销户账户余额之和
FROM
t_ckzh a
JOIN t_zhye b ON a.zhdh = b.zhdh
JOIN t_hl c ON a.bz = c.bz
GROUP BY
a.khjg