SQL面试题-行列互换-if、【case when】
http://www.cda.cn/view/21469.html
tb_lemon_grade中,表中字段id,student_name,course,score分别表示成绩id,学生姓名,课程名称,课程成绩,表中数据表1所示。请写出一条SQL,将表1的数据变成表2的形式
id 学生姓名 课程名称 课程成绩
1 张三 Linux 85
2 张三 MySQL 92
3 张三 Java 87
4 李四 Linux 96
5 李四 MySQL 89
6 李四 Java 100
7 王五 Linux 91
8 王五 MySQL 83
9 王五 Java 98
表1
学生姓名 Linux MySQL Java
张三 85 92 87
李四 96 89 100
王五 91 83 98
表2
一:创建表
CREATE TABLE tb_lemon_grade (
id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
student_name VARCHAR(20) DEFAULT NULL,
course VARCHAR(20) DEFAULT NULL,
score FLOAT DEFAULT '0');
二:初始化数据
INSERT INTO tb_lemon_grade (student_name, course, score) VALUES
("张三", "Linux", 85),
("张三", "MySQL", 92),
("张三", "Java", 87),
("李四", "Linux", 96),
("李四", "MySQL", 89),
("李四", "Java", 100),
("王五", "Linux", 91),
("王五", "MySQL", 83),
("王五", "Java", 98);
三:首先我们查询出所有数据,这个结果和我们的图1是一样的
select * from tb_lemon_grade;
四:使用常量列输出我们的目标结构
可以看到结果已经和我们的图二非常接近了
五:使用IF函数,替换我们的常量列,将成绩赋值到对应行的对应列
SELECT student_name,
IF(COURSE = 'Linux',SCORE,0) 'Linux',
IF(COURSE = 'MySQL',SCORE,0) 'MySQL',
IF(COURSE = 'Java',SCORE,0) 'Java'
FROM tb_lemon_grade;
运行SQL,结果如下所示:
六:我们来分析这个结果集,
在原始结构中,每一行表示了某个同学某一个科的成绩,以第一行为例,第一行是张三同学Linux的成绩,所以我们结果集中Linux有成绩为85,而其他两列MySQL和Java作为常量列,成绩为0。
再分析每个同学的成绩的所有行,如下图所示,每个方块内包含行中,就有该同学这门课程的成绩,并且该方块内其余行的成绩值为0。因此,不难想到,我们可以使用分组,通过分组提取出每科的成绩
七:分组,使用MAX函数取出最大值
(因为其中只有一行成绩为真实成绩,其他行值为0,所以最大值就是真实成绩)
SELECT student_name,
MAX(IF(COURSE = 'Linux',SCORE,0)) 'Linux',
MAX(IF(COURSE = 'MySQL',SCORE,0)) 'MySQL',
MAX(IF(COURSE = 'Java',SCORE,0)) 'Java'
FROM tb_lemon_grade
GROUP BY student_name;
八:也可以分组后,对每行数据进行求和,使用SUM函数,语句和结果如下:
SELECT student_name,
SUM(IF(COURSE = 'Linux',SCORE,0)) 'Linux',
SUM(IF(COURSE = 'MySQL',SCORE,0)) 'MySQL',
SUM(IF(COURSE = 'Java',SCORE,0)) 'Java'
FROM tb_lemon_grade
GROUP BY student_name;
九:既然使用IF语句可以达到效果,那使用CASE语句也是同样的效果
分组,使用MAX聚合函数
SELECT student_name,
max(CASE COURSE when 'Linux' THEN SCORE ELSE 0 END) as 'Linux',
max(CASE COURSE when 'MySQL' THEN SCORE ELSE 0 END) as 'MySQL',
max(CASE COURSE when 'Java' THEN SCORE ELSE 0 END) as 'Java'
FROM tb_lemon_grade
GROUP BY student_name;
结果如下图所示:
使用SUM,结果如下图所示
SELECT student_name,
SUM(CASE COURSE when 'Linux' THEN SCORE ELSE 0 END) as 'Linux',
SUM(CASE COURSE when 'MySQL' THEN SCORE ELSE 0 END) as 'MySQL',
SUM(CASE COURSE when 'Java' THEN SCORE ELSE 0 END) as 'Java'
FROM tb_lemon_grade
GROUP BY student_name;
最新文章
- iOS 局部变量 全局变量 成员变量
- 去掉Actionbar下的shadow
- lib库dll库的使用方法与关系
- Sphinx学习之sphinx的安装篇
- Java 枚举&;注解
- SRF之数据验证
- 编译ffmpeg(iOS)
- 从Count看Oracle执行计划的选择
- Http,Https (SSL)的Url绝对路径,相对路径解决方案Security Switch 4.2 英文帮助文档 分类: ASP.NET 2014-10-28 10:50 147人阅读 评论(1) 收藏
- hiho #1079 : 离散化
- Logback相关知识汇总
- js:深闭包(范围:上)
- Java笔记:与系统交互、系统相关的类,Object类
- ZOJ 2059 The Twin Towers
- http压测工具wrk使用
- https下 http的会被阻塞 This request has been blocked; the content must be served over HTTPS.
- JDBC MVC框架实现用户登录
- kbmMW均衡负载与容灾(3)(转载红鱼儿)
- <;转>;特征工程(二)
- Kafka网络模型分析