Leetcode SQL题库D4 196/197/262/595/596/601/620/626/1179

Leetcode D4
196. 删除重复的电子邮件，并保留最小的id和Email
这道题首先是delete 的使用：

delete from 表明 where 条件

然后重复的电子邮件：这个是使用的group by的函数筛选出id最小的Email，然后删除id不在其中的Email，这样就保留了id最小的Email。

     delete from person
     where id not in (
         select a.id from 
         (select min(id) id,email #要重更新给min(id)命名 否则会报错，说不存在a.id
         from person
         group by email ) as a # as a 一定要给这个表命名，否则会报错
     );

197. 上升到的温度
     这道题我首先想到的是通过id链接的自链接，因为给的势力里面，日期是从上往下的。但是提交之后发现错误，因为提交用的数据日期不是升序。所以考虑到用日期来做链接，即datediff函数。

     select w2.id 
     from weather w1
     join weather w2
     on datediff(w2.recorddate,w1.recorddate)=1
     and w1.temperature < w2.temperature;

198. 用户取消率
     这道题很复杂。首先是两个表连接，不能直接使用用户id和客户id，司机id来排除不在，因为会出现错误，所以要链接两次。然后是取消率的计算。

     select t.request_at 'Day',round(
         sum(if (t.status='completed',0,1))/count(t.status),2
     ) as 'Cancellation Rate' #计算取消率
     from trips t
     join users u1     #客户表，被禁止的
     on t.client_id=u1.users_id
     and u1.banned = 'No'
     join users u2    #司机表，司机被禁止的
     on t.driver_id=u2.users_id
     and u2.banned = 'No'
     where t.request_at between '2013-10-01' and '2013-10-03'
     group by t.request_at;  # 根据时间分组

199. 大的国家
     这个题主要是运用where和or

     select name,population,area
     from world
     where area >3000000
     or population > 25000000;

200. 超过5名学生的课
     这个直接可以想到通过group by 之后计数，然后要注意要对group by之后的结果筛选用having语句

     select class
     from courses
     group by class
     having count(distinct student) >=5

201. 体育馆的人流量
     这个也是同时连接表，然后大于100筛选。但是在链接的时候要考虑，高峰分别为第一天，第二天和第三天的情况。

     select distinct s1.*
     from stadium s1, stadium s2,stadium s3
     where (s1.people >= 100 and s2.people >= 100 and  s3.people >= 100)
     and((s1.id-s2.id = 1 and s3.id-s1.id=1)
     or (s1.id - s2.id = 1 and s2.id - s3.id=1)
     or (s2.id-s1.id=1 and s3.id -s2.id=1))
     order by s1.id;

620. 有趣的电影
     三个要求，第一，非boring，可以采用<>,第二，id为奇数，可以采用mod(id,2)=1效率比id%2=1要高，第三，按rating排序，采用order by DESC。

     select *
     from cinema
     where description <> 'boring'
     and mod(id,2) =1
     order by rating DESC;

626. 换座位
     这个是首先要找到规律，奇数座位号要加1，偶数座位要加2。然后题目中也有提示，在最后一个若是奇数，则他的座位号不变。然后要加入子查询

     select (
         case 
         when mod(id,2)=1 and c <> id then id + 1
         when mod(id,2)=1 and c = id then id
         else id -1  #座位变化
         end ) as id, #一定要重新命名
         student
     from seat,(select
                 count(*) as c
                from
                 seat) as b
     order by id;

1179. 重新格式化部门表
      这个就是重新命名表达式叭，感觉2333。然后通过sum和if都可以。就是十二个月打起来麻烦一点。

      select id,
             sum(if (month = 'Jan',revenue,null)) as 'Jan_Revenue' ,
             sum(if (month = 'Feb',revenue,null)) as 'Feb_Revenue' ,
             sum(if (month = 'Mar',revenue,null)) as 'Mar_Revenue' ,
             sum(if (month = 'Apr',revenue,null)) as 'Apr_Revenue' ,
             sum(if (month = 'May',revenue,null)) as 'May_Revenue' ,
             sum(if (month = 'Jun',revenue,null)) as 'Jun_Revenue' ,
             sum(if (month = 'Jul',revenue,null)) as 'Jul_Revenue' ,
             sum(if (month = 'Aug',revenue,null)) as 'Aug_Revenue' ,
             sum(if (month = 'Sep',revenue,null)) as 'Sep_Revenue' ,
             sum(if (month = 'Oct',revenue,null)) as 'Oct_Revenue' ,
             sum(if (month = 'Nov',revenue,null)) as 'Nov_Revenue' ,
             sum(if (month = 'Dec',revenue,null)) as 'Dec_Revenue' 
      from department
      group by id;