LeetCode sql语句 学习专题【Easy】

sql语句写的不多，希望提高一下自己的sql能力，按照Easy->medium->hard的顺序练习

题目链接如下： https://leetcode.com/problemset/database/

---

目录

595. Big Countries

627. Swap Salary

620. Not Boring Movies

182. Duplicate Emails

175. Combine Two Tables

181. Employees Earning More Than Their Managers

183. Customers Who Never Order

596. Classes More Than 5 Students

197. Rising Temperature

196. Delete Duplicate Emails

176. Second Highest Salary

---

595. Big Countries

找出一张表中满足条件的记录，考察or的用法

# Write your MySQL query statement below
select name,population,area from World where area>=3000000 or population>=25000000;

---

627. Swap Salary

将表中的字段按照要求转换为别的，需要用到 sql里的判断  CASE  X WHEN .. THEN .. ELSE ...END;

# Write your MySQL query statement below
update salary 
    set sex=CASE sex WHEN 'f' THEN 'm'
    ELSE 'f' END;

 

---

620. Not Boring Movies

找出满足条件，且按照某字段排序的记录

# Write your MySQL query statement below
select * from (
    select * from cinema where description !='boring' and id %2=1
) A order by rating DESC

---

182. Duplicate Emails

表中有重复的字段，按照要求统计出来

# Write your MySQL query statement below
select Email from(
    select Email,count(Email) as num from Person
    group by Email
)A where num>1

---

 

175. Combine Two Tables

根据某个字段连接两张表，使用left join

# 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 ;

 

---

181. Employees Earning More Than Their Managers

根据要求找到满足的字段，在同一张表中有以来关系，可以采用 tableName a的形式指代一张表

# Write your MySQL query statement below
select a.Name as Employee from
    Employee  as a,Employee as b
where
    a.ManagerId = b.Id and
    a.Salary > b.Salary

 

---

 

183. Customers Who Never Order

两张表，查询不在另外一张表里的内容，需要使用not in

# Write your MySQL query statement below
select Name as Customers from
    Customers as a
where
    a.id not in(
        select CustomerId from Orders
    )

 

---

 

596. Classes More Than 5 Students

找出人数超过5个人的课程，注意可能有重复字段 需要用DISTINCT

# Write your MySQL query statement below
select class from (
    select class,count(DISTINCT student) as num from courses group by class
) A where num>=5

---

 

197. Rising Temperature

需要找出比前一天温度高的记录，需要使用函数datadiff()

# Write your MySQL query statement below
select b.Id from Weather as a,Weather as b
where datediff(b.RecordDate,a.RecordDate)=1 and a.Temperature < b.Temperature

SELECT
    weather.id AS 'Id'
FROM
    weather
        JOIN
    weather w ON DATEDIFF(weather.date, w.date) = 1
        AND weather.Temperature > w.Temperature
;

---

 

196. Delete Duplicate Emails

删除重复的记录保留主键最小的那个

# Write your MySQL query statement below
delete p1 from Person p1,Person p2
where p1.Email = p2.Email and p1.Id > p2.Id

---

 

176. Second Highest Salary

查询第二高的价格，注意 1价格可能重复 2.可能没有

# Write your MySQL query statement below
select (
    select distinct Salary from Employee order by Salary DESC limit 1,1    
) as SecondHighestSalary