197. 上升的温度
用到 DATEDIFF-返回两个日期之间的天数
select a.id
from Weather as a, Weather as b
where DATEDIFF(a.recordDate, b.recordDate) = 1
and a.Temperature > b.Temperature
不太理解题解里为什么要用 join on
1179. 重新格式化部门表
用到 行转列-case when
select id,
sum(case when month='Jan' then revenue end) as Jan_Revenue,
sum(case when month='Feb' then revenue end) as Feb_Revenue,
sum(case when month='Mar' then revenue end) as Mar_Revenue,
sum(case when month='Apr' then revenue end) as Apr_Revenue,
sum(case when month='May' then revenue end) as May_Revenue,
sum(case when month='Jun' then revenue end) as Jun_Revenue,
sum(case when month='Jul' then revenue end) as Jul_Revenue,
sum(case when month='Aug' then revenue end) as Aug_Revenue,
sum(case when month='Sep' then revenue end) as Sep_Revenue,
sum(case when month='Oct' then revenue end) as Oct_Revenue,
sum(case when month='Nov' then revenue end) as Nov_Revenue,
sum(case when month='Dec' then revenue end) as Dec_Revenue
from Department
group by id
order by id
596. 超过5名学生的课
有个小坑-重修的学生要剔除,不能计数
DISTINCT-用于返回唯一不同的值
select class
from courses
group by class
having count(DISTINCT student) >= 5
620. 有趣的电影
奇数-MOD()-mod(id,2)=1
偶数-MOD()-mod(id,2)=0
select *
from cinema
where description != 'boring'
and id % 2 = 1
order by rating desc
627. 变更性别
条件判断,IF(condition,arg1, arg2)如果condition为真,arg1,否则arg2
update salary set sex=if(sex='m','f','m')