之前一个公司面试的笔试题,记录一下,题目如下:
1.请写出查询下表的 SQL(请注明是那个数据库产品的,如: MySQL、Oracle 等),表结构和输出结果如下。
➢表结构和数据:
ID | 姓名 | 课程 | 成绩 |
---|---|---|---|
10000 | 李雷 | 语文 | 80 |
10001 | 李雷 | 数学 | 87 |
10002 | 韩梅 | 语文 | 97 |
10003 | 李雷 | 英语 | 68 |
10004 | 韩梅 | 数学 | 53 |
10005 | 张美 | 语文 | 90 |
10006 | 张美 | 数学 | 94 |
10007 | 韩梅 | 英语 | 95 |
10008 | 张美 | 英语 | 92 |
输出结果:
姓名 | 语文 | 数学 | 英语 |
---|---|---|---|
张美 | 90 | 94 | 92 |
李雷 | 80 | 87 | 68 |
韩梅 | 97 | 53 | 95 |
方法1:sum(if(条件,列值,0))
条件为真时,执行列值(字段名)求和,条件为假时为0求和(当然还是0)
MySQL执行代码如下:
SELECT t.`姓名` AS `姓名`,
SUM(IF(t.`课程`='语文', t.`成绩`, 0)) AS `语文`,
SUM(IF(t.`课程`='数学', t.`成绩`, 0)) AS `数学`,
SUM(IF(t.`课程`='英语', t.`成绩`, 0)) AS `英语`
FROM csdnTest t
GROUP BY t.姓名
结果:
方法2:MAX(CASE WHEN 条件 THEN 结果 END)
case when 如果条件为真,则执行结果。如果条件为假,则end结束
MySQL执行代码如下:
SELECT t.`姓名` AS `姓名`,
MAX(CASE WHEN t.`课程`='语文' THEN t.`成绩` END) AS `语文`,
MAX(CASE WHEN t.`课程`='数学' THEN t.`成绩` END) AS `数学`,
MAX(CASE WHEN t.`课程`='英语' THEN t.`成绩` END) AS `英语`
FROM csdnTest t
GROUP BY t.姓名
结果:
(1)SUM() :GROUP BY根据 t.姓名 进行分组,t.姓名 对应的 t.`课程`='语文' 的记录只有一条,所以SUM() 的值就等于对应那一条记录的的值。如果有多条 t.`课程`='语文',SUM() 的值则为满足条件的累加值,如李雷一条语文成绩记录为80,另一条记录为2,则李雷语文成绩为82,所以SUM()适合一个学生的课程对应多条成绩数据,比如多学期求和。
(2)MAX() :此时 t.姓名 对应的 t.`课程`='语文' 的值将会是这多条记录里面值最大的一个。所以MAX()适合一个学生的课程对应单条成绩记录,比如单学期求和。
但是正常情况下,一个学生对应一个课程只有一个分数,因此可以使用SUM()、MAX()、MIN()、AVG()等聚合函数都可以达到行转列的效果。
如果有错误或者不妥之处,请留言指正,Thanks……