Combine Two Tables
# Write your MySQL query statement below
Select p.FirstName, p.LastName, a.City, a.State from Person p left join Address a on p.PersonId = a.PersonId
Consecutive Numbers
# Write your MySQL query statement below
select distinct num from (
select num, count(rank) as cnt from (
select num, @cur := @cur + if(@prev = num, 0, 1) as rank, @prev := num
from Logs s, (select @cur := 0) r, (select @prev := null) p
order by id asc
) t group by rank having cnt >= 3
) n;
个人觉得还是用下面这个简单的好
# Write your MySQL query statement below
select distinct l1.Num from Logs l1, Logs l2, Logs l3 where l1.Id + 1 = l2.Id and l1.Id + 2 = l3.Id and l1.Num = l2.Num and l1.Num = l3.Num;
Customers Who Never Order
# Write your MySQL query statement below
select c.Name from Customers c left join Orders o on c.Id = o.CustomerId where o.Id is null
# Write your MySQL query statement below
select c.Name from Customers c where c.Id not in (select CustomerId from Orders o)
# Write your MySQL query statement below
select c.Name from Customers c where not exists (select c.Id from Orders o where o.CustomerId = c.Id)
in和exists的区别参考http://www.cnblogs.com/seasons1987/archive/2013/07/03/3169356.html
可以这么理解，我们偏向于：
select * from 大表 where cc in (select cc from 小表); 因为in相当于内表（这里是小表）与外表的hash连接，因此内表应选择小表
select * from 小表 where exists (select cc from 大表 where cc = 小表.cc); 因为exists是先对外表（这里是小表）做loop，再在后面exists里面的语句（内表）进行查询，因此外表要选择小表
Delete Duplicate Emails
# Write your MySQL query statement below
delete p1 from Person p1 inner join Person p2 where p1.email = p2.email and p1.Id > p2.Id
# Write your MySQL query statement below
delete from p1 using Person p1 inner join Person p2 where p1.email = p2.email and p1.Id > p2.Id
# Write your MySQL query statement below
delete from Person where Id not in (select * from (select min(Id) from Person p group by Email) t)
注意下面这个语句会出现BUG
# Write your MySQL query statement below
delete from Person where Id not in (select min(Id) from Person p group by Email)
错误提示为：You can't specify target table 'Person' for update in FROM clause
这个错误为在MySQL中，禁止在FROM子句中指定被更新的目标表。也就是说用Min或者max的时候要在外面再套一层select，这个错误只有在MYSQL中有，在MSSQL和Oracal中没有（好奇怪。。）
Department Highest Salary
# Write your MySQL query statement below
select d.name as Department, t.Salary, e.Name as Employee from Employee e inner join (select DepartmentId, max(salary) as Salary from Employee group by DepartmentId) t using (DepartmentId, Salary) inner join Department d on d.Id = t.DepartmentId
SQL的顺序一般从from后面开始，再是select，最后是order by
下面这段代码更加容易理解
# Write your MySQL query statement below
select d.Name Department, e.Name Employee, s.Salary from (
select MAX(e.Salary) Salary, e.DepartmentId from Employee e, Department d where e.DepartmentId = d.Id group by e.DepartmentId
) s, Employee e, Department d where s.Salary = e.Salary and e.DepartmentId = d.Id and e.DepartmentId = s.DepartmentId;
Departmet Top Three Salaries
# Write your MySQL query statement below
select D.Name as Department, E.Name as Employee, E.Salary as Salary
from Employee E, Department D
where (select count(distinct(Salary)) from Employee where DepartmentId = E.DepartmentId and Salary > E.Salary) in (0, 1, 2)
and
E.DepartmentId = D.Id
order by E.DepartmentId, E.Salary DESC;
太难了。。
Duplicate Emails
# Write your MySQL query statement below
select distinct(p.Email) from Person p, Person q where p.Id != q.Id and p.Email = q.Email
# Write your MySQL query statement below
select Email from Person group by Email having count(*) > 1
Employees Earning More Than Their Managers
# Write your MySQL query statement below
select e1.Name from Employee e1, Employee e2 where e1.ManagerId = e2.Id and e1.Salary > e2.Salary
Rank Scores
# Write your MySQL query statement below
select Score, Rank from (
select Score, @cur := @cur + if(@prev = Score, 0, 1) as Rank, @prev := Score from
Scores s, (select @cur := 0) r, (select @prev := null) p order by Score desc) t
Rising Temperature
# Write your MySQL query statement below
select w1.Id from Weather w1 join Weather w2 where to_days(w1.Date) = to_days(w2.Date) + 1 and w1.Temperature > w2.Temperature
Second Highest Salary
# Write your MySQL query statement below
select max(Salary) from Employee where Salary < (select max(Salary) from Employee)