SQL例题汇总

第1题：上升的温度——datediff

select w1.Id
from Weather w1,Weather w2
where datediff(w1.RecordDate,w2.RecordDate)=1 and w1.Temperature>w2.Temperature;

第2题：组合两个表——左连接

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

第3题：第二高的薪水——limit…offset…

select
    IFNULL(
        (select distinct Salary 
        from Employee
        order by Salary desc
        limit 1 offset 1),NULL)
as SecondHighestSalary;

第4题：重复的电子邮箱——having分组后筛选

SELECT Email
FROM Person
group by Email
having count(*)>1;

第5题：超过5名学生的课——having，distinct

select class
from courses
group by class
having count(distinct student)>=5;
#有可能或重修，所以不能对每个分组的class数量进行统计，而应该统计学生数量

第5题：从不订购的客户

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