leetcode刷题mysql简单
组合俩个表
题目
表1: Person
列名 | 类型 |
---|---|
PersonId | int |
FirstName | varchar |
LastName | varchar |
PersonId 是上表主键
表2: Address
列名 | 类型 |
---|---|
AddressId | int |
PersonId | int |
City | varchar |
State | varchar |
AddressId 是上表主键
编写一个 SQL 查询,满足条件:无论 person 是否有地址信息,都需要基于上述两表提供 person 的以下信息:
链接:https://leetcode-cn.com/problems/combine-two-tables
解答
利用左查询连接俩表
select p.FirstName,p.LastName,a.City,a.State from Person as p left join Address as a on p.PersonId=a.PersonId
第二高的薪水
题目
编写一个 SQL 查询,获取 Employee 表中第二高的薪水(Salary) 。
Id | Salary |
---|---|
1 | 100 |
2 | 200 |
3 | 300 |
例如上述 Employee 表,SQL查询应该返回 200 作为第二高的薪水。如果不存在第二高的薪水,那么查询应返回 null。
SecondHighestSalary |
---|
200 |
链接:https://leetcode-cn.com/problems/second-highest-salary
解答
连接查询限定条件
# Write your MySQL query statement below
SELECT MAX(Salary) as SecondHighestSalary from Employee where Salary !=(SELECT max(Salary) FROM Employee )
超过经理收入的员工
题目
Employee 表包含所有员工,他们的经理也属于员工。每个员工都有一个 Id,此外还有一列对应员工的经理的 Id。
Id | Name | Salary | ManagerId |
---|---|---|---|
1 | Joe | 70000 | 3 |
2 | Henry | 80000 | 4 |
3 | Sam | 60000 | NULL |
4 | Max | 90000 | NULL |
给定 Employee 表,编写一个 SQL 查询,该查询可以获取收入超过他们经理的员工的姓名。在上面的表格中,Joe 是唯一一个收入超过他的经理的员工。
Employee |
---|
Joe |
链接:https://leetcode-cn.com/problems/employees-earning-more-than-their-managers
解答
通过左查询限定条件
select y.Name as Employee from Employee as y left join Employee as j on y.ManagerId =j.id where y.Salary>j.Salary ;
查找重复的邮箱
简介
编写一个 SQL 查询,查找 Person 表中所有重复的电子邮箱。
示例:
Id | |
---|---|
1 | a@b.com |
2 | c@d.com |
3 | a@b.com |
根据以上输入,你的查询应返回以下结果:
a@b.com |
说明:所有电子邮箱都是小写字母。
链接:https://leetcode-cn.com/problems/duplicate-emails
解答
根据分组查询对查询记录进行去重留下单条的email结果,之后用having count(*)统计数量
select Email from Person GROUP BY Email having count(*)>1
从不订购的客户
题目
某网站包含两个表,Customers 表和 Orders 表。编写一个 SQL 查询,找出所有从不订购任何东西的客户。
Customers 表:
Id | Name |
---|---|
1 | Joe |
2 | Henry |
3 | Sam |
4 | Max |
Orders 表:
Id | CustomerId |
---|---|
1 | 3 |
2 | 1 |
例如给定上述表格,你的查询应返回:
Customers |
---|
Henry |
Max |
链接:https://leetcode-cn.com/problems/customers-who-never-order
解答
查询出所有Orders表CustomerId,用not in在其中进行排除
select Name as Customers from Customers where id not in (select CustomerId from Orders);
删除重复的电子邮箱
题目
编写一个 SQL 查询,来删除 Person 表中所有重复的电子邮箱,重复的邮箱里只保留 Id 最小 的那个。
Id | |
---|---|
1 | john@example.com |
2 | bob@example.com |
3 | john@example.com |
Id 是这个表的主键。
例如,在运行你的查询语句之后,上面的 Person 表应返回以下几行:
Id | |
---|---|
1 | john@example.com |
2 | bob@example.com |
提示:
执行 SQL 之后,输出是整个 Person 表。
使用 delete 语句。
链接:https://leetcode-cn.com/problems/delete-duplicate-emails
解答
先使用以下代码,将此表与自身在电子邮箱列中连接起来
SELECT p1.*
FROM Person p1,
Person p2
WHERE
p1.Email = p2.Email
;
然后我们需要找到其记录中具有相同电子邮件地址的更大id,所以给这样where子句添加一个新的条件
p1.Email = p2.Email AND p1.Id > p2.Id逻辑是在确保邮箱相等的情况下找出比较大的id
SELECT p1.*
FROM Person p1,
Person p2
WHERE
p1.Email = p2.Email AND p1.Id > p2.Id
;
因为我们已经得到了要删除的记录,所以我们最终可以将该语句更改为 DELETE
。
delete p1 from Person as p1 ,Person as p2 where p1.Email=p2.Email and p1.Id>p2.Id
这里需要注意delete的语法,如果不在delete和 from加上表名,sql就不知道去删除哪个数据了
上升的温度
题目
给定一个 Weather 表,编写一个 SQL 查询,来查找与之前(昨天的)日期相比温度更高的所有日期的 Id。
Id(INT) | RecordDate(DATE) | Temperature(INT) |
---|---|---|
1 | 2015-01-01 | 10 |
2 | 2015-01-02 | 25 |
3 | 2015-01-03 | 20 |
4 | 2015-01-04 | 30 |
例如,根据上述给定的 Weather 表格,返回如下 Id:
Id |
---|
2 |
4 |
链接:https://leetcode-cn.com/problems/rising-temperature=
解答
这里用到了比较重要的函数 datediff
使用如下 SELECT 语句:
SELECT DATEDIFF('2008-12-30','2008-12-29') AS DiffDate
结果:
DiffDate |
---|
1 |
使用如下 SELECT 语句:
SELECT DATEDIFF('2008-12-29','2008-12-30') AS DiffDate
结果:
DiffDate |
---|
-1 |
首先查询比过去温度搞的数据
select w1.id from Weather as w1,Weather as w2
where w1.Temperature>w2.Temperature
之后用datediff将条件限定查询的结果只能为昨天
select w1.id from Weather as w1,Weather as w2
where w1.Temperature>w2.Temperature
and datediff(w1.RecordDate,w2.RecordDate)=1
大的国家
题目
这里有张 World 表
name | continent | area | population | gdp |
---|---|---|---|---|
Afghanistan | Asia | 652230 | 25500100 | 20343000 |
Albania | Europe | 28748 | 2831741 | 12960000 |
Algeria | Africa | 2381741 | 37100000 | 188681000 |
Andorra | Europe | 468 | 78115 | 3712000 |
Angola | Africa | 1246700 | 20609294 | 100990000 |
如果一个国家的面积超过300万平方公里,或者人口超过2500万,那么这个国家就是大国家。
编写一个SQL查询,输出表中所有大国家的名称、人口和面积。
例如,根据上表,我们应该输出:
name | population | area |
---|---|---|
Afghanistan | 25500100 | 652230 |
Algeria | 37100000 | 2381741 |
链接:https://leetcode-cn.com/problems/big-countries
解答
这个只要or连接俩个查询条件就好了
select name,population,area from World where population>25000000 or area>3000000
超过五名学生的课
题目
有一个courses 表 ,有: student (学生) 和 class (课程)。
请列出所有超过或等于5名学生的课。
例如,表:
student | class |
---|---|
A | Math |
B | English |
C | Math |
D | Biology |
E | Math |
F | Computer |
G | Math |
H | Math |
I | Math |
应该输出:
class |
---|
Math |
Note:
学生在每个课中不应被重复计算。
链接:https://leetcode-cn.com/problems/classes-more-than-5-students
解答
用分组查询将俩个记录分组之后用having过滤不超过五个的,此外不要忘记加上distinct去重哦
select class from courses group by class having count(distinct student)>=5;
行程和用户
题目
某城市开了一家新的电影院,吸引了很多人过来看电影。该电影院特别注意用户体验,专门有个 LED显示板做电影推荐,上面公布着影评和相关电影描述。
作为该电影院的信息部主管,您需要编写一个 SQL查询,找出所有影片描述为非 boring (不无聊) 的并且 id 为奇数 的影片,结果请按等级 rating 排列。
例如,下表 cinema:
id | movie | description | rating |
---|---|---|---|
1 | War | great 3D | 8.9 |
2 | Science | fiction | 8.5 |
3 | irish | boring | 6.2 |
4 | Ice song | Fantacy | 8.6 |
5 | House card | Interesting | 9.1 |
对于上面的例子,则正确的输出是为:
id | movie | description | rating |
---|---|---|---|
5 | House card | Interesting | 9.1 |
1 | War | great 3D | 8.9 |
链接:https://leetcode-cn.com/problems/not-boring-movies
解答
查询出id为奇数(id%2)为1的就是奇数,之后排除boring的电影,最后根据rating降序排序
select * from cinema where (id%2)=1 and description !='boring' order by rating desc
交换工资
题目
给定一个 salary 表,如下所示,有 m = 男性 和 f = 女性 的值。交换所有的 f 和 m 值(例如,将所有 f 值更改为 m,反之亦然)。要求只使用一个更新(Update)语句,并且没有中间的临时表。
注意,您必只能写一个 Update 语句,请不要编写任何 Select 语句。
例如:
id | name | sex | salary |
---|---|---|---|
1 | A | m | 2500 |
2 | B | f | 1500 |
3 | C | m | 5500 |
4 | D | f | 500 |
运行你所编写的更新语句之后,将会得到以下表:
id | name | sex | salary |
---|---|---|---|
1 | A | f | 2500 |
2 | B | m | 1500 |
3 | C | f | 5500 |
4 | D | m | 500 |
来源:力扣(LeetCode)
链接:https://leetcode-cn.com/problems/swap-salary
著作权归领扣网络所有。商业转载请联系官方授权,非商业转载请注明出处。
解答
在mysql中if()函数的用法类似于java中的三目表达式,其用处也比较多,具体语法如下:
IF(expr1,expr2,expr3),如果expr1的值为true,则返回expr2的值,如果expr1的值为false,
根据原来的值判断新值
# Write your MySQL query statement below
update salary set sex=if(sex='m','f','m')
重新格式化部门表
题目
编写一个 SQL 查询来重新格式化表,使得新的表中有一个部门 id 列和一些对应 每个月 的收入(revenue)列。
查询结果格式如下面的示例所示:
Department 表: | ||
---|---|---|
id | revenue | month |
1 | 8000 | Jan |
2 | 9000 | Jan |
3 | 10000 | Feb |
1 | 7000 | Feb |
1 | 6000 | Mar |
查询得到的结果表: | |||||
---|---|---|---|---|---|
id | Jan_Revenue | Feb_Revenue | Mar_Revenue | ... | Dec_Revenue |
1 | 8000 | 7000 | 6000 | ... | null |
2 | 9000 | null | null | ... | null |
3 | null | 10000 | null | ... | null |
注意,结果表有 13 列 (1个部门 id 列 + 12个月份的收入列)。
链接:https://leetcode-cn.com/problems/reformat-department-table
解答
首先,根据题意,需要将revenue列根据month拆成行,结果如下表,有两种方式,CASE WHEN或者IF语句。
SELECT id,
CASE `month` WHEN 'Jan' THEN revenue END Jan_Revenue,
CASE `month` WHEN 'Feb' THEN revenue END Feb_Revenue,
CASE `month` WHEN 'Mar' THEN revenue END Mar_Revenue,
CASE `month` WHEN 'Apr' THEN revenue END Apr_Revenue,
CASE `month` WHEN 'May' THEN revenue END May_Revenue,
CASE `month` WHEN 'Jun' THEN revenue END Jun_Revenue,
CASE `month` WHEN 'Jul' THEN revenue END Jul_Revenue,
CASE `month` WHEN 'Aug' THEN revenue END Aug_Revenue,
CASE `month` WHEN 'Sep' THEN revenue END Sep_Revenue,
CASE `month` WHEN 'Oct' THEN revenue END Oct_Revenue,
CASE `month` WHEN 'Nov' THEN revenue END Nov_Revenue,
CASE `month` WHEN 'Dec' THEN revenue END Dec_Revenue
FROM Department;
之后用分组查询对id进行合并,用max函数求最大值
SELECT id,
SUM(CASE `month` WHEN 'Jan' THEN revenue END) Jan_Revenue,
SUM(CASE `month` WHEN 'Feb' THEN revenue END) Feb_Revenue,
SUM(CASE `month` WHEN 'Mar' THEN revenue END) Mar_Revenue,
SUM(CASE `month` WHEN 'Apr' THEN revenue END) Apr_Revenue,
SUM(CASE `month` WHEN 'May' THEN revenue END) May_Revenue,
SUM(CASE `month` WHEN 'Jun' THEN revenue END) Jun_Revenue,
SUM(CASE `month` WHEN 'Jul' THEN revenue END) Jul_Revenue,
SUM(CASE `month` WHEN 'Aug' THEN revenue END) Aug_Revenue,
SUM(CASE `month` WHEN 'Sep' THEN revenue END) Sep_Revenue,
SUM(CASE `month` WHEN 'Oct' THEN revenue END) Oct_Revenue,
SUM(CASE `month` WHEN 'Nov' THEN revenue END) Nov_Revenue,
SUM(CASE `month` WHEN 'Dec' THEN revenue END) Dec_Revenue
FROM Department
GROUP BY id;