Leetcode数据库习题之Mysql实现

1、简单难度

175、组合两个表

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

这里需要注意，不能使用内连接，内连接只有满足条件的数据才会出现在结果中。
如果people没有地址则没有满足的结果，与题意不符。

176、第二高的薪水

select distinct Salary as SecondHighestSalary 
    from Employee 
    order by Salary DESC limit 1, 1

distinct:去除查询结果中的重复记录。

181、超过经理收入的员工

select e1.Name as Employee 
    from Employee e1, Employee e2 
    where e1.ManagerId = e2.Id and e1.Salary > e2.Salary

182、查找重复的电子邮箱

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

183、从不订购的客户

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

196、删除重复的电子邮箱

delete p2.* 
    from Person p1, Person p2 
    where p1.Email = p2.Email and p2.Id > p1.Id

197、上升的温度

select w2.Id 
    from Weather w1, Weather w2 
    where w2.Temperature > w1.Temperature and DATE_SUB(w2.RecordDate,INTERVAL 1 DAY) = w1.RecordDate

595、大的国家

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

596、超过5名学生的课

select class 
    from (select distinct * from courses) as a 
    group by a.class 
    having count(class) >= 5

这个题比较坑，一个学生可以上多次相同的课，数据库有多条相同的记录。

620、有趣的电影

select id, movie, description, rating 
    from cinema 
    where description <> 'boring' and MOD(ID, 2) = 1 
    order by rating DESC

627、交换工资

update salary set sex = if(sex = 'm', 'f','m');

Mysql的 IF(Condition, A, B) 函数，条件为true，返回A；条件为false，返回B。