力扣-数据库练习-笔记

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')