当前位置:   article > 正文

力扣SQL专项突破33题详解及笔记--(由浅入深)_力扣专项训练

力扣专项训练

前言

首先感谢您的阅览,本篇是记录力扣SQL专项突破的题目详解和做题记录,有些知识点便于回顾。其中不足之处,也望诸君多多包涵,也欢迎您的指正。
也希望看完本篇笔记,您能心有所得。能解决您的疑惑与问题是本篇最大的荣誉。

下面两篇文章是针对MySQL的一些知识点的详细记录讲解

MySQL基础篇传送门:http://t.csdn.cn/Bkuoq

MySQL进阶篇传送门:http://t.csdn.cn/CMRdw


Day01 选择

595. 大的国家

先来一题能重拳出击的题目,where选择过滤解决掉。
±------------±--------+
| Column Name | Type |
±------------±--------+
| name | varchar |
| continent | varchar |
| area | int |
| population | int |
| gdp | int |
±------------±--------+
name 是这张表的主键。
这张表的每一行提供:国家名称、所属大陆、面积、人口和 GDP 值。

如果一个国家满足下述两个条件之一,则认为该国是 大国 :

  • 面积至少为 300 万平方公里(即,3000000 km2),或者
  • 人口至少为 2500 万(即 25000000)

编写一个 SQL 查询以报告 大国 的国家名称、人口和面积。

按 任意顺序 返回结果表。

在这里插入图片描述


解答

使用 WHERE 子句和 OR
如下
方式一

select name, population, area from World where area >= 3000000 or population >= 25000000;
  • 1

但是使用 or 会使索引可能失效,在数据量较大的时候查找效率较低,通常建议使用 union 代替 or
方式二

select t.name, t.population, t.area from world t
    where t.area >= 3000000
    union
select t.name, t.population, t.area from world t
    where t.population >= 25000000
  • 1
  • 2
  • 3
  • 4
  • 5

1757. 可回收且低脂的产品

±------------±--------+
| Column Name | Type |
±------------±--------+
| product_id | int |
| low_fats | enum |
| recyclable | enum |
±------------±--------+
product_id 是这个表的主键。
low_fats 是枚举类型,取值为以下两种 (‘Y’, ‘N’),其中 ‘Y’ 表示该产品是低脂产品,‘N’ 表示不是低脂产品。
recyclable 是枚举类型,取值为以下两种 (‘Y’, ‘N’),其中 ‘Y’ 表示该产品可回收,而 ‘N’ 表示不可回收。

写出 SQL 语句,查找既是低脂又是可回收的产品编号。

返回结果 无顺序要求 。

查询结果格式如下例所示

在这里插入图片描述

解答,前几题都是增强信心题,没什么好说的

select a.product_id
from Products a
where 
	a.low_fats = 'Y'
AND
	a.recyclable = 'Y'
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

584. 寻找用户推荐人

给定表 customer ,里面保存了所有客户信息和他们的推荐人
在这里插入图片描述

写一个查询语句,返回一个客户列表,列表中客户的推荐人的编号都 不是 2。

对于上面的示例数据,结果为:

在这里插入图片描述

select name from customer where referee_id <> 2 or referee_id is null;
  • 1

183. 从不订购的客户

某网站包含两个表,Customers 表和 Orders 表。编写一个 SQL 查询,找出所有从不订购任何东西的客户。

Customers 表:
在这里插入图片描述

Orders 表:
在这里插入图片描述

例如给定上述表格,你的查询应返回:
在这里插入图片描述

解答,当然,这里也可以用左外连接的方式来解

select c.Name as Customers from 
Customers c
where id not in (select o.CustomerId from Orders o);
  • 1
  • 2
  • 3

左外连接方式

select Customers.Name as Customers from 
Customers left join Orders on 
Customers.id = Orders.CustomerId 
where CustomerId is null;
  • 1
  • 2
  • 3
  • 4

Day02 排序 & 修改

1873. 计算特殊奖金

表: Employees
在这里插入图片描述

写出一个SQL 查询语句,计算每个雇员的奖金。如果一个雇员的id是奇数并且他的名字不是以’M’开头,那么他的奖金是他工资的100%,否则奖金为0。

Return the result table ordered by employee_id.

返回的结果集请按照employee_id排序。

查询结果格式如下面的例子所示。

在这里插入图片描述
在这里插入图片描述

解释:
因为雇员id是偶数,所以雇员id 是2和8的两个雇员得到的奖金是0。
雇员id为3的因为他的名字以’M’开头,所以,奖金是0。
其他的雇员得到了百分之百的奖金。

解答

这里用到了case when … then … else … end 流程控制函数,当然也可以用if(条件,true返回值,false返回值)
以下是两种方式的展示

select employee_id,
case
when mod(employee_id, 2) != 0  and substring(name,1,1) != 'M' then salary
else  0 
end as bonus 
from employees
order by employee_id asc;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

select employee_id,
if(
    employee_id%2 != 0 and name not like 'M%',
    salary,
    0
) as bonus
from employees order by employee_id asc;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

627. 变更性别

Salary 表
在这里插入图片描述
请你编写一个 SQL 查询来交换所有的 ‘f’ 和 ‘m’ (即,将所有 ‘f’ 变为 ‘m’ ,反之亦然),仅使用 单个 update 语句 ,且不产生中间临时表。

注意,你必须仅使用一条 update 语句,且 不能 使用 select 语句。

查询结果如下例所示。
在这里插入图片描述

在这里插入图片描述
解释:
(1, A) 和 (3, C) 从 ‘m’ 变为 ‘f’ 。
(2, B) 和 (4, D) 从 ‘f’ 变为 ‘m’ 。

case when方式

update salary set sex = case  sex when 'm' then 'f' else 'm' end;
  • 1

if函数方式

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

196. 删除重复的电子邮箱(稍难)

表: Person
在这里插入图片描述
编写一个 SQL 删除语句来 删除 所有重复的电子邮件,只保留一个id最小的唯一电子邮件。

以 任意顺序 返回结果表。 (注意: 仅需要写删除语句,将自动对剩余结果进行查询)

查询结果格式如下所示。

在这里插入图片描述
在这里插入图片描述

解答

用了自连接的方式,自连接, 就是从驱动表(左表) 从上往下, 依次拿一行行数据, 去复制的新表(右表), 一行行去对比, 看是否符合where的条件, 如果符合, 就返回, 看是做select还是delete操作;

在这里插入图片描述

a. 从表p1取出3条记录;
b. 拿着第1条记录去表p2查找满足WHERE的记录,代入该条件p1.Email = p2.Email AND p1.Id > p2.Id后,发现没有满足的,所以不用删掉记录1;
c. 记录2同理;
d. 拿着第3条记录去表p2查找满足WHERE的记录,发现有一条记录满足,所以要从p1删掉记录3;
e. 3条记录遍历完,删掉了1条记录,这个DELETE也就结束了。

DELETE p1 FROM Person p1,
    Person p2
WHERE
    p1.Email = p2.Email AND p1.Id > p2.Id;
  • 1
  • 2
  • 3
  • 4

Day03 字符串处理函数/正则

1667. 修复表中的名字

表: Users
在这里插入图片描述
编写一个 SQL 查询来修复名字,使得只有第一个字符是大写的,其余都是小写的。

返回按 user_id 排序的结果表。

查询结果格式示例如下。
在这里插入图片描述
在这里插入图片描述

解答,主要考查字符串函数的运用,可以使用substring截取,也可以使用left, right 函数从左右截取

left,right函数

select user_id ,
concat(UPPER(left(name, 1)), LOWER(right(name,length(name)-1))) as name
from users
order by user_id;
  • 1
  • 2
  • 3
  • 4

substring函数

select user_id, 
concat (upper(substring(name,1,1)), lower(substring(name,2)))
as name from users order by user_id;
  • 1
  • 2
  • 3

总结

知识点:字符串函数

知识点
1 CONCAT() 函数
CONCAT 可以将多个字符串拼接在一起。

2 LEFT(str, length) 函数
从左开始截取字符串,length 是截取的长度。

3 UPPER(str) 与 LOWER(str)
UPPER(str) 将字符串中所有字符转为大写

LOWER(str) 将字符串中所有字符转为小写

4 SUBSTRING(str, begin, end)
截取字符串,end 不写默认为空。
SUBSTRING(name, 2) 从第二个截取到末尾,注意并不是下标,就是第二个。


  • CONCAT 用来拼接字符串
  • LEFT 从左边截取字符
  • RIGHT 从右边截取字符
  • UPPER 变为大写
  • LOWER 变为小写
  • LENGTH 获取字符串长度

1484. 按日期分组销售产品(有点难)

表 Activities:
在这里插入图片描述
编写一个 SQL 查询来查找每个日期、销售的不同产品的数量及其名称。
每个日期的销售产品名称应按词典序排列。
返回按 sell_date 排序的结果表。
查询结果格式如下例所示。
在这里插入图片描述
在这里插入图片描述
解释:
对于2020-05-30,出售的物品是 (Headphone, Basketball, T-shirt),按词典序排列,并用逗号 ‘,’ 分隔。
对于2020-06-01,出售的物品是 (Pencil, Bible),按词典序排列,并用逗号分隔。
对于2020-06-02,出售的物品是 (Mask),只需返回该物品名。

解答
①:直接select sell_date
②:num_sold:当前这个日期下,卖出去了多少个不同的产品(注意是“不同的”,这决定了我们需要用DISTINCT去重),我们需要进行“计数”。但,并不是简单的统计某一列中有多少条数据,而是需要我们分别统计:product列中,sell_date分别等于各个日期的不同的产品有多少个(比如:product列中,sell_date等于2020-05-30的不同的产品共有3个),因此,这里应该想到用分组语句GROUP BY
③:实际上就是把同一sell_date下的不同产品的名称连接起来,这里就涉及到GROUP_CONCAT()这个函数了

知识点:GROUP_CONCAT函数

在这里插入图片描述

执行如下sql 语句:
SELECT id,GROUP_CONCAT(score) from score GROUP BY id;
在这里插入图片描述
由此可以解答该题

SELECT sell_date,
       COUNT(DISTINCT product) num_sold,
       GROUP_CONCAT(DISTINCT product) products
FROM Activities
GROUP BY sell_date;
  • 1
  • 2
  • 3
  • 4
  • 5

1527. 患某种疾病的患者

患者信息表: Patients
在这里插入图片描述
patient_id (患者 ID)是该表的主键。
‘conditions’ (疾病)包含 0 个或以上的疾病代码,以空格分隔。
这个表包含医院中患者的信息。

写一条 SQL 语句,查询患有 I 类糖尿病的患者 ID (patient_id)、患者姓名(patient_name)以及其患有的所有疾病代码(conditions)。I 类糖尿病的代码总是包含前缀 DIAB1 。

按 任意顺序 返回结果表。

查询结果格式如下示例所示。

在这里插入图片描述

在这里插入图片描述

select patient_id, patient_name, conditions from 
patients 
where conditions like  'DIAB1%' or conditions like  '% DIAB1%';
  • 1
  • 2
  • 3

Day04 组合查询 & 指定选取

1965. 丢失信息的雇员(稍难)

表: Employees
在这里插入图片描述

表: Salaries
在这里插入图片描述
写出一个查询语句,找到所有 丢失信息 的雇员id。当满足下面一个条件时,就被认为是雇员的信息丢失:

雇员的 姓名 丢失了,或者
雇员的 薪水信息 丢失了,或者
返回这些雇员的id employee_id , 从小到大排序 。

查询结果格式如下面的例子所示。
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

解答

select employee_id from
(select employee_id from employees
union all
select employee_id from salaries) as t
group by employee_id
having count(employee_id)=1
order by employee_id;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

1.先将employee_id都从两表查出来用union all合并(注意: union 会自动去除关联的两个结果集中的重复数据 union all 不会主动去除两个结果集中的重复数据),将结果合并成一张结果集表
2.再根据employee_id分组,这样信息丢失的他的count(employee_id)只有1次,而信息未丢失的两表都查出来合并,count就为2,可以having过滤分组后的数据
3.最后根据employee_id排序就结束了


1795. 每个产品在不同商店的价格

表:Products
在这里插入图片描述
请你重构 Products 表,查询每个产品在不同商店的价格,使得输出的格式变为(product_id, store, price) 。如果这一产品在商店里没有出售,则不输出这一行。

输出结果表中的 顺序不作要求 。

查询输出格式请参考下面示例。
在这里插入图片描述
在这里插入图片描述
解释:
产品0在store1,store2,store3的价格分别为95,100,105。
产品1在store1,store3的价格分别为70,80。在store2无法买到。

解答

SELECT product_id, 'store1' store, store1 price FROM products WHERE store1 IS NOT NULL
UNION
SELECT product_id, 'store2' store, store2 price FROM products WHERE store2 IS NOT NULL
UNION
SELECT product_id, 'store3' store, store3 price FROM products WHERE store3 IS NOT NULL;
  • 1
  • 2
  • 3
  • 4
  • 5

十分经典的列转行
【行转列——MAX/SUM+CASE WHEN+GROUP BY】
【列转行——MAX+UNION+GROUP BY】
之所以用max或sum,是因为 会判断有空值,所以要取最大的。详细地址:行转列,列转行
如果’store1’不加引号是给原有的store1这一列取别名,而值还是原来的值,也就是那些价格数字; 加了引号是说创造一个新的列叫store,这一列的所有值都是"store1"


608. 树节点

给定一个表 tree,id 是树节点的编号, p_id 是它父节点的 id 。
在这里插入图片描述
树中每个节点属于以下三种类型之一:

叶子:如果这个节点没有任何孩子节点。
根:如果这个节点是整棵树的根,即没有父节点。
内部节点:如果这个节点既不是叶子节点也不是根节点。

写一个查询语句,输出所有节点的编号和节点的类型,并将结果按照节点编号排序。上面样例的结果为:
在这里插入图片描述
解释

节点 ‘1’ 是根节点,因为它的父节点是 NULL ,同时它有孩子节点 ‘2’ 和 ‘3’ 。
节点 ‘2’ 是内部节点,因为它有父节点 ‘1’ ,也有孩子节点 ‘4’ 和 ‘5’ 。
节点 ‘3’, ‘4’ 和 ‘5’ 都是叶子节点,因为它们都有父节点同时没有孩子节点。

解答

select id,
(case
when p_id is null then 'Root'
when id not in (select ifnull(p_id,0) from tree) then 'Leaf'
else 'Inner'
end)
as Type from tree order by id;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

176. 第二高的薪水

Employee 表:
在这里插入图片描述
编写一个 SQL 查询,获取并返回 Employee 表中第二高的薪水 。如果不存在第二高的薪水,查询应该返回 null 。

查询结果如下例所示。
在这里插入图片描述
在这里插入图片描述

解答

SELECT
    (SELECT DISTINCT
            Salary
        FROM
            Employee
        ORDER BY Salary DESC
        LIMIT 1 OFFSET 1) AS SecondHighestSalary;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

将不同的薪资按降序排序,然后使用 LIMIT 子句获得第二高的薪资,然而,如果没有这样的第二最高工资,需要返回null,可以将其作为临时表来返回null。

知识点:limit和offset

1、当 limit后面跟一个参数的时候,该参数表示要取的数据的数量

例如 select* from user limit 3 表示直接取前三条数据

2、当limit后面跟两个参数的时候,第一个数表示要跳过的数量,后一位表示要取的数量,例如

select * from user limit 1,3;

就是跳过1条数据,从第2条数据开始取,取3条数据,也就是取2,3,4三条数据

3、当 limit和offset组合使用的时候,limit后面只能有一个参数,表示要取的的数量,offset表示要跳过的数量 。

例如select * from user limit 3 offset 1;表示跳过1条数据,从第2条数据开始取,取3条数据,也就是取2,3,4三条数据


Day05 合并

175. 组合两个表

表: Person
在这里插入图片描述

表: Address
在这里插入图片描述
编写一个SQL查询来报告 Person 表中每个人的姓、名、城市和州。如果 personId 的地址不在 Address 表中,则报告为空 null 。

以 任意顺序 返回结果表。

查询结果格式如下所示。
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

解答

select p.firstName, p.lastName, a.city, a.state from
person p left join address a on p.personid = a.personid;
  • 1
  • 2

知识点:左外连接

内连接 inner join:A,B表值都存在情况

外连接 outer join:附表中值可能存在null的情况。

外连接又分左外连接和右外连接

左外连接是取A表全部,B表没有对应的值,则为null

右外连接是取B表全部,A表没有对应的值,则为null

所以该题很明显需要用左外连接,因为personId在Address表中不存在,其city和state都要置空null,但是person表里的firstName,lastName还是要全部展示出来的

第一次我写的where过滤,结果由于连接条件为person.personId = address.personId,而personId在Address表中不存在的情况会直接俄过滤掉,它就不会显示姓名信息了。

总结:

①A inner join B:取交集

②A left join B:取A全部,B没有对应的值,则为null

③A right join B:取B全部,A没有对应的值,则为null

④A full outer join B:取并集,彼此没有对应的值为null

上述4种的对应条件,在on后填写。


1581. 进店却未进行过交易的顾客(较难)

表:Visits
在这里插入图片描述
表:Transactions
在这里插入图片描述
有一些顾客可能光顾了购物中心但没有进行交易。请你编写一个 SQL 查询,来查找这些顾客的 ID ,以及他们只光顾不交易的次数。

返回以 任何顺序 排序的结果表。

查询结果格式如下例所示。
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
解释:
ID = 23 的顾客曾经逛过一次购物中心,并在 ID = 12 的访问期间进行了一笔交易。
ID = 9 的顾客曾经逛过一次购物中心,并在 ID = 13 的访问期间进行了一笔交易。
ID = 30 的顾客曾经去过购物中心,并且没有进行任何交易。
ID = 54 的顾客三度造访了购物中心。在 2 次访问中,他们没有进行任何交易,在 1 次访问中,他们进行了 3 次交易。
ID = 96 的顾客曾经去过购物中心,并且没有进行任何交易。
如我们所见,ID 为 30 和 96 的顾客一次没有进行任何交易就去了购物中心。顾客 54 也两次访问了购物中心并且没有进行任何交易。

解答

select V.customer_id, count(distinct V.visit_id) as count_no_trans 
from Visits V left join Transactions T
on V.visit_id = T.visit_id where T.transaction_id is null
group by(V.customer_id)
order by count_no_trans desc;
  • 1
  • 2
  • 3
  • 4
  • 5

讲解看这位博主,已经图文并茂了,很清晰:详细解法


1148. 文章浏览

Views 表:
在这里插入图片描述
此表无主键,因此可能会存在重复行。
此表的每一行都表示某人在某天浏览了某位作者的某篇文章。
请注意,同一人的 author_id 和 viewer_id 是相同的。

请编写一条 SQL 查询以找出所有浏览过自己文章的作者,结果按照 id 升序排列。

查询结果的格式如下所示:
在这里插入图片描述
在这里插入图片描述

解答

SELECT DISTINCT author_id AS id
FROM  Views
WHERE author_id = viewer_id
ORDER BY author_id;
  • 1
  • 2
  • 3
  • 4

查询以找出所有浏览过自己文章的作者,翻译过来就是,观看文章的读者同时也必须是作者才满足条件(如果是单纯读者,则条件不满足),只需要过滤一下读者id是否也是作者id就可以了


197. 上升的温度(经典)

表: Weather
在这里插入图片描述
编写一个 SQL 查询,来查找与之前(昨天的)日期相比温度更高的所有日期的 id 。

返回结果 不要求顺序 。

查询结果格式如下例。

在这里插入图片描述
在这里插入图片描述

解答

这第一种方式,自然用的是自连接加上日期函数

认识一下 DATEDIFF 函数,可以计算两者的日期差

DATEDIFF(‘2007-12-31’,‘2007-12-30’); # 1
DATEDIFF(‘2010-12-30’,‘2010-12-31’); # -1

select a.id from 
weather a, weather b 
where
a.temperature > b.temperature and datediff(a.recordDate, b.recordDate) = 1;
  • 1
  • 2
  • 3
  • 4

当然方法不只这一种,下面推荐一个更好玩的,用TIMESTAMPDIFF

相比于datediff函数要灵活很多。可以计算相差天数、小时、分钟和秒。格式是时间小的前,时间大的放在后面
如下
计算相差天数

select TIMESTAMPDIFF(DAY,'2019-05-20', '2019-05-21'); # 1
  • 1

计算相差小时数:

select TIMESTAMPDIFF(HOUR, '2015-03-22 07:00:00', '2015-03-22 18:00:00'); # 11
  • 1

该题解法

select a.id from 
weather a, weather b 
where 
a.temperature > b.temperature and timestampdiff(day, b.recordDate, a.recordDate) = 1;
  • 1
  • 2
  • 3
  • 4

607. 销售员

在这里插入图片描述
sales_id 是该表的主键列。
该表的每一行都显示了销售人员的姓名和 ID ,以及他们的工资、佣金率和雇佣日期。
在这里插入图片描述

com_id 是该表的主键列。
该表的每一行都表示公司的名称和 ID ,以及公司所在的城市。

表: Orders

在这里插入图片描述
order_id 是该表的主键列。
com_id 是 Company 表中 com_id 的外键。
sales_id 是来自销售员表 sales_id 的外键。
该表的每一行包含一个订单的信息。这包括公司的 ID 、销售人员的 ID 、订单日期和支付的金额。

编写一个SQL查询,报告没有任何与名为 “RED” 的公司相关的订单的所有销售人员的姓名。

以 任意顺序 返回结果表。

查询结果格式如下所示。
在这里插入图片描述

解答

select name from SalesPerson s where s.sales_id 
not in 
(SELECT o.sales_id from company c inner join Orders o on c.com_id = o.com_id and c.name = 'RED');

  • 1
  • 2
  • 3
  • 4

Day06 计算函数

1141. 查询近30天活跃用户数

活动记录表:Activity
在这里插入图片描述
该表是用户在社交网站的活动记录。
该表没有主键,可能包含重复数据。
activity_type 字段为以下四种值 (‘open_session’, ‘end_session’, ‘scroll_down’, ‘send_message’)。
每个 session_id 只属于一个用户。

请写SQL查询出截至 2019-07-27(包含2019-07-27),近 30 天的每日活跃用户数(当天只要有一条活动记录,即为活跃用户)。

以 任意顺序 返回结果表。

查询结果示例如下。
在这里插入图片描述
在这里插入图片描述

这题题目属实没看明白

每个 session_id 只属于一个用户。

这个也太诱导人犯错了 以为一个用户只能由一个session_id

其实一个用户可以有多个session_id 不知道题目给这个条件是想说明什么

所以这道题根本不可以用session_id进行count

select activity_date day, count(distinct user_id) active_users
from activity
where datediff('2019-07-27', activity_date) >= 0 AND datediff('2019-07-27', activity_date) <30
group by activity_date
  • 1
  • 2
  • 3
  • 4

1693. 每天的领导和合伙人

在这里插入图片描述

按 任意顺序 返回结果表。

查询结果格式如下示例所示。
在这里插入图片描述
解释:
在 2020-12-8,丰田(toyota)有领导者 = [0, 1] 和合伙人 = [0, 1, 2] ,同时本田(honda)有领导者 = [1, 2] 和合伙人 = [1, 2]。
在 2020-12-7,丰田(toyota)有领导者 = [0] 和合伙人 = [1, 2] ,同时本田(honda)有领导者 = [0, 1, 2] 和合伙人 = [1, 2]。
写一条 SQL 语句,使得对于每一个 date_id 和 make_name,返回不同的 lead_id 以及不同的 partner_id 的数量。

解答
group by 的意思为分组汇总 使用了group by 后,要求Select出的结果字段都是可汇总的,否则就会出错 group by 有一个原则,就是 select 后面的所有列中,没有使用聚合函数的列,必须出现在 group by 后面

一、抽题干 1. 对于每一个 date_id 和 make_name,返回不同的 lead_id 以及不同的 partner_id 的数量

二: 观察发现:其实就是按照日期、make_name进行分组,然后各自对lead_id、 partner_id进行去重统计


1729. 求关注者的数量

表: Followers

在这里插入图片描述
写出 SQL 语句,对于每一个用户,返回该用户的关注者数量。

按 user_id 的顺序返回结果表。

查询结果的格式如下示例所示。

在这里插入图片描述

在这里插入图片描述
解释:
0 的关注者有 {1}
1 的关注者有 {0}
2 的关注者有 {0,1}

这题就比较简单了,别想复杂了,就是根据user_id分组,然后用count函数统计每组数量就行了,不需要用distinct去重,别想太多,就这么简单

select  user_id, count(follower_id) followers_count 
from followers
group by user_id
order by user_id;
  • 1
  • 2
  • 3
  • 4

586. 订单最多的客户

表: Orders
在这里插入图片描述

编写一个SQL查询,为下了 最多订单 的客户查找 customer_number 。

测试用例生成后, 恰好有一个客户 比任何其他客户下了更多的订单。

查询结果格式如下所示。

在这里插入图片描述

在这里插入图片描述

解答

先根据customer_number分组,再对分组按条数降序,最后用limit分页取出第一条就是订单最多的用户

select customer_number from orders
group by customer_number
order by count(*) desc
limit 1
  • 1
  • 2
  • 3
  • 4

511. 游戏玩法分析 I

活动表 Activity:
在这里插入图片描述
表的主键是 (player_id, event_date)。
这张表展示了一些游戏玩家在游戏平台上的行为活动。
每行数据记录了一名玩家在退出平台之前,当天使用同一台设备登录平台后打开的游戏的数目(可能是 0 个)。

写一条 SQL 查询语句获取每位玩家 第一次登陆平台的日期。

查询结果的格式如下所示:
在这里插入图片描述
在这里插入图片描述

解答

先根据player_id分组,取出每组最小值来查询

select player_id, min(event_date) as first_login
from activity
group by player_id;
  • 1
  • 2
  • 3

1890. 2020年最后一次登录

表: Logins
在这里插入图片描述
编写一个 SQL 查询,该查询可以获取在 2020 年登录过的所有用户的本年度 最后一次 登录时间。结果集 不 包含 2020 年没有登录过的用户。

返回的结果集可以按 任意顺序 排列。

查询结果格式如下例。

在这里插入图片描述
在这里插入图片描述
解释:
6号用户登录了3次,但是在2020年仅有一次,所以结果集应包含此次登录。
8号用户在2020年登录了2次,一次在2月,一次在12月,所以,结果集应该包含12月的这次登录。
2号用户登录了2次,但是在2020年仅有一次,所以结果集应包含此次登录。
14号用户在2020年没有登录,所以结果集不应包含。

解答

这题考查的是日期函数

select user_id, max(time_stamp) as last_stamp
from Logins
where year(time_stamp) = '2020'
group by user_id
  • 1
  • 2
  • 3
  • 4

1741. 查找每个员工花费的总时间

表: Employees
在这里插入图片描述
(emp_id, event_day, in_time) 是这个表的主键。
该表显示了员工在办公室的出入情况。
event_day 是此事件发生的日期,in_time 是员工进入办公室的时间,而 out_time 是他们离开办公室的时间。
in_time 和 out_time 的取值在1到1440之间。
题目保证同一天没有两个事件在时间上是相交的,并且保证 in_time 小于 out_time。

编写一个SQL查询以计算每位员工每天在办公室花费的总时间(以分钟为单位)。 请注意,在一天之内,同一员工是可以多次进入和离开办公室的。 在办公室里一次进出所花费的时间为out_time 减去 in_time。

返回结果表单的顺序无要求。
查询结果的格式如下:

在这里插入图片描述
在这里插入图片描述
雇员 1 有三次进出: 有两次发生在 2020-11-28 花费的时间为 (32 - 4) + (200 - 55) = 173, 有一次发生在 2020-12-03 花费的时间为 (42 - 1) = 41。
雇员 2 有两次进出: 有一次发生在 2020-11-28 花费的时间为 (33 - 3) = 30, 有一次发生在 2020-12-09 花费的时间为 (74 - 47) = 27。

解答

难点就是怎么想这个是按日期和id分组的

select event_day day, emp_id, sum(out_time-in_time) total_time
from Employees
group by day, emp_id
  • 1
  • 2
  • 3

Day07 控制流

1393. 股票的资本损益

在这里插入图片描述

编写一个SQL查询来报告每支股票的资本损益。

股票的资本损益是一次或多次买卖股票后的全部收益或损失。

以任意顺序返回结果即可。

SQL查询结果的格式如下例所示:
在这里插入图片描述
在这里插入图片描述
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美元。

解答

这里用case…when比较合适,最后用sum求和;如果是售出,就是加上price,如果是买入,就加上-price

SELECT stock_name,
        SUM(
            CASE operation WHEN 'sell'   #如果我们的操作是卖出
            THEN price ELSE -price   #那么返回我们的到的收益,反之,操作是买入,返回支出
            END                  
           ) AS capital_gain_loss      #计算卖出和买入差值的和
FROM Stocks
GROUP BY stock_name  #最后结果是要查询每支股票的资本损益,需要对股票进行分组
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8

1407. 排名靠前的旅行者

表:Users
在这里插入图片描述
表:Rides
在这里插入图片描述
写一段 SQL , 报告每个用户的旅行距离。

返回的结果表单,以 travelled_distance 降序排列 ,如果有两个或者更多的用户旅行了相同的距离, 那么再以 name 升序排列 。

查询结果格式如下例所示。
在这里插入图片描述
在这里插入图片描述

在这里插入图片描述

Elvis 和 Lee 旅行了 450 英里,Elvis 是排名靠前的旅行者,因为他的名字在字母表上的排序比 Lee 更小。
Bob, Jonathan, Alex 和 Alice 只有一次行程,我们只按此次行程的全部距离对他们排序。
Donald 没有任何行程, 他的旅行距离为 0。

解答

select u.name, ifnull(sum(r.distance),0) as travelled_distance from users u left join rides r
on u.id = r.user_id
group by u.id
order by travelled_distance desc, u.name asc
  • 1
  • 2
  • 3
  • 4

1158. 市场分析 I

Table: Users
在这里插入图片描述
Table: Orders
在这里插入图片描述
请写出一条SQL语句以查询每个用户的注册日期和在 2019 年作为买家的订单总数。

以 任意顺序 返回结果表。

查询结果格式如下。
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

解答

WHERE和ON筛选的区别
外连接时要注意where和on的区别,on是在连接构造临时表时执行的,不管on中条件是否成立都会返回主表(也就是left join左边的表)的内容,where是在临时表形成后执行筛选作用的,不满足条件的整行都会被过滤掉。如果这里用的是 where year(order_date)=‘2019’ 那么得到的结果将会把不满足条件的user_id为3,4的行给删掉。用on的话会保留user_id为3,4的行

select user_id as buyer_id, join_date, count(order_id) as orders_in_2019
from Users as u left join Orders as o on u.user_id = o.buyer_id and year(order_date)='2019'
group by user_id
  • 1
  • 2
  • 3

Day08 过滤

182. 查找重复的电子邮箱

编写一个 SQL 查询,查找 Person 表中所有重复的电子邮箱。

示例:

在这里插入图片描述
根据以上输入,你的查询应返回以下结果:
在这里插入图片描述

解答

这题就很简单了,直接通过对邮箱分组,再对个数进行过滤就能得出答案

select email from person 
group by email
having count(id) > 1
  • 1
  • 2
  • 3

1050. 合作过至少三次的演员和导演

ActorDirector 表:
在这里插入图片描述
写一条SQL查询语句获取合作过至少三次的演员和导演的 id 对 (actor_id, director_id)

示例:

在这里插入图片描述
在这里插入图片描述

解答

同样使用到了分组过滤,只是这里分组是按演员id和导演id共同分组的,最后通过having过滤出次数大于3的即可

select actor_id, director_id from actorDirector
group by actor_id,director_id
having count(actor_id) >= 3;
  • 1
  • 2
  • 3

1587. 银行账户概要 II

表: Users

在这里插入图片描述

在这里插入图片描述
写一个 SQL, 报告余额高于 10000 的所有用户的名字和余额. 账户的余额等于包含该账户的所有交易的总和.

返回结果表单没有顺序要求.

查询结果格式如下例所示.

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

解答

1.左外连接 2.分组 3.过滤

select u.name, sum(t.amount) as balance
from Users as u
left join Transactions as t on u.account = t.account
group by t.account
having sum(t.amount) > 10000
  • 1
  • 2
  • 3
  • 4
  • 5

1084. 销售分析III

Table: Product

在这里插入图片描述

Table: Sales
在这里插入图片描述
编写一个SQL查询,报告2019年春季才售出的产品。即仅在2019-01-01至2019-03-31(含)之间出售的商品。

以 任意顺序 返回结果表。

查询结果格式如下所示。

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

解答

先使用内连接,连接两表,然后根据产品id分组,过滤出销售日期在春季里的

select 
s.product_id, p.product_name 
from product p join sales s
on p.product_id = s.product_id
group by s.product_id
HAVING MIN(sale_date) >= '2019-01-01' AND MAX(sale_date) <= '2019-03-31'
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/知新_RL/article/detail/872041
推荐阅读
相关标签
  

闽ICP备14008679号