一、先来看一个例子
1.mysql的写法可能写的有些复杂:
2.oracle的写法:
oracle SELECT t.`NAME`,
SUM(case t.`subject` when '语文' then decode(t.SCORE,NULL,0,t.SCORE) else 0 end) as china,
SUM(case t.`subject` when '数学' then decode(t.SCORE,NULL,0,t.SCORE) else 0 end) as math,
SUM(case t.`subject` when '英语' then decode(t.SCORE,NULL,0,t.SCORE) else 0 end) as eng
from score t
group by t.name
二、看看面试中的出题
1、题目
SQL试题2
一、
教师号 星期号 是否有课
1 2 有
1 3 有
2 1 有
3 2 有`
1 2 有
写一条sql语句让你变为这样的表
教师号 星期一 星期二 星期三
1 2 1
2 1
3 1
各星期下的数字表示:对应的教师在星期几已经排的课数
2、SQL语句
SELECT a.teacher AS '教师号',
CASE WHEN a.day1 IS NULL THEN 0 ELSE a.day1 END AS '星期一',
CASE WHEN a.day2 IS NULL THEN 0 ELSE a.day2 END AS '星期二',
CASE WHEN a.day3 IS NULL THEN 0 ELSE a.day3 END AS '星期三' FROM
(SELECT t.`t_number` AS teacher,
SUM(CASE t.`t_day` WHEN '1' THEN t.class ELSE 0 END ) AS day1,
SUM(CASE t.`t_day` WHEN '2' THEN t.class ELSE 0 END ) AS day2,
SUM(CASE t.`t_day` WHEN '3' THEN t.class ELSE 0 END ) AS day3
FROM
(SELECT e.t_number,e.t_day, CASE e.`t_isclass` WHEN '有' THEN 1 ELSE 0 END AS class FROM teacher e) AS t
GROUP BY t.`t_number`) AS a
3、建表
4、效果
可以看出与要给出的结果一样其中还对会出现空做了判断,当然在真实的面试中还是不要画蛇添足了
5、这样也是可以达到效果的
SELECT t.`t_number` AS '教师号',
SUM(CASE t.`t_day` WHEN '1' THEN t.class ELSE 0 END ) AS '星期一',
SUM(CASE t.`t_day` WHEN '2' THEN t.class ELSE 0 END ) AS '星期二',
SUM(CASE t.`t_day` WHEN '3' THEN t.class ELSE 0 END ) AS '星期三'
FROM
(SELECT e.t_number,e.t_day, CASE e.`t_isclass` WHEN '有' THEN 1 ELSE 0 END AS class FROM teacher e) AS t
GROUP BY t.`t_number`
总结:其考察了对sql语句的子查询和case,when,else,end函数的运用,多想一会其实也就不难了,一步步的写下去。