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 一定要给这个表命名,否则会报错
);
- 上升到的温度
这道题我首先想到的是通过id链接的自链接,因为给的势力里面,日期是从上往下的。但是提交之后发现错误,因为提交用的数据日期不是升序。所以考虑到用日期来做链接,即datediff函数。
select w2.id
from weather w1
join weather w2
on datediff(w2.recorddate,w1.recorddate)=1
and w1.temperature < w2.temperature;
- 用户取消率
这道题很复杂。首先是两个表连接,不能直接使用用户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; # 根据时间分组
- 大的国家
这个题主要是运用where和or
select name,population,area
from world
where area >3000000
or population > 25000000;
- 超过5名学生的课
这个直接可以想到通过group by 之后计数,然后要注意要对group by之后的结果筛选用having语句
select class
from courses
group by class
having count(distinct student) >=5
- 体育馆的人流量
这个也是同时连接表,然后大于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;
- 有趣的电影
三个要求,第一,非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;
- 换座位
这个是首先要找到规律,奇数座位号要加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;
- 重新格式化部门表
这个就是重新命名表达式叭,感觉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;