sql语句写的不多,希望提高一下自己的sql能力,按照Easy->medium->hard的顺序练习
题目链接如下: https://leetcode.com/problemset/database/
目录
181. Employees Earning More Than Their Managers
183. Customers Who Never Order
596. Classes More Than 5 Students
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