SQL|Leetode刷题笔记

175.组合两个表
总结：连接两个表用 (表a) join (表b) on 匹配字段

select p.FirstName, p.LastName, a.City, a.State 
from Person as p left join Address as a
on p.PersonId = a.PersonId

176.第二高的薪水
总结：offset x 表示省略前x个值；if null(A ,null)表示如果第一项为空，返回null

select ifnull(
    (select distinct Salary from Employee #一定要加distinct，
    order by Salary desc #先执行select,在执行Order by
    limit 1 offset 1),null) as Second HighestSalary

177.第N高的薪水

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN 
    set N = N - 1;
    IF N < 0 THEN
    RETURN NULL;
    ELSE
    RETURN (
        SELECT IFNULL (
            (
                SELECT
                DISTINCT Salary
                FROM Employee 
                ORDER BY Salary DESC
                LIMIT N offset 1
            ),NULL
        )  AS getNthHighestSalary
    );
    END IF;
END

178.分数排名（200421）
总结：dense_rank() over (order by …) #重复部分不占名次
rank() over (order by …) #重复部分占名次
row_number() over (order by …) #重复部分都会计名次

select score,
dense_rank() over(order by score desc) as rank
from Scores

180.连续出现的数字（200421）
总结：将表自联，要求id相差1，数字相等

select distinct a.Num as ConsecutiveNums #要用distinct排除相同数字
from Logs as a, Logs as b, Logs as c
where b.id-a.id=1
and c.id-b.id=1
and b.num=a.num
and c.num=b.num

181.超过经理收入的员工
总结：1.连接两个表时，可以用join on +查找字段；也可以用 表a，表b where 限制查询条件（效率更高）
2.对应字段时将两个表连接起来

select a.Name as Employee
from Employee as a, Employee as b #自动进行笛卡尔连接-表a每行都和表b所有行相连接
where a.ManagerId=b.Id
and a.Salary>b.Salary 

182.查找重复的电子邮箱

select Email 
from Person 
group by Email
having count(Email)>1

183.从不订购的客户
总结：两个select 嵌套，选出第二张表中没有的名单

select c.Name as Customers
 from Customers as c
 where c.Id not in
 (select CustomerId from orders)

或者按下面的方式：将表进行做链接，筛选出null的行

select c.Name as Customers
from Customers as c left join Orders as o
on c.Id=o.Id
where o.CustomerId is null

以上为公式应用和连接后的结果


184.部门最高的工资
总结：首先找出Employee中工资最高的行，再和Deaprtment匹配

select d.Name as Department, e.Name as Employee, e.Salary
from Employee as e, Department as d
 where e.DeaprtmentId=d.Id
 and (e.DepartmentId, e.salary) in 
 (select DepartmentId, max(Salary)
 from Employee
 group by Employee.DepartmentId)

196. 删除重复的电子邮箱
总结：1.distinct 只能返回distinct指定的字段，例如，通过distinct name得不到之后的备注列；
2. distinct必须放在开头，当用distinct筛选两列，如distinct name sex, 只要有一列不同就返回。

3. 使用group by，min 找出符合条件的id，再用not in删除

delete from Person 
where id not in
(select temp.id 
from ((select min(id) as id
from Person
group by email) as temp)) #as 表明之前的部分一定要加括号/必须用到临时表

197.上升的温度
总结：datediff(startdate,enddate)

select a.Id
from Weather as a join Weather as b
on datediff(a.RecordDate, b.RecordDate)=1 #教程上必须要加第一个参数，次数加上就报错
where a.Temperature>b.Temperature

262.行程和用户（200422）
总结：找不出哪里有错

select t.Request_at as Day, round(sum(case when t.Status != 'completed' then 1 end)/count(t.status),2) 
as Cancellation_rate
from trips as t 
left join Users as a on t.Client_Id=a.Users_Id
left join Users as b on t.Driver_Id=b.Users_Id
where a.Banned='No' and
b.Banned= 'No'
group by t.Request_at

595.大的国家

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

596.超过5名学生选的课
总结：having相当于where，也是限制条件，出现聚合函数 count、avg等只能用having，并且放在group by 后面

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

620.有趣的电影
总结：取余数用%，不是答案中的mod

select *
from cinema 
where id % 2 =1
and description != 'boring'
order by rating desc

626.换座位（200421）
总结：1. 思路：将奇数id-1.偶数id+1
2. case when … then…
else…

select (case 
when id%2 = 1 and id = (select count(*) from seat) then id #奇数id，并且是最后一位，id保持不变
when id%2 = 1 then id+1 #不满足条件一时，才判断此条件。奇数id，并且不是最后一位
else id-1
end) as id, student #select id,student
from seat
order by id asc

627.交换工资
总结：1.update ‘表a’ set 替换后的值
2.case函数：case ’列名‘ when…then

UPDATE salary
SET
    sex = CASE sex
        WHEN 'm' THEN 'f'
        ELSE 'm'
    END;