leetcode 数据库题第二弹
- 196. 删除重复的电子邮箱
- 197. 上升的温度
- 262. 行程和用户
- 511. 游戏玩法分析 I
- 550. 游戏玩法分析 IV
- 570. 至少有5名直接下属的经理
- 577. 员工奖金
- 小结
196. 删除重复的电子邮箱
题目地址：https://leetcode.cn/problems/delete-duplicate-emails/
嗯。。。。这个是考 delete 指令的，因为 oracle 不熟，所以单独写了个指令，其他的用排名函数完成。对 group 和 min 组合，结合 not in 也可以完成。
# 一拖三
delete from person
where id not in (select min(id) from persongroup by email
)
# mysql & mssql
delete from person
where id in (select id from (select *,row_number() over(partition by email order by id) ridfrom person) awhere rid>1
)
CSDN 文盲老顾的博客，https://blog.csdn.net/superwfei
197. 上升的温度
题目地址：https://leetcode.cn/problems/rising-temperature/
这次就是考日期计算方式，每个数据库都不一样
# oracle
select a.id
from weather a
where exists(select 1 from weather b where a.recordDate-b.recordDate = 1 and a.temperature>b.temperature
)
# mysql
select a.id
from weather a
where exists(select 1 from weather b where datediff(a.recordDate,b.recordDate) = 1 and a.temperature>b.temperature
)
# mssql
select a.id
from weather a
where exists(select 1 from weather b where datediff(d,a.recordDate,b.recordDate) = -1 and a.temperature>b.temperature
)
262. 行程和用户
题目地址：https://leetcode.cn/problems/trips-and-users/
考点是分组和聚合，困难评价名不符实。哦，还有个保留两位小数。
# mssql
select request_at Day,convert(decimal(16,2),sum(case when status<>'completed' then 1.0 else 0.0 end) / count(0)) [cancellation Rate]
from trips t
left join users c on t.client_id=c.users_id
left join users d on t.driver_id=d.users_id
where c.banned='No' and d.banned='No' and request_at between '2013-10-01' and '2013-10-03'
group by request_at
# mysql
select request_at Day,round(sum(case when status<>'completed' then 1.0 else 0.0 end) / count(0),2) `cancellation Rate`
from trips t
left join users c on t.client_id=c.users_id
left join users d on t.driver_id=d.users_id
where c.banned='No' and d.banned='No' and request_at between '2013-10-01' and '2013-10-03'
group by request_at
# oracle
select request_at Day,cast(sum(case when status<>'completed' then 1.0 else 0.0 end) / count(0) as numeric(4,2)) "Cancellation Rate"
from trips t
left join users c on t.client_id=c.users_id
left join users d on t.driver_id=d.users_id
where c.banned='No' and d.banned='No' and request_at between '2013-10-01' and '2013-10-03'
group by request_at
还以为 oracle 不进行补零会失败，没想到通过了
511. 游戏玩法分析 I
题目地址：https://leetcode.cn/problems/game-play-analysis-i/
额。。。。排名函数还是很好用的，然后输出只输出日期即可。
# oracle
select player_id,to_char(event_date,'YYYY-mm-DD') first_login
from (select a.*,row_number() over(partition by player_id order by event_date) rid from activity a
) a
where rid=1
# mssql
select player_id,convert(date,event_date) first_login
from (select a.*,row_number() over(partition by player_id order by event_date) rid from activity a
) a
where rid=1
# 一拖三
select player_id,min(event_date) first_login
from activity
group by player_id
550. 游戏玩法分析 IV
题目地址：https://leetcode.cn/problems/game-play-analysis-iv/
嗯。。。游戏玩法分析2和3都是会员题目，老顾是看不到的，看别人题解吧。
这个4的题目要考啥，老顾有点拿不准，是 group 吗？还是要考子查询？
#mssql
with t as (select player_id id,min(event_date) mn,1 tp from activitygroup by player_id
)
select convert(decimal(4,2),(select convert(float,count(0))from t binner join activity a on b.id=a.player_id and datediff(d,b.mn,a.event_date)=1
) / (select count(0) from t)) fraction
# mysql
with t as (select player_id,min(event_date) mn from activitygroup by player_id
)
select round(sum(case when exists(select 1 from activity where player_id=a.player_id and datediff(event_date,a.mn)=1
) then 1 else 0 end) / count(0),2) as fraction
from t a
# oracle
select round(c1.c1 / c2.cnt,2) fraction
from (select count(0) as c1from (select player_id id,min(event_date) mn from activitygroup by player_id) binner join activity a on a.player_id=b.id and a.event_date-b.mn = 1
) c1,(select count(0) cntfrom (select player_id from activitygroup by player_id) x
) c2
是实在不知道 oracle 怎么直接用子查询结果作为一个列哎使用 ，只好用笨办法来实现一下。
570. 至少有5名直接下属的经理
题目地址：https://leetcode.cn/problems/managers-with-at-least-5-direct-reports/
这个题目，不管是用 row_number 协助，还是用 group 协助，都一样简单，还是用group吧，可以一拖三。而且有 having 辅助，比row_number 简单一些。
select e.name
from (select managerid from employee group by manageridhaving(count(0)>4)
) a
inner join employee e on a.managerid=e.id
577. 员工奖金
题目地址：https://leetcode.cn/problems/employee-bonus/
又一个基础题目，这次考 null 情况和 left join。
#mysql
select name,bonus
from employee e
left join bonus b on b.empId=e.empId
where ifnull(bonus,0)<1000
# mssql
select name,bonus
from employee e
left join bonus b on b.empId=e.empId
where isnull(bonus,0)<1000
# oracle
select name,bonus
from employee e
left join bonus b on b.empId=e.empId
where nvl(bonus,0)<1000
可以看到，业务逻辑完全相同，仅仅是在处理 null 时，使用了各自数据库的特定函数罢了。
小结
又刷了几个数据库题目，无奈的是老顾没有会员，所以有些题目就跳过了。还有就是，题目序号开始不连续了，真就不知道该怎么给文章起标题了。
这次做几个题目，还是算作基础题吧，都没有特别复杂的关联查询和业务逻辑在里面。
本次这几个题目，主要还是一些基础知识点的应用，group，group 加 having，子查询嵌套，保留小数的方式，日期范围的选择、求差之类的，教程里基本都有。
所有的数据库题目，和算法题唯一相同的就是，你需要将需求，转成各种逻辑，但是由于数据库查询逻辑套路就在这里摆着，所以用熟了一个数据库，其他数据库做简单查询还是很容易的。
在做这几个题目的时候，oracle 也弄了一下，因为在群里听到小伙伴说，oracle附加列的时候，其他列都需要加表别名前缀，试了试果然如此。比如第511题，就成功的按照这个方式实现出来了。
但是，还有一个方式的需求没有实现成功，就是用子查询结果，单行单列的结果，作为附加列，然后产生后续计算的方式，没能弄明白到底哪里出问题了，oracle 一直报错。顺便吐槽一下，oracle 报错信息太简陋了，就一个执行出错，啥原因啥反馈都没有了。
最后，对于每个指令在不同的环境下运行的效率问题，老顾表示不想讨论，数据库的优化，尤其是查询优化算是一个大项了，各人有各自的玩法，针对这种公共的，非大量数据集的内容，实在不值得专门考虑优化问题，而且，只要不是特别的交叉关联，一般这种题目也很难超时，能实现逻辑返回正确结果即可，大可不必像算法题一样，几毫秒几毫秒的在那里琢磨。
对于绝大多数数据库来说，都是符合 t-sql 语法规范的，刨去各自数据库自己的函数实现，很多指令其实都能做到一拖三的。对于数据库来说，基本的查询，永远是通用的多于个性的，学好 t-sql 对于数据库转型是很有必要的。当然，要做数据迁移，那仅仅会 t-sql 肯定不够，毕竟，各自的存储过程、自定义函数、自定义类型，实现机制都有差异，没办法全部自动生成迁移代码。
最后的最后，给大家一点小建议，对于数据库指令，也尽可能的保持良好的书写习惯，保持结构化缩进，这样就很容易维护了。另外就是一些奇葩的情况，可以参考老顾以前的文章《SqlDataAdapter.Fill()时超时的一个另类的原因：你的存储过程中有超长的代码或注释吗？》，真就没想到。不过在 stockoverflow 上，倒是看到设置一个什么环境变量，就可以修正这个超长指令异常的问题，但老顾忘记记录下来了.。
