力扣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;

但是使用 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

---

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'

---

584. 寻找用户推荐人

给定表 customer ，里面保存了所有客户信息和他们的推荐人

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

对于上面的示例数据，结果为：

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

---

183. 从不订购的客户

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

Customers 表：

Orders 表：

例如给定上述表格，你的查询应返回：

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

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

左外连接方式

select Customers.Name as Customers from 
Customers left join Orders on 
Customers.id = Orders.CustomerId 
where CustomerId is null;

---

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;

---

select employee_id,
if(
    employee_id%2 != 0 and name not like 'M%',
    salary,
    0
) as bonus
from employees order by employee_id asc;

---

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;

if函数方式

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

---

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;

---

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;

substring函数

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

---

总结

知识点：字符串函数

知识点
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;

---

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%';

---

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.先将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;

十分经典的列转行
【行转列——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;

---

176. 第二高的薪水

Employee 表：

编写一个 SQL 查询，获取并返回 Employee 表中第二高的薪水 。如果不存在第二高的薪水，查询应该返回 null 。

查询结果如下例所示。

解答

SELECT
    (SELECT DISTINCT
            Salary
        FROM
            Employee
        ORDER BY Salary DESC
        LIMIT 1 OFFSET 1) AS SecondHighestSalary;

将不同的薪资按降序排序，然后使用 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;

知识点：左外连接

内连接 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;

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

---

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;

查询以找出所有浏览过自己文章的作者，翻译过来就是，观看文章的读者同时也必须是作者才满足条件（如果是单纯读者，则条件不满足），只需要过滤一下读者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;

---

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

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

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

计算相差小时数：

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

该题解法

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

---

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');

---

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

---

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;

---

586. 订单最多的客户

表: Orders

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

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

查询结果格式如下所示。

解答

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

select customer_number from orders
group by customer_number
order by count(*) desc
limit 1

---

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;

---

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

---

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

---

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  #最后结果是要查询每支股票的资本损益，需要对股票进行分组

---

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

---

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

---

Day08 过滤

182. 查找重复的电子邮箱

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

示例：

根据以上输入，你的查询应返回以下结果：

解答

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

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

---

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;

---

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

---

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'