leetcode--mysql

where
and, or, between
like
in
not

1
2
3

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

627. Swap Salary

620. Not Boring Movies

182. Duplicate Emails

1
2
3

Select distinct a.Email
from Person a, Person b
where a.Email = b.Email and a.Id != b.Id

175. Combine Two Tables

1
2
3

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

181. Employees Earning More Than Their Managers

1
2
3

select a.Name
from Employee a, Employee b
where a.Salary > b.Salary and a.ManagerId = b.Id

183. Customers Who Never Order

1
2
3

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

596. Classes More Than 5 Students
197. Rising Temperature
196. Delete Duplicate Emails
176. Second Highest Salary

select ifnull(exp1, exp2)
exp1 is not null return corresponding field, else return exp2

distinct
返回唯一值

desc, asce
只对跟着的字段有效

select ifnull(
(select Salary from Employee order by Salary desc limit 1,1),
null)
as SecondHighestSalary;

key :
注意返回的字段是否命名
当有重复数据

medium
626. Exchange Seats
178. Rank Scores

1
2
3

select Score, (select count(distinct a.Score) from Scores a where a.Score >= b.Score) as Rank
from Scores b
order by Score desc

180. Consecutive Numbers

1
2
3

select distinct L2.Num as ConsecutiveNums
from Logs L1, Logs L2, Logs L3
where L1.Id = L2.Id-1 and L3.Id = L2.Id+1 and L1.Num = L2.Num and L3.Num = L2.Num

184. Department Highest Salary
177. Nth Highest Salary

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    declare m int;
    set m = N-1;
  RETURN (
      select distinct Salary from Employee order by Salary desc limit m, 1     
  );
END

hard
601. Human Traffic of Stadium
185. Department Top Three Salaries
262. Trips and Users