Leetcode--SQL刷题(176-262)

二刷：
-- 176 求某个字段的第2个最大值，两种方法(先limit再group by也不错)
-- 177 熟悉函数的写法
-- 178 实现dense_rank()的经典方法,以及cast的使用
-- 180 同理：结合178体现了建造辅助列的核心思想
-- 184 学会使用多字段in，但是效果不好
-- 185 看自己二刷的方法，彻底掌握辅助列方法; 掌握官方那个方法中子查询的使用
-- 196 其实先做一个GROUP BY拿到ID，然后再做一个JOIN即可
-- 262 一轮的方法不对，看一下正确方案(应该有两个限制条件)

176.第二高的薪水

# Write your MySQL query statement below
答案一:常规思路
select IFNULL((select distinct(Salary) 
from Employee
order by Salary desc
limit 1,1),null) as SecondHighestSalary;

答案二:去掉最大值
select max(Salary) SecondHighestSalary
from employee
where
salary<(select max(salary) from employee)

177.第N高的薪水

FUNCTION如果查询不到结果会自动返回一个NULL.
所以我认为这个题没有必要嵌套IFNULL函数.
SET设置变量的时候是需要加分号的.

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
SET N=N-1;
RETURN (
      # Write your MySQL query statement below.
      SELECT DISTINCT salary FROM employee ORDER BY salary DESC LIMIT N,1);
END

但是如果这个demo要在实际系统中要运行,应该标明存储过程特性
delimiter &&
create function sal_grade(n int) returns int
reads sql data
begin
set N=n-1;
return(select distinct sal from emp order by sal desc limit N,1);
end
&&

178.分数排名

方法一:但是LeetCode中还不支持,并且很慢
# Write your MySQL query statement below
SELECT Score, dense_rank() over(ORDER BY score DESC) 'Rank' FROM Scores;

方法二:很慢
SELECT Score, (SELECT count(DISTINCT score) FROM Scores WHERE score >= s.score) AS Rank FROM Scores s ORDER BY Score DESC ;

方法三:超级好
mysql中变量不用事前申明，在用的时候直接用“@变量名”使用就可以了。
第一种用法：set @num=1; 或set @num:=1;  在set,update中都是赋值的意思
第二种用法：select @num:=1; 或 select @num:=字段名 from 表名 where ……
注意上面两种赋值符号，使用set时可以用“=”或“：=”，但是使用select时必须用“：=赋值”

SELECT Score,cast((
    CASE
    WHEN @prev = Score THEN @rk
    WHEN @prev := Score THEN @rk := @rk + 1
    else @rk := @rk + 1
    END) as UNSIGNED INTEGER) AS Rank
FROM Scores, (SELECT @rk := 0, @prev := null) r
ORDER BY Score DESC;

通过这里重点理解case函数.

180. 连续出现的数字

注意题目要求:连续出现三次

方法1: 0.9306
select distinct a.Num as ConsecutiveNums
from Logs as a left join logs b on a.num=b.num and a.id=b.id+1
left join logs c on b.num=c.num and b.id=c.id+1
where b.num is not null and c.num is not null;

方法2: 和方法1本质相同,注意差别.0.9569
select distinct a.Num as ConsecutiveNums
from Logs as a join logs b on a.num=b.num and a.id=b.id+1
join logs c on b.num=c.num and b.id=c.id+1;

方法3:超棒 0.9933  所以我们要尽量减少join操作
select distinct Num as ConsecutiveNums
from (
  select Num, 
    case 
      when @prev = Num then @count := @count + 1
      when @prev := Num then @count := 1
    end as CNT
  from Logs, (select @prev := null,@count := null) as t
) as temp
where temp.CNT = 3;

我发现优秀的代码都会   建造辅助列!!!

183. 从不订购的客户

select Name Customers from customers c left join orders o
on c.id=o.customerid
where customerid is null;

select Name Customers from customers
where id not in(select  customerid from orders where coustomerid  is not null);

这里我给出了两种答案
这两种方法差不多快 但是如果把第二种的限制where coustomerid is not null去掉会超级快

184.部门工资最高的员工

通过这个题理解:join连接有时候可以作为筛选方法使用!

方法一 官方解法  性能较差  但是要学会多个字段同时使用in
select d.Name Department, e.name Employee, e.Salary from department d join employee e
on d.id=e.departmentid
where (e.salary, e.departmentid) in
(select max(salary) m_salary, departmentid from employee group by departmentid);


方法二 三表连接
select d.Name Department, e.name Employee, e.Salary from department d join employee e
on d.id=e.departmentid
join (select max(salary) m_salary, departmentid from employee group by departmentid) s
on s.m_salary=e.salary and s.departmentid=e.departmentid;

185. 部门工资前三高的所有员工

自己二刷的代码，彻底掌握辅助列的方法
SELECT d.Name Department,t2.Name Employee, Salary
FROM
(select DepartmentId, Name, Salary, case when @dept=departmentid and @sal=Salary then @rk
									     when @dept=departmentid then @rk:=@rk+1
                                         else @rk:=1 end 'rank', @dept:=departmentid, @sal:= Salary
from employee, (select @dept:=0, @sal:=0, @rk:=0) t
order by departmentid, Salary desc) t2 join Department d
on t2.departmentid=d.Id
where t2.rank<=3 order by Department, Employee;

这个题目如果用串口函数去做就很简单 只是LeetCode中还不支持

窗口函数解法
select Name, Employee, Salary from
(select d.name Name, e.name Employee, e.Salary,  dense_rank() over(partition by e.departmentid order by e.salary desc) 排名 from
employee e join department d
on e.departmentid=d.id)
where 排名<4;

下面这个代码写的太漂亮了!!!

select d.Name Department,e.Name Employee, e.Salary from Employee e, Department d
where e.DepartmentId = d.Id
and (e.Salary , e.DepartmentId) in
(
select Salary,DepartmentId from(
SELECT 
    IF(@eTemp = a.DepartmentId
        @rowNum:=@rowNum + 1,
        @rowNum:=1) rowNum,
    @eTemp:=a.DepartmentId,
    Salary,
    DepartmentId
FROM
    (SELECT 
        Salary, DepartmentId
    FROM
        Employee e
    GROUP BY DepartmentId , Salary
    ORDER BY DepartmentId , Salary DESC) a,
    (SELECT @eTemp:=NULL, @rowNum:=0) b
) r where r.rowNum <=3
)
order by e.DepartmentId ,e.Salary desc;

我自己通过emp表写的一段代码 应该也不错
这可能是目前唯一一种可以实现根据两个字段排名的demo
通过这段代码理解 函数的执行顺序
select deptno, ename,sal,
if(@dep=deptno,null, @sa:=sal), 
(case
when @dep=deptno and @sa=sal then @ran:=@ran
when @dep=deptno and @sa:=sal then @ran:=@ran+1
when @dep:=deptno  then @ran:=1 end ) 排名
from emp, (select @dep:=0, @sa:=0, @ran:=0) t
order by deptno, sal desc;

最后看一下官方给的解释: 扩宽思路 但是性能不好


196. 删除重复的电子邮箱

这是一道简单级别的题目 但是自己想了好久都没做出来
题目要求对每个email只保留最小ID 那么其实我们很容易把最小的ID找出来… 然后not in

DELETE from Person 
Where Id not in (
    Select Id 
    From(
    Select MIN(Id) as id
    From Person 
    Group by Email
   ) t
)
其中表的别名t好像必须要加上,碰到很多这种情况了

197. 上升的温度

认识一下datediff函数
两个日期之间是可以直接相减的, 但是一旦数据不规整,代码就会报错.

方法一
select a.Id from  Weather as a join Weather as b 
on a.Temperature > b.Temperature and dateDiff(a.RecordDate,b.RecordDate) = 1 

方法二  超过99.9%
select
    Id
from
    (select w.*,
     @curd := w.RecordDate,
     @curt := w.Temperature,
     @isH := if(datediff(@curd,@pred) = 1 and @curt > @pret,1,0) as r,
     @pret := @curt,
     @pred := @curd
     from
        Weather w,
        (select 
            @curd := null,
            @pred := null,
            @curt := 0,
            @pret := 0,
            @isH := 0
        ) init
     order by w.RecordDate
    ) t

where
    t.r = 1;

理解一句很重要的话:
最后的order by 是影响其中的 r列的!! 他是根据其他列的计算结果得到的!
其实它有点像动态的!
而且这里的if表达式,是curd的本行和pred的上一行做比较!! 一定要理解这里!

自己写出了超过100%的代码!

select ID FROM
(select w.id,if(@tem<w.Temperature and datediff(w.RecordDate, @dat)=1,1,0) num,
@tem:=w.Temperature, @dat:=w.RecordDate
from Weather w, (select @tem:=0,@dat:=null ) t1
order by w.RecordDate) t2
where num=1;

262. 行程和用户


首先第一种方法是自己的做法 89% 自己用了一个子查询and内连接
第二种方法,是对第一种方法的改进 只使用了内连接 98%
通过第二种方法要学会: 在一个计算结果中使用多个函数.

方法一
select
    t.request_at Day, 
    (
        round(count(if(status != 'completed', status, null)) / count(status), 2)
    ) as 'Cancellation Rate'
from
    Users u inner join Trips t
on
    u.Users_id = t.Client_Id
and
    u.banned != 'Yes'
where
    t.Request_at >= '2013-10-01'
and
    t.Request_at <= '2013-10-03'
group by
    t.Request_at


方法二
select Day, round(cancell/number,2) 'Cancellation Rate' from
(select t.Request_at Day, count(*) number, sum(if(t.Status like "completed", 0, 1)) cancell
from trips t join users u
on t.Client_Id=u.Users_Id and u.Banned  like "NO"
where t.request_at>='2013-10-01' and t.request_at<='2013-10-03'
group by t.Request_at
 ) tab;

-- 上面的方法是不对的！应该是两个限制条件！(只是官网可以通过...)

SELECT T.request_at AS `Day`, 
	ROUND(SUM(IF(T.STATUS = 'completed',0,1))/COUNT(T.STATUS),2) AS `Cancellation Rate`
FROM Trips AS T
JOIN Users AS U1 ON (T.client_id = U1.users_id AND U1.banned ='No')
JOIN Users AS 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;