力扣sql练习题(所有sql以mysql形式执行)
表一:Person
+-------------+---------+
| 列名 | 类型 |
+-------------+---------+
| PersonId | int |
| FirstName | varchar |
| LastName | varchar |
+-------------+---------+
PersonId 是上表主键
表二:Address
+-------------+---------+
| 列名 | 类型 |
+-------------+---------+
| AddressId | int |
| PersonId | int |
| City | varchar |
| State | varchar |
+-------------+---------+
AddressId 是上表主键
编写一个 SQL 查询,满足条件:无论 person 是否有地址信息,都需要基于上述两表提供 person 的以下信息:
FirstName, LastName, City, State
答案:
select p.FirstName, p.LastName, a.City, a.State
from person p
left join address a
on p.PersonId = a.PersonId
解析:left在左边 表示前一个表是一个基准表 基准表的所有字段无论是否记录为null都要显示在联合表中 right同理 另外left join其实是left outer join 属于外连接表之间有“等级”
表:Employee
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| name | varchar |
| salary | int |
| managerId | int |
+-------------+---------+
Id是该表的主键。
该表的每一行都表示雇员的ID、姓名、工资和经理的ID。
编写一个SQL查询来查找收入比经理高的员工。
以 任意顺序 返回结果表。
示例:输入:
Employee 表:
+----+-------+--------+-----------+
| id | name | salary | managerId |
+----+-------+--------+-----------+
| 1 | Joe | 70000 | 3 |
| 2 | Henry | 80000 | 4 |
| 3 | Sam | 60000 | Null |
| 4 | Max | 90000 | Null |
+----+-------+--------+-----------+
输出:
+----------+
| Employee |
+----------+
| Joe |
+----------+
解释: Joe 是唯一挣得比经理多的雇员。
答案:
select name as Employee
from employee e
where salary > (select salary from employee where id=e.managerID);
解析:e.mangerID是调取外层employee的managerID
编写一个 SQL 查询,查找 Person 表中所有重复的电子邮箱。
示例:
+----+---------+
| Id | Email |
+----+---------+
| 1 | a@b.com |
| 2 | c@d.com |
| 3 | a@b.com |
+----+---------+
根据以上输入,你的查询应返回以下结果:
+---------+
| Email |
+---------+
| a@b.com |
+---------+
说明:所有电子邮箱都是小写字母。
答案:
第一种:
select email as Email from person group by email having count(email)>1
第二种:
select email as Email from (select count(1) as t,email from person group by email)r where r.t>1;
第三种:
select distinct(p1.Email) from Person p1
join Person p2 on p1.Email = p2.Email AND p1.Id!=p2.Id
Employee 表:
+-------------+------+
| Column Name | Type |
+-------------+------+
| id | int |
| salary | int |
+-------------+------+
id 是这个表的主键。
表的每一行包含员工的工资信息。
编写一个 SQL 查询,获取并返回 Employee 表中第二高的薪水 。如果不存在第二高的薪水,查询应该返回 null 。
查询结果如下例所示。
示例 1:
输入:
Employee 表:
+----+--------+
| id | salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
输出:
+---------------------+
| SecondHighestSalary |
+---------------------+
| 200 |
+---------------------+
答案:
select ifNull((
select distinct Salary
from Employee
order by Salary desc limit 1,1),null) as SecondHighestSalary
解析:要想获取第二高,需要排序,使用 order by(默认是升序 asc,即从小到大),若想降序则使用关键字 desc
去重,如果有多个相同的数据,使用关键字 distinct 去重
判断临界输出,如果不存在第二高的薪水,查询应返回 null,使用 ifNull(查询,null)方法
起别名,使用关键字 as ...
因为去了重,又按顺序排序,使用 limit()方法,查询第二大的数据,即第二高的薪水,即 limit(1,1) (因为默认从0开始,所以第一个1是查询第二大的数,第二个1是表示往后显示多少条数据,这里只需要一条)
表: Employee
+-------------+------+
| Column Name | Type |
+-------------+------+
| id | int |
| salary | int |
+-------------+------+
Id是该表的主键列。
该表的每一行都包含有关员工工资的信息。
编写一个SQL查询来报告 Employee 表中第 n 高的工资。如果没有第 n 个最高工资,查询应该报告为 null 。
查询结果格式如下所示。
示例 1:
输入:
Employee table:
+----+--------+
| id | salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
n = 2
输出:
+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200 |
+------------------------+
答案:CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
set N=N - 1;
RETURN (
select ifNull((select distinct salary
from Employee order by
salary desc limit N,1),null) as getNthHighestSalary
);
END
解析:如上题,但是要提前创建一个set N=N+1;因为limit中直接写N+1会报错
表: Scores
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| score | decimal |
+-------------+---------+
Id是该表的主键。
该表的每一行都包含了一场比赛的分数。Score是一个有两位小数点的浮点值。
编写 SQL 查询对分数进行排序。排名按以下规则计算:
分数应按从高到低排列。
如果两个分数相等,那么两个分数的排名应该相同。
在排名相同的分数后,排名数应该是下一个连续的整数。换句话说,排名之间不应该有空缺的数字。
按 score 降序返回结果表
示例 1:
输入:
Scores 表:
+----+-------+
| id | score |
+----+-------+
| 1 | 3.50 |
| 2 | 3.65 |
| 3 | 4.00 |
| 4 | 3.85 |
| 5 | 4.00 |
| 6 | 3.65 |
+----+-------+
输出:
+-------+------+
| score | rank |
+-------+------+
| 4.00 | 1 |
| 4.00 | 1 |
| 3.85 | 2 |
| 3.65 | 3 |
| 3.65 | 3 |
| 3.50 | 4 |
+-------+------+
答案:
select s1.score,count(distinct s2.score) as "rank"
from scores as s1,scores as s2
where s1.score<=s2.score
group by s1.id
order by s1.score desc;
思路:
1.从两张相同的表scores分别命名为s1,s2。
2.s1中的score与s2中的score比较大小。意思是在输出s1.score的前提下,有多少个s2.score大于等于它。
比如当s1.salary=3.65的时候,s2.salary中[4.00,4.00,3.85,3.65,3.65]有5个成绩大于等于他,但是利用count(distinct s2.score)去重可得s1.salary3.65的rank为3
3.group by s1.id 不然的话只会有一条数据
4.最后根据s1.score排序desc
5.为什么rank加个单引号就可以运行呢?rank是MySQL版本8.0.2中定义的MySQL保留字。因此,不能将rank用作列名。你需要使用反勾号。相当于将rank进行转义
表:Logs
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| num | varchar |
+-------------+---------+
id 是这个表的主键。
编写一个 SQL 查询,查找所有至少连续出现三次的数字。
返回的结果表中的数据可以按 任意顺序 排列。
示例 1:
输入:
Logs 表:
+----+-----+
| Id | Num |
+----+-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
+----+-----+
输出:
Result 表:
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1 |
+-----------------+
解释:1 是唯一连续出现至少三次的数字。
答案:
select distinct Num as ConsecutiveNums
from (
select Num,
case
when @prev = Num then @count := @count + 1
when (@prev := Num) is not null then @count := 1
end as CNT
from Logs, (select @prev := null,@count := null) as t
) as temp
where temp.CNT >= 3
与自关联或自连接相比,这种方法的效率更高,不受Logs表中的Id是否连续的限制,而且可以任意设定某个值连续出现的次数。
1)逻辑:构建两个变量@prev 和@count ,前者用于与Num做比较判断,后者用于@prev和Num相等时的条件计数;
2)(select @prev := null,@count := null) as t 这句的作用是初始化两个变量,并将初始化后的变量放到一张临时表t中,:=符号在MySQL中是赋值的意思;
3)when @prev = Num then @count := @count + 1和when (@prev := Num) is not null then @count := 1 这两个语句不能交换顺序,赋值语句永远非NULL,所以一旦执行顺序来到了第二个when,@count 是一定会被赋值为1的,后者放到前面的话就达不到计数的目的;
4)(@prev := Num) is not null这部分去掉后面加的判断,SQL也能正常执行,上面SQL中case when的这种用法,when后是判断条件,赋值后又加判断,我原以为这样会好理解点;
5)case when本质是一个函数,有值时就返回内部处理得到的值,无值就返回NULL,针对每一个Num,上面SQL中的case when 都会有一个计数,并把这个计数返回给CNT。
PS:MySQL8.0以后的版本开始支持窗口函数,使用窗口函数也能很好的解决此类问题。关于MySQL的窗口函数,可以参考译文:https://blog.csdn.net/qq_41080850/article/details/86416106
表: Weather
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| recordDate | date |
| temperature | int |
+---------------+---------+
id 是这个表的主键
该表包含特定日期的温度信息
编写一个 SQL 查询,来查找与之前(昨天的)日期相比温度更高的所有日期的 id 。
返回结果 不要求顺序 。
查询结果格式如下例。
示例 1:
输入:
Weather 表:
+----+------------+-------------+
| id | recordDate | Temperature |
+----+------------+-------------+
| 1 | 2015-01-01 | 10 |
| 2 | 2015-01-02 | 25 |
| 3 | 2015-01-03 | 20 |
| 4 | 2015-01-04 | 30 |
+----+------------+-------------+
输出:
+----+
| id |
+----+
| 2 |
| 4 |
+----+
解释:
2015-01-02 的温度比前一天高(10 -> 25)
2015-01-04 的温度比前一天高(20 -> 30)
答案:
select a.id from weather as a
join weather as b on a.Temperature > b.Temperature
and dateDiff(a.recordDate,b.recordDate)=1;
解析:
join表示表连接,dateDiff()方法代表数据中的时间差
表: Person
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| email | varchar |
+-------------+---------+
id是该表的主键列。
该表的每一行包含一封电子邮件。电子邮件将不包含大写字母。
编写一个 SQL 删除语句来 删除 所有重复的电子邮件,只保留一个id最小的唯一电子邮件。
以 任意顺序 返回结果表。 (注意: 仅需要写删除语句,将自动对剩余结果进行查询)
查询结果格式如下所示。
示例 1:
输入:
Person 表:
+----+------------------+
| id | email |
+----+------------------+
| 1 | john@example.com |
| 2 | bob@example.com |
| 3 | john@example.com |
+----+------------------+
输出:
+----+------------------+
| id | email |
+----+------------------+
| 1 | john@example.com |
| 2 | bob@example.com |
+----+------------------+
解释: john@example.com重复两次。我们保留最小的Id = 1。
答案:
方法1:delete a from person a,person b where a.id=b.id and a.email=b.email;
*注意:在删除过程中,自连接起的别名要放到delete和form中间,以作为自连接第一张表的别名
方法2:
DELETE from Person
where id not in (
SELECT id
from(
SELECT min(id) as id
from Person
group by email
)t
)
解析:不能在一条语句里对同一张表又查又删/改,但可以加个中间表T T 和 原表也存在id字段,这个where的id mysql无法分辨是子查询的还是person表的,需要借助别名进行准确的定义,所以(select min(id) from Person Group by email) t
这张表,表名为t,表内唯一字段的字段名是 'min(id)',用 select id 取查是查不到的 所以要起一个和原来表格字段相同的别名
*注:where id not in (select top 6 id from 表名) 就是查找id不在前6条数据里的数据 select top 3 字段名 from 表名 是查找前3条数据, 合起来的意思就是就是查id不在前6条数据里的前三条数据,也就是查第7-9条数据
Stocks 表:
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| stock_name | varchar |
| operation | enum |
| operation_day | int |
| price | int |
+---------------+---------+
(stock_name, day) 是这张表的主键
operation 列使用的是一种枚举类型,包括:('Sell','Buy')
此表的每一行代表了名为 stock_name 的某支股票在 operation_day 这一天的操作价格。
保证股票的每次'Sell'操作前,都有相应的'Buy'操作。
编写一个SQL查询来报告每支股票的资本损益。
股票的资本损益是一次或多次买卖股票后的全部收益或损失。
以任意顺序返回结果即可。
SQL查询结果的格式如下例所示:
Stocks 表:
+---------------+-----------+---------------+--------+
| stock_name | operation | operation_day | price |
+---------------+-----------+---------------+--------+
| Leetcode | Buy | 1 | 1000 |
| Corona Masks | Buy | 2 | 10 |
| Leetcode | Sell | 5 | 9000 |
| Handbags | Buy | 17 | 30000 |
| Corona Masks | Sell | 3 | 1010 |
| Corona Masks | Buy | 4 | 1000 |
| Corona Masks | Sell | 5 | 500 |
| Corona Masks | Buy | 6 | 1000 |
| Handbags | Sell | 29 | 7000 |
| Corona Masks | Sell | 10 | 10000 |
+---------------+-----------+---------------+--------+
Result 表:
+---------------+-------------------+
| stock_name | capital_gain_loss |
+---------------+-------------------+
| Corona Masks | 9500 |
| Leetcode | 8000 |
| Handbags | -23000 |
+---------------+-------------------+
Leetcode 股票在第一天以1000美元的价格买入,在第五天以9000美元的价格卖出。资本收益=9000-1000=8000美元。
Handbags 股票在第17天以30000美元的价格买入,在第29天以7000美元的价格卖出。资本损失=7000-30000=-23000美元。
Corona Masks 股票在第1天以10美元的价格买入,在第3天以1010美元的价格卖出。在第4天以1000美元的价格再次购买,在第5天以500美元的价格出售。最后,它在第6天以1000美元的价格被买走,在第10天以10000美元的价格被卖掉。资本损益是每次(’Buy'->'Sell')操作资本收益或损失的和=(1010-10)+(500-1000)+(10000-1000)=1000-500+9000=9500美元。
答案:
select stock_name,
sum(if(operation = 'Buy',-price,price))
as capital_gain_loss
from stocks
group by stock_name
解析:在mysql中if ()函数的用法类似于java中的三目表达式,其用处也比较多,具体语法如下: IF (expr1,expr2,expr3),如果expr1的值为true,则返回expr2的值,如果expr1的值为false,则返回expr3的值。
表: Seat
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| name | varchar |
+-------------+---------+
Id是该表的主键列。
该表的每一行都表示学生的姓名和ID。
Id是一个连续的增量。
编写SQL查询来交换每两个连续的学生的座位号。如果学生的数量是奇数,则最后一个学生的id不交换。
按 id 升序 返回结果表。
查询结果格式如下所示。
示例 1:
输入:
Seat 表:
+----+---------+
| id | student |
+----+---------+
| 1 | Abbot |
| 2 | Doris |
| 3 | Emerson |
| 4 | Green |
| 5 | Jeames |
+----+---------+
输出:
+----+---------+
| id | student |
+----+---------+
| 1 | Doris |
| 2 | Abbot |
| 3 | Green |
| 4 | Emerson |
| 5 | Jeames |
+----+---------+
解释:
请注意,如果学生人数为奇数,则不需要更换最后一名学生的座位。
答案:select (case when mod(id,2)=1 and id=(select count(*) from seat) then id
when mod(id,2)=0 then id-1
else id+1
end) as id,student from seat
order by id
解析:CASE
WHEN 条件1 THEN 结果1
WHEN 条件2 THEN 结果2
WHEN 条件3 THEN 结果3
WHEN 条件4 THEN 结果4
.........
WHEN 条件N THEN 结果N
ELSE 结果X
END
mod()方法是取模函数