LeetCode刷题100道，让你滚瓜烂熟拿下SQL
??????
哈喽！大家好，我是【IT邦德】，江湖人称jeames007，10年DBA工作经验
一位上进心十足的【大数据领域博主】！???
中国DBA联盟(ACDU)成员，目前从事DBA及程序编程
擅长主流数据Oracle、MySQL、PG 运维开发，备份恢复，安装迁移，性能优化、故障应急处理等。
✨ 如果有对【数据库】感兴趣的【小可爱】，欢迎关注【IT邦德】???
❤️❤️❤️感谢各位大可爱小可爱！❤️❤️❤️
对人工智能感兴趣的小伙伴，请多关注以下通俗易懂，风趣幽默的人工智能体系分析
https://www.captainai.net/jeames
文章目录
前言
SQL每个人都要用，但是用来衡量产出的并不是SQL本身，你需要用这个工具，去创造其它的价值。
1.SQL 入门
?595.大的国家
? World 表：
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| name | varchar |
| continent | varchar |
| area | int |
| population | int |
| gdp | int |
+-------------+---------+
name 是这张表的主键。
这张表的每一行提供：国家名称、所属大陆、面积、人口和 GDP 值。
? 需求
如果一个国家满足下述两个条件之一，则认为该国是大国 ：
面积至少为 300 平方公里（即，3000000 km2），或者人口至少为 2500 万（即 25000000）
编写一个 SQL 查询以报告 大国 的国家名称、人口和面积。
按 任意顺序 返回结果表。
查询结果格式如下例所示。
? 示例：
输入：
World 表：
+-------------+-----------+---------+------------+--------------+
| name | continent | area | population | gdp |
+-------------+-----------+---------+------------+--------------+
| Afghanistan | Asia | 652230 | 25500100 | 20343000000 |
| Albania | Europe | 28748 | 2831741 | 12960000000 |
| Algeria | Africa | 2381741 | 37100000 | 188681000000 |
| Andorra | Europe | 468 | 78115 | 3712000000 |
| Angola | Africa | 1246700 | 20609294 | 100990000000 |
+-------------+-----------+---------+------------+--------------+
输出：
+-------------+------------+---------+
| name | population | area |
+-------------+------------+---------+
| Afghanistan | 25500100 | 652230 |
| Algeria | 37100000 | 2381741 |
+-------------+------------+---------+
?? 答案
# Write your MySQL query statement below
select name,population,area from World
where area>=3000000
or population >=25000000
/* Write your T-SQL query statement below */
select name,population,area from World
where area>=3000000
or population >=25000000
/* Write your PL/SQL query statement below */
select
name "name",
population "population",
area "area"
from World
where area>=3000000
or population >=25000000
?1757. 可回收且低脂的产品
? 表：Products
+-------------+---------+
| 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 语句，查找既是低脂又是可回收的产品编号。
返回结果 无顺序要求 。
查询结果格式如下例所示：
Products 表：
+-------------+----------+------------+
| product_id | low_fats | recyclable |
+-------------+----------+------------+
| 0 | Y | N |
| 1 | Y | Y |
| 2 | N | Y |
| 3 | Y | Y |
| 4 | N | N |
+-------------+----------+------------+
Result 表：
+-------------+
| product_id |
+-------------+
| 1 |
| 3 |
+-------------+
只有产品 id 为 1 和 3 的产品，既是低脂又是可回收的产品。
?? 答案
# Write your MySQL query statement below
select product_id from Products
where low_fats = 'Y'
and recyclable ='Y'
/* Write your T-SQL query statement below */
select product_id from Products
where low_fats = 'Y'
and recyclable ='Y'
/* Write your PL/SQL query statement below */
select product_id "product_id" from Products
where low_fats = 'Y'
and recyclable ='Y'
?584. 寻找用户推荐人
? 给定表 customer ，里面保存了所有客户信息和他们的推荐人。
+------+------+-----------+
| id | name | referee_id|
+------+------+-----------+
| 1 | Will | NULL |
| 2 | Jane | NULL |
| 3 | Alex | 2 |
| 4 | Bill | NULL |
| 5 | Zack | 1 |
| 6 | Mark | 2 |
+------+------+-----------+
? 需求
写一个查询语句，返回一个客户列表，列表中客户的推荐人的编号都不是2。
对于上面的示例数据，结果为：
+------+
| name |
+------+
| Will |
| Jane |
| Bill |
| Zack |
+------+
?? 答案
# Write your MySQL query statement below
select name from customer
where IFNULL(referee_id,0) <> 2
--mysql判断非空的函数
ISNULL(expr) 如果expr为null返回值1，否则返回值为0
IFNULL(expr1,expr2) 如果expr1值为null返回expr2的值，否则返回expr1的值
/* Write your T-SQL query statement below */
select name from customer
where referee_id <> 2 OR referee_id IS NULL
/* Write your PL/SQL query statement below */
select name "name" from customer
where nvl(referee_id,0) <> 2
?183. 从不订购的客户
? 某网站包含两个表，Customers 表和 Orders 表。编写一个 SQL 查询，找出所有从不订购任何东西的客户。
Customers 表：
+----+-------+
| Id | Name |
+----+-------+
| 1 | Joe |
| 2 | Henry |
| 3 | Sam |
| 4 | Max |
+----+-------+
Orders 表：
+----+------------+
| Id | CustomerId |
+----+------------+
| 1 | 3 |
| 2 | 1 |
+----+------------+
? 需求
例如给定上述表格，你的查询应返回：
+-----------+
| Customers |
+-----------+
| Henry |
| Max |
+-----------+
?? 答案
# Write your MySQL query statement below
select Name "Customers" from Customers
where id not in (select CustomerId from Orders)
/* Write your T-SQL query statement below */
select Name "Customers" from Customers
where id not in (select CustomerId from Orders)
/* Write your PL/SQL query statement below */
select Name "Customers" from Customers a
where not exists (select 1 from Orders b where a.Id = b.CustomerId)
order by 1
2 排序 & 修改
?1873. 计算特殊奖金
? 表: Employees
+-------------+---------+
| 列名 | 类型 |
+-------------+---------+
| employee_id | int |
| name | varchar |
| salary | int |
+-------------+---------+
employee_id 是这个表的主键。
此表的每一行给出了雇员id ，名字和薪水。
? 需求
写出一个SQL 查询语句，计算每个雇员的奖金。如果一个雇员的id是奇数并且他的名字不是以'M'开头，那么他的奖金是他工资的100%，否则奖金为0。
Return the result table ordered by employee_id.
返回的结果集请按照employee_id排序。
查询结果格式如下面的例子所示。
示例 1:
输入：
Employees 表:
+-------------+---------+--------+
| employee_id | name | salary |
+-------------+---------+--------+
| 2 | Meir | 3000 |
| 3 | Michael | 3800 |
| 7 | Addilyn | 7400 |
| 8 | Juan | 6100 |
| 9 | Kannon | 7700 |
+-------------+---------+--------+
输出：
+-------------+-------+
| employee_id | bonus |
+-------------+-------+
| 2 | 0 |
| 3 | 0 |
| 7 | 7400 |
| 8 | 0 |
| 9 | 7700 |
+-------------+-------+
解释：
因为雇员id是偶数，所以雇员id 是2和8的两个雇员得到的奖金是0。
雇员id为3的因为他的名字以'M'开头，所以，奖金是0。
其他的雇员得到了百分之百的奖金。
?? 答案
# Write your MySQL query statement below
select
employee_id,
case when mod(employee_id,2)=1 and LEFT(name,1)!='M' then salary
else 0 end bonus
from Employees
order by employee_id
/* Write your T-SQL query statement below */
select
employee_id,
case when employee_id%2=1 and SUBSTRING(name,1,1)!='M' then salary
else 0 end bonus
from Employees
order by employee_id
/* Write your PL/SQL query statement below */
select
employee_id "employee_id",
case when mod(employee_id,2)=1 and substr(name,1,1)!='M' then salary
else 0 end "bonus"
from Employees
order by 1
?627. 变更性别
? Salary 表：
+-------------+----------+
| Column Name | Type |
+-------------+----------+
| id | int |
| name | varchar |
| sex | ENUM |
| salary | int |
+-------------+----------+
id 是这个表的主键。
sex 这一列的值是 ENUM 类型，只能从 ('m', 'f') 中取。
本表包含公司雇员的信息。
? 需求
请你编写一个 SQL 查询来交换所有的 'f' 和 'm' （即，将所有 'f' 变为 'm' ，反之亦然），仅使用 单个 update 语句 ，且不产生中间临时表。
注意，你必须仅使用一条 update 语句，且 不能 使用 select 语句。
查询结果如下例所示。
示例 1:
输入：
Salary 表：
+----+------+-----+--------+
| 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 |
+----+------+-----+--------+
解释：
(1, A) 和 (3, C) 从 'm' 变为 'f' 。
(2, B) 和 (4, D) 从 'f' 变为 'm' 。
?? 答案
# Write your MySQL query statement below
update Salary
set sex=
case sex
when 'm' then 'f'
when 'f' then 'm'
end
/* Write your T-SQL query statement below */
update Salary
set sex=
case sex
when 'm' then 'f'
when 'f' then 'm'
end
/* Write your PL/SQL query statement below */
update Salary set sex=decode(sex,'m','f','f','m')
?196. 删除重复的电子邮箱
? 表: Person
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| email | varchar |
+-------------+---------+
id是该表的主键列。
该表的每一行包含一封电子邮件。电子邮件将不包含大写字母。
? 需求
编写一个 SQL 删除语句来 删除 所有重复的电子邮件，只保留一个id最小的唯一电子邮件。
以任意顺序 返回结果表。 （注意： 仅需要写删除语句，将自动对剩余结果进行查询）
查询结果格式如下所示。
示例 1:
输入:
Person 表:
+----+------------------+
| id | email |
+----+------------------+
| 1 | [email protected] |
| 2 | [email protected] |
| 3 | [email protected] |
+----+------------------+
输出:
+----+------------------+
| id | email |
+----+------------------+
| 1 | [email protected] |
| 2 | [email protected] |
+----+------------------+
解释: [email protected]重复两次。我们保留最小的Id = 1。
?? 答案
# Please write a DELETE statement and DO NOT write a SELECT statement.
# Write your MySQL query statement below
DELETE p1 FROM Person p1, Person p2
WHERE p1.email = p2.email AND p1.id > p2.id
/*
Please write a DELETE statement and DO NOT write a SELECT statement.
Write your T-SQL query statement below
*/
DELETE p1 FROM Person p1, Person p2
WHERE p1.email = p2.email AND p1.id > p2.id
/*
Please write a DELETE statement and DO NOT write a SELECT statement.
Write your PL/SQL query statement below
*/
delete from Person where id in (select e1.id from Person e1,Person e2
where e1.email = e2.email and e1.id>e2.id)
delete from Person e1 where exists (select 1 from Person e2
where e1.email = e2.email and e1.id>e2.id)
第3天 字符串处理函数/正则
?1667. 修复表中的名字
? 表： Users
+----------------+---------+
| Column Name | Type |
+----------------+---------+
| user_id | int |
| name | varchar |
+----------------+---------+
user_id 是该表的主键。
该表包含用户的 ID 和名字。名字仅由小写和大写字符组成。
? 需求
编写一个 SQL 查询来修复名字，使得只有第一个字符是大写的，其余都是小写的。
返回按 user_id 排序的结果表。
查询结果格式示例如下。
示例 1：
输入：
Users table:
+---------+-------+
| user_id | name |
+---------+-------+
| 1 | aLice |
| 2 | bOB |
+---------+-------+
输出：
+---------+-------+
| user_id | name |
+---------+-------+
| 1 | Alice |
| 2 | Bob |
+---------+-------+
?? 答案
# Write your MySQL query statement below
select user_id,
concat(upper(left(name,1)),lower(substr(name,2))) name
from Users
order by user_id
/* Write your T-SQL query statement below */
select user_id,
UPPER(substring(name,1,1))+LOWER(substring(name,2,len(name)-1)) name
from Users
order by user_id
/* Write your PL/SQL query statement below */
select user_id "user_id",
initcap(name) "name"
from Users
order by 1
?1484. 按日期分组销售产品
?表 Activities：
+-------------+---------+
| 列名 | 类型 |
+-------------+---------+
| sell_date | date |
| product | varchar |
+-------------+---------+
此表没有主键，它可能包含重复项。
此表的每一行都包含产品名称和在市场上销售的日期。
? 需求
编写一个 SQL 查询来查找每个日期、销售的不同产品的数量及其名称。
每个日期的销售产品名称应按词典序排列。
返回按 sell_date 排序的结果表。
查询结果格式如下例所示。
示例 1:
输入：
Activities 表：
+------------+-------------+
| sell_date | product |
+------------+-------------+
| 2020-05-30 | Headphone |
| 2020-06-01 | Pencil |
| 2020-06-02 | Mask |
| 2020-05-30 | Basketball |
| 2020-06-01 | Bible |
| 2020-06-02 | Mask |
| 2020-05-30 | T-Shirt |
+------------+-------------+
输出：
+------------+----------+------------------------------+
| sell_date | num_sold | products |
+------------+----------+------------------------------+
| 2020-05-30 | 3 | Basketball,Headphone,T-shirt |
| 2020-06-01 | 2 | Bible,Pencil |
| 2020-06-02 | 1 | Mask |
+------------+----------+------------------------------+
解释：
对于2020-05-30，出售的物品是 (Headphone, Basketball, T-shirt)，按词典序排列，并用逗号 ',' 分隔。
对于2020-06-01，出售的物品是 (Pencil, Bible)，按词典序排列，并用逗号分隔。
对于2020-06-02，出售的物品是 (Mask)，只需返回该物品名。
?? 答案
# Write your MySQL query statement below
select
sell_date,
count(distinct product) as num_sold,
group_concat(distinct product) as products
from Activities
group by sell_date
/* Write your T-SQL query statement below */
SELECT STUFF((SELECT ','+product FROM Activities for xml path('')),1,1,'')
/* Write your T-SQL query statement below */
select
sell_date,
count(distinct product) as num_sold,
stuff((select distinct ','+product from Activities a
where a.sell_date=b.sell_date for xml path('')),1,1,'') AS products
from Activities b group by sell_date
--行转列
create table test (id int,mc varchar(2000))
insert into test
select 1,1111 from dual
union all
select 1,2222 from dual
--drop table test
select id, count(distinct mc) mc,
ltrim(max(sys_connect_by_path(mc,',')),',') row2col
from (select id,mc,
id+(row_number() over(order by id)) node_id,
row_number() over(partition by id order by id) rn
from test)
start with rn = 1
connect by node_id-1 = prior node_id
group by id
order by id;
elect name,coures,to_char(wmsys.wm_concat(xxx.score)) c
from
(select '小明' name,'语文' coures,90 score from dual
union all
select '小明' name,'语文' coures,91 score from dual
union all
select '小明' name,'数学' coures,90 score from dual
union all
select '小明' name,'数学' coures,91 score from dual) xxx
group by xxx.name,coures
--列转行
select
REGEXP_SUBSTR(a.rolecode ,'[^,]+',1,l)
rolecode
from (
select 'a,aa,aaa' rolecode from dual
) a,
(SELECT LEVEL l FROM DUAL CONNECT BY LEVEL<=100) b
WHERE l <=LENGTH(a.rolecode) - LENGTH(REPLACE(rolecode,','))+1
with a as (select 'ABC,AA,AD,ABD,JI,CC,ALSKD,ALDKDJ' id from dual)
select regexp_substr(id,'[^,]+',1,rownum) id from a
connect by rownum <= length(regexp_replace(id,'[^,]+'))
--分组加排序，数据量大时结果会比较慢
SELECT listagg(t.ename,',') WITHIN GROUP(ORDER BY t.sal) FROM scott.emp t;
查询结果为CLOB
SELECT wm_concat(t.ename) FROM scott.emp t ORDER BY t.sal;
/* Write your PL/SQL query statement below */
select
sell_date "sell_date",
count(distinct product) as "num_sold",
wm_concat(distinct product) as "products"
from Activities
group by sell_date
?1527. 患某种疾病的患者
? 患者信息表： Patients
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| patient_id | int |
| patient_name | varchar |
| conditions | varchar |
+--------------+---------+
patient_id （患者 ID）是该表的主键。
'conditions' （疾病）包含 0 个或以上的疾病代码，以空格分隔。
这个表包含医院中患者的信息。
写一条 SQL 语句，查询患有 I 类糖尿病的患者 ID （patient_id）、患者姓名（patient_name）以及其患有的所有疾病代码（conditions）。
I 类糖尿病的代码总是包含前缀 DIAB1 。
按 任意顺序 返回结果表。
查询结果格式如下示例所示。
? 需求
示例 1:
输入：
Patients表：
+------------+--------------+--------------+
| patient_id | patient_name | conditions |
+------------+--------------+--------------+
| 1 | Daniel | YFEV COUGH |
| 2 | Alice | |
| 3 | Bob | DIAB100 MYOP |
| 4 | George | ACNE DIAB100 |
| 5 | Alain | DIAB201 |
+------------+--------------+--------------+
输出：
+------------+--------------+--------------+
| patient_id | patient_name | conditions |
+------------+--------------+--------------+
| 3 | Bob | DIAB100 MYOP |
| 4 | George | ACNE DIAB100 |
+------------+--------------+--------------+
解释：Bob 和 George 都患有代码以 DIAB1 开头的疾病。
?? 答案
# Write your MySQL query statement below
select * FROM Patients
WHERE conditions REGEXP '^DIAB1|\sDIAB1'
注意：正则表达式，|表示包含，s表示空格，\表示转义
/* Write your PL/SQL query statement below */
select
patient_id "patient_id",
patient_name "patient_name",
conditions "conditions"
from Patients where regexp_like(conditions,'^DIAB1| s*DIAB1')
以下为Oracle的正则表达式
值 描述
^ 匹配一个字符串的开始。如果与“m” 的match_parameter一起使用，则匹配表达式中任何位置的行的开头。
$ 匹配字符串的结尾。如果与“m” 的match_parameter一起使用，则匹配表达式中任何位置的行的末尾。
* 匹配零个或多个。
+ 匹配一个或多个出现。
？ 匹配零次或一次出现。
。 匹配任何字符，除了空。
| 用“OR”来指定多个选项。
[] 用于指定一个匹配列表，您尝试匹配列表中的任何一个字符。
[^] 用于指定一个不匹配的列表，您尝试匹配除列表中的字符以外的任何字符。
（） 用于将表达式分组为一个子表达式。
{M} 匹配m次。
{M，} 至少匹配m次。
{M，N} 至少匹配m次，但不多于n次。
n n是1到9之间的数字。在遇到 n之前匹配在（）内找到的第n个子表达式。
[..] 匹配一个可以多于一个字符的整理元素。
[：] 匹配字符类。
[==] 匹配等价类。
d 匹配一个数字字符。
D 匹配一个非数字字符。
w 匹配包括下划线的任何单词字符。
W 匹配任何非单词字符。
s 匹配任何空白字符，包括空格，制表符，换页符等等。
S 匹配任何非空白字符。
A 在换行符之前匹配字符串的开头或匹配字符串的末尾。
Z 匹配字符串的末尾。
*？ 匹配前面的模式零次或多次发生。
+？ 匹配前面的模式一个或多个事件。
?? 匹配前面的模式零次或一次出现。
{N}？ 匹配前面的模式n次。
{N，}？ 匹配前面的模式至少n次。
{N，M}？ 匹配前面的模式至少n次，但不超过m次。
第4天 组合查询 & 指定选取
?1965. 丢失信息的雇员
? 表: Employees
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| employee_id | int |
| name | varchar |
+-------------+---------+
employee_id 是这个表的主键。
每一行表示雇员的id 和他的姓名。
表: Salaries
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| employee_id | int |
| salary | int |
+-------------+---------+
employee_id is 这个表的主键。
每一行表示雇员的id 和他的薪水。
? 需求
写出一个查询语句，找到所有丢失信息的雇员id。当满足下面一个条件时，就被认为是雇员的信息丢失：
雇员的 姓名 丢失了，或者
雇员的 薪水信息 丢失了
返回这些雇员的id employee_id ， 从小到大排序 。
查询结果格式如下面的例子所示。
示例 1：
输入：
Employees table:
+-------------+----------+
| employee_id | name |
+-------------+----------+
| 2 | Crew |
| 4 | Haven |
| 5 | Kristian |
+-------------+----------+
Salaries table:
+-------------+--------+
| employee_id | salary |
+-------------+--------+
| 5 | 76071 |
| 1 | 22517 |
| 4 | 63539 |
+-------------+--------+
输出：
+-------------+
| employee_id |
+-------------+
| 1 |
| 2 |
+-------------+
解释：
雇员1，2，4，5 都工作在这个公司。
1号雇员的姓名丢失了。
2号雇员的薪水信息丢失了。
?? 答案
# Write your MySQL query statement below
select employee_id
from
(select employee_id from Employees
union all
select employee_id from Salaries ) as temp
group by employee_id
having count(*) = 1
order by employee_id
/* Write your T-SQL query statement below */
select employee_id
from
(select employee_id from Employees
union all
select employee_id from Salaries ) as temp
group by employee_id
having count(*) = 1
order by employee_id
/* Write your PL/SQL query statement below */
select employee_id "employee_id" from (
select employee_id
from Employees a
where not exists (select 1 from Salaries where employee_id= a.employee_id)
union all
select employee_id
from Salaries b
where not exists (select 1 from Employees where employee_id= b.employee_id)
order by employee_id
)
?1795. 每个产品在不同商店的价格
? 表：Products
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| product_id | int |
| store1 | int |
| store2 | int |
| store3 | int |
+-------------+---------+
这张表的主键是product_id（产品Id）。
每行存储了这一产品在不同商店store1, store2, store3的价格。
如果这一产品在商店里没有出售，则值将为null。
? 需求
请你重构 Products 表，查询每个产品在不同商店的价格，使得输出的格式变为(product_id, store, price) 。
如果这一产品在商店里没有出售，则不输出这一行。
输出结果表中的 顺序不作要求 。
查询输出格式请参考下面示例。
示例 1：
输入：
Products table:
+------------+--------+--------+--------+
| product_id | store1 | store2 | store3 |
+------------+--------+--------+--------+
| 0 | 95 | 100 | 105 |
| 1 | 70 | null | 80 |
+------------+--------+--------+--------+
输出：
+------------+--------+-------+
| product_id | store | price |
+------------+--------+-------+
| 0 | store1 | 95 |
| 0 | store2 | 100 |
| 0 | store3 | 105 |
| 1 | store1 | 70 |
| 1 | store3 | 80 |
+------------+--------+-------+
解释：
产品0在store1，store2,store3的价格分别为95,100,105。
产品1在store1，store3的价格分别为70,80。在store2无法买到。
?? 答案
# Write your MySQL query statement below
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
/* Write your T-SQL query statement below */
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
/* Write your PL/SQL query statement below */
select
product_id "product_id",
lower(store) as "store",
price "price"
from Products
unpivot
( price for store in(store1,store2,store3))
?608. 树节点
? 给定一个表 tree，id 是树节点的编号， p_id 是它父节点的 id 。
+----+------+
| id | p_id |
+----+------+
| 1 | null |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 2 |
+----+------+
树中每个节点属于以下三种类型之一：
叶子：如果这个节点没有任何孩子节点。
根：如果这个节点是整棵树的根，即没有父节点。
内部节点：如果这个节点既不是叶子节点也不是根节点。
? 需求
写一个查询语句，输出所有节点的编号和节点的类型，并将结果按照节点编号排序。上面样例的结果为：
+----+------+
| id | Type |
+----+------+
| 1 | Root |
| 2 | Inner|
| 3 | Leaf |
| 4 | Leaf |
| 5 | Leaf |
+----+------+
解释
节点 '1' 是根节点，因为它的父节点是 NULL ，同时它有孩子节点 '2' 和 '3' 。
节点 '2' 是内部节点，因为它有父节点 '1' ，也有孩子节点 '4' 和 '5' 。
节点 '3', '4' 和 '5' 都是叶子节点，因为它们都有父节点同时没有孩子节点。
样例中树的形态如下：
1
/
2 3
/
4 5
注意
如果树中只有一个节点，你只需要输出它的根属性。
?? 答案
# Write your MySQL query statement below
select id,case when p_id is null then 'Root'
when id in (select p_id from tree) then 'Inner'
else 'Leaf' end as Type
from tree
/* Write your T-SQL query statement below */
select id,case when p_id is null then 'Root'
when id in (select p_id from tree) then 'Inner'
else 'Leaf' end as Type
from tree
/* Write your PL/SQL query statement below */
select id "id",case when p_id is null then 'Root'
when id in (select p_id from tree) then 'Inner'
else 'Leaf' end as "Type"
from tree
?176. 第二高的薪水
? Employee 表：
+-------------+------+
| Column Name | Type |
+-------------+------+
| id | int |
| salary | int |
+-------------+------+
id 是这个表的主键。
表的每一行包含员工的工资信息。
? 需求
编写一个 SQL 查询，获取并返回 Employee 表中第二高的薪水 。
如果不存在第二高的薪水，查询应该返回 null 。
查询结果如下例所示。
示例 1：
输入：
Employee 表：
+----+--------+
| id | salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
输出：
+---------------------+
| SecondHighestSalary |
+---------------------+
| 200 |
+---------------------+
示例 2：
输入：
Employee 表：
+----+--------+
| id | salary |
+----+--------+
| 1 | 100 |
+----+--------+
输出：
+---------------------+
| SecondHighestSalary |
+---------------------+
| null |
+---------------------+
?? 答案
# Write your MySQL query statement below
SELECT
IFNULL(
(SELECT DISTINCT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1),
NULL) AS SecondHighestSalary
/* Write your T-SQL query statement below */
SELECT MAX(Salary) SecondHighestSalary FROM Employee
Where Salary <
(SELECT MAX(Salary) FROM Employee);
/* Write your PL/SQL query statement below */
SELECT MAX(Salary) "SecondHighestSalary" FROM Employee E1
WHERE 1 =
(SELECT COUNT(DISTINCT(E2.Salary)) FROM Employee E2
WHERE E2.Salary > E1.Salary);
第5天合并
?175. 组合两个表
? 表: Person
+-------------+---------+
| 列名 | 类型 |
+-------------+---------+
| PersonId | int |
| FirstName | varchar |
| LastName | varchar |
+-------------+---------+
personId 是该表的主键列。
该表包含一些人的 ID 和他们的姓和名的信息。
表: Address
+-------------+---------+
| 列名 | 类型 |
+-------------+---------+
| AddressId | int |
| PersonId | int |
| City | varchar |
| State | varchar |
+-------------+---------+
addressId 是该表的主键列。
该表的每一行都包含一个 ID = PersonId 的人的城市和州的信息。
? 需求
编写一个SQL查询来报告 Person 表中每个人的姓、名、城市和州。如果 personId 的地址不在 Address 表中，则报告为空 null 。
以 任意顺序 返回结果表。
查询结果格式如下所示。
示例 1:
输入:
Person表:
+----------+----------+-----------+
| personId | lastName | firstName |
+----------+----------+-----------+
| 1 | Wang | Allen |
| 2 | Alice | Bob |
+----------+----------+-----------+
Address表:
+-----------+----------+---------------+------------+
| addressId | personId | city | state |
+-----------+----------+---------------+------------+
| 1 | 2 | New York City | New York |
| 2 | 3 | Leetcode | California |
+-----------+----------+---------------+------------+
输出:
+-----------+----------+---------------+----------+
| firstName | lastName | city | state |
+-----------+----------+---------------+----------+
| Allen | Wang | Null | Null |
| Bob | Alice | New York City | New York |
+-----------+----------+---------------+----------+
解释:
地址表中没有 personId = 1 的地址，所以它们的城市和州返回 null。
addressId = 1 包含了 personId = 2 的地址信息。
?? 答案
# Write your MySQL query statement below
select
a.firstName,
a.lastName,
b.city,
b.state
from Person a left join Address b
on a.PersonId = b.PersonId
/* Write your T-SQL query statement below */
select
a.firstName,
a.lastName,
b.city,
b.state
from Person a left join Address b
on a.PersonId = b.PersonId
/* Write your PL/SQL query statement below */
select
a.firstName "firstName",
a.lastName "lastName",
b.city "city",
b.state "state"
from Person a,Address b
where a.PersonId = b.PersonId(+)
order by 1
?1581. 进店却未进行过交易的顾客
? 表：Visits
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| visit_id | int |
| customer_id | int |
+-------------+---------+
visit_id 是该表的主键。
该表包含有关光临过购物中心的顾客的信息。
表：Transactions
+----------------+---------+
| Column Name | Type |
+----------------+---------+
| transaction_id | int |
| visit_id | int |
| amount | int |
+----------------+---------+
transaction_id 是此表的主键。
此表包含 visit_id 期间进行的交易的信息。
? 需求
有一些顾客可能光顾了购物中心但没有进行交易。请你编写一个 SQL 查询，来查找这些顾客的 ID ，以及他们只光顾不交易的次数。
返回以 任何顺序 排序的结果表。
查询结果格式如下例所示。
示例 1：
输入:
Visits
+----------+-------------+
| visit_id | customer_id |
+----------+-------------+
| 1 | 23 |
| 2 | 9 |
| 4 | 30 |
| 5 | 54 |
| 6 | 96 |
| 7 | 54 |
| 8 | 54 |
+----------+-------------+
Transactions
+----------------+----------+--------+
| transaction_id | visit_id | amount |
+----------------+----------+--------+
| 2 | 5 | 310 |
| 3 | 5 | 300 |
| 9 | 5 | 200 |
| 12 | 1 | 910 |
| 13 | 2 | 970 |
+----------------+----------+--------+
输出:
+-------------+----------------+
| customer_id | count_no_trans |
+-------------+----------------+
| 54 | 2 |
| 30 | 1 |
| 96 | 1 |
+-------------+----------------+
解释:
ID = 23 的顾客曾经逛过一次购物中心，并在 ID = 12 的访问期间进行了一笔交易。
ID = 9 的顾客曾经逛过一次购物中心，并在 ID = 13 的访问期间进行了一笔交易。
ID = 30 的顾客曾经去过购物中心，并且没有进行任何交易。
ID = 54 的顾客三度造访了购物中心。在 2 次访问中，他们没有进行任何交易，在 1 次访问中，他们进行了 3 次交易。
ID = 96 的顾客曾经去过购物中心，并且没有进行任何交易。
如我们所见，ID 为 30 和 96 的顾客一次没有进行任何交易就去了购物中心。顾客 54 也两次访问了购物中心并且没有进行任何交易。
?? 答案
# Write your MySQL query statement below
select
customer_id "customer_id",
count(*) "count_no_trans"
from Visits a
where not exists (select * from Transactions b where a.visit_id = b.visit_id )
group by customer_id
order by 2 desc
/* Write your T-SQL query statement below */
select
customer_id,
count(*) count_no_trans
from Visits a
where not exists (select * from Transactions b where a.visit_id = b.visit_id )
group by customer_id
order by count_no_trans desc
/* Write your PL/SQL query statement below */
select
customer_id "customer_id",
count(*) "count_no_trans"
from Visits a
where not exists (select * from Transactions b where a.visit_id = b.visit_id )
group by customer_id
order by 2 desc
?1148.文章浏览I
? Views 表：
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| article_id | int |
| author_id | int |
| viewer_id | int |
| view_date | date |
+---------------+---------+
此表无主键，因此可能会存在重复行。
此表的每一行都表示某人在某天浏览了某位作者的某篇文章。
请注意，同一人的 author_id 和 viewer_id 是相同的。
? 需求
请编写一条 SQL 查询以找出所有浏览过自己文章的作者，结果按照 id 升序排列。
查询结果的格式如下所示：
Views 表：
+------------+-----------+-----------+------------+
| article_id | author_id | viewer_id | view_date |
+------------+-----------+-----------+------------+
| 1 | 3 | 5 | 2019-08-01 |
| 1 | 3 | 6 | 2019-08-02 |
| 2 | 7 | 7 | 2019-08-01 |
| 2 | 7 | 6 | 2019-08-02 |
| 4 | 7 | 1 | 2019-07-22 |
| 3 | 4 | 4 | 2019-07-21 |
| 3 | 4 | 4 | 2019-07-21 |
+------------+-----------+-----------+------------+
结果表：
+------+
| id |
+------+
| 4 |
| 7 |
+------+
?? 答案
# Write your MySQL query statement below
select distinct author_id id
from Views
where author_id = viewer_id
order by id
/* Write your T-SQL query statement below */
select distinct author_id id
from Views
where author_id = viewer_id
order by id
/* Write your PL/SQL query statement below */
select distinct author_id "id"
from Views
where author_id = viewer_id
order by 1
第6天 合并
?197. 上升的温度
表： 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）
?? 答案
# Write your MySQL query statement below
select a.Id from Weather as a
join Weather as b
on a.Temperature > b.Temperature
and dateDiff(a.RecordDate,b.RecordDate) = 1
/* Write your T-SQL query statement below */
SELECT
w2.id AS 'Id'
FROM
Weather w1, Weather w2
WHERE
DATEDIFF(day, w1.RecordDate, w2.RecordDate) = 1
AND w1.Temperature < w2.Temperature;
/* Write your PL/SQL query statement below */
select id "id" from (
select id, Temperature,
lag(Temperature) over(order by recordDate) dk
from Weather
) where Temperature>dk
?607. 销售员
? 表: SalesPerson
+-----------------+---------+
| Column Name | Type |
+-----------------+---------+
| sales_id | int |
| name | varchar |
| salary | int |
| commission_rate | int |
| hire_date | date |
+-----------------+---------+
sales_id 是该表的主键列。
该表的每一行都显示了销售人员的姓名和 ID ，以及他们的工资、佣金率和雇佣日期。
表: Company
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| com_id | int |
| name | varchar |
| city | varchar |
+-------------+---------+
com_id 是该表的主键列。
该表的每一行都表示公司的名称和 ID ，以及公司所在的城市。
表: Orders
+-------------+------+
| Column Name | Type |
+-------------+------+
| order_id | int |
| order_date | date |
| com_id | int |
| sales_id | int |
| amount | int |
+-------------+------+
order_id 是该表的主键列。
com_id 是 Company 表中 com_id 的外键。
sales_id 是来自销售员表 sales_id 的外键。
该表的每一行包含一个订单的信息。这包括公司的 ID 、销售人员的 ID 、订单日期和支付的金额。
? 需求
编写一个SQL查询，报告没有任何与名为 “RED” 的公司相关的订单的所有销售人员的姓名。
以任意顺序 返回结果表。
查询结果格式如下所示。
示例：
输入：
SalesPerson 表:
+----------+------+--------+-----------------+------------+
| sales_id | name | salary | commission_rate | hire_date |
+----------+------+--------+-----------------+------------+
| 1 | John | 100000 | 6 | 4/1/2006 |
| 2 | Amy | 12000 | 5 | 5/1/2010 |
| 3 | Mark | 65000 | 12 | 12/25/2008 |
| 4 | Pam | 25000 | 25 | 1/1/2005 |
| 5 | Alex | 5000 | 10 | 2/3/2007 |
+----------+------+--------+-----------------+------------+
Company 表:
+--------+--------+----------+
| com_id | name | city |
+--------+--------+----------+
| 1 | RED | Boston |
| 2 | ORANGE | New York |
| 3 | YELLOW | Boston |
| 4 | GREEN | Austin |
+--------+--------+----------+
Orders 表:
+----------+------------+--------+----------+--------+
| order_id | order_date | com_id | sales_id | amount |
+----------+------------+--------+----------+--------+
| 1 | 1/1/2014 | 3 | 4 | 10000 |
| 2 | 2/1/2014 | 4 | 5 | 5000 |
| 3 | 3/1/2014 | 1 | 1 | 50000 |
| 4 | 4/1/2014 | 1 | 4 | 25000 |
+----------+------------+--------+----------+--------+
输出：
+------+
| name |
+------+
| Amy |
| Mark |
| Alex |
+------+
解释：
根据表 orders 中的订单 '3' 和 '4' ，容易看出只有 'John' 和 'Pam' 两个销售员曾经向公司 'RED' 销售过。
所以我们需要输出表 salesperson 中所有其他人的名字。
?? 答案
# Write your MySQL query statement below
select a.name from SalesPerson a where not exists (
select n.name from Orders m,Company n
where m.com_id = n.com_id
and n.name = 'RED'
and m.sales_id = a.sales_id)
/* Write your T-SQL query statement below */
select a.name from SalesPerson a where not exists (
select n.name from Orders m,Company n
where m.com_id = n.com_id
and n.name = 'RED'
and m.sales_id = a.sales_id)
/* Write your T-SQL query statement below */
select a.name from SalesPerson a where sales_id not in (
select m.sales_id from Orders m,Company n
where m.com_id = n.com_id
and n.name = 'RED'
and m.sales_id = a.sales_id)
第7天 统计去重
?1141.查询近30天活跃用户数
? 活动记录表：Activity
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| user_id | int |
| session_id | int |
| activity_date | date |
| activity_type | enum |
+---------------+---------+
该表是用户在社交网站的活动记录。
该表没有主键，可能包含重复数据。
activity_type 字段为以下四种值 ('open_session', 'end_session', 'scroll_down', 'send_message')。
每个 session_id 只属于一个用户。
? 需求
请写SQL查询出截至 2019-07-27（包含2019-07-27），近 30 天的每日活跃用户数（当天只要有一条活动记录，即为活跃用户）。
以 任意顺序 返回结果表。
查询结果示例如下。
示例 1:
输入：
Activity table:
+---------+------------+---------------+---------------+
| user_id | session_id | activity_date | activity_type |
+---------+------------+---------------+---------------+
| 1 | 1 | 2019-07-20 | open_session |
| 1 | 1 | 2019-07-20 | scroll_down |
| 1 | 1 | 2019-07-20 | end_session |
| 2 | 4 | 2019-07-20 | open_session |
| 2 | 4 | 2019-07-21 | send_message |
| 2 | 4 | 2019-07-21 | end_session |
| 3 | 2 | 2019-07-21 | open_session |
| 3 | 2 | 2019-07-21 | send_message |
| 3 | 2 | 2019-07-21 | end_session |
| 4 | 3 | 2019-06-25 | open_session |
| 4 | 3 | 2019-06-25 | end_session |
+---------+------------+---------------+---------------+
输出：
+------------+--------------+
| day | active_users |
+------------+--------------+
| 2019-07-20 | 2 |
| 2019-07-21 | 2 |
+------------+--------------+
解释：注意非活跃用户的记录不需要展示。
?? 答案
# Write your MySQL query statement below
select activity_date day, count(distinct user_id) active_users
from activity
where activity_date > date_sub('2019-07-27', interval 30 day)
and activity_date <= '2019-07-27'
group by activity_date
/* Write your T-SQL query statement below */
select
activity_date day,
count(distinct user_id ) active_users
from Activity
where activity_date > CONVERT(varchar(100), dateadd(dd,-30,'2019-07-27'), 23)
and activity_date<='2019-07-27'
group by activity_date
/* Write your PL/SQL query statement below */
select
to_char(activity_date,'yyyy-mm-dd') "day",
count(distinct user_id ) "active_users"
from Activity
where activity_date > to_date('20190727','yyyymmdd')-30
and to_char(activity_date,'yyyy-mm-dd') < '2019-07-27'
group by to_char(activity_date,'yyyy-mm-dd')
?1693.每天的领导和合伙人
? 表：DailySales
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| date_id | date |
| make_name | varchar |
| lead_id | int |
| partner_id | int |
+-------------+---------+
该表没有主键。
该表包含日期、产品的名称，以及售给的领导和合伙人的编号。
名称只包含小写英文字母。
? 需求
写一条 SQL 语句，使得对于每一个 date_id 和 make_name，返回不同的 lead_id 以及不同的 partner_id 的数量。
按 任意顺序 返回结果表。
查询结果格式如下示例所示。
示例 1:
输入：
DailySales 表：
+-----------+-----------+---------+------------+
| date_id | make_name | lead_id | partner_id |
+-----------+-----------+---------+------------+
| 2020-12-8 | toyota | 0 | 1 |
| 2020-12-8 | toyota | 1 | 0 |
| 2020-12-8 | toyota | 1 | 2 |
| 2020-12-7 | toyota | 0 | 2 |
| 2020-12-7 | toyota | 0 | 1 |
| 2020-12-8 | honda | 1 | 2 |
| 2020-12-8 | honda | 2 | 1 |
| 2020-12-7 | honda | 0 | 1 |
| 2020-12-7 | honda | 1 | 2 |
| 2020-12-7 | honda | 2 | 1 |
+-----------+-----------+---------+------------+
输出：
+-----------+-----------+--------------+-----------------+
| date_id | make_name | unique_leads | unique_partners |
+-----------+-----------+--------------+-----------------+
| 2020-12-8 | toyota | 2 | 3 |
| 2020-12-7 | toyota | 1 | 2 |
| 2020-12-8 | honda | 2 | 2 |
| 2020-12-7 | honda | 3 | 2 |
+-----------+-----------+--------------+-----------------+
解释：
在 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]。
?? 答案
# Write your MySQL query statement below
select date_id,make_name,
count(distinct lead_id) unique_leads,
count(distinct partner_id) unique_partners
from DailySales
group by date_id,make_name
/* Write your PL/SQL query statement below */
select
to_char(date_id,'yyyy-mm-dd') "date_id",
make_name "make_name",
count(distinct lead_id) "unique_leads",
count(distinct partner_id) "unique_partners"
from DailySales
group by to_char(date_id,'yyyy-mm-dd'),make_name
?1729.求关注者的数量
? 表： Followers
+-------------+------+
| Column Name | Type |
+-------------+------+
| user_id | int |
| follower_id | int |
+-------------+------+
(user_id, follower_id) 是这个表的主键。
该表包含一个关注关系中关注者和用户的编号，其中关注者关注用户。
? 需求
写出 SQL 语句，对于每一个用户，返回该用户的关注者数量。
按 user_id 的顺序返回结果表。
查询结果的格式如下示例所示。
示例 1：
输入：
Followers 表：
+---------+-------------+
| user_id | follower_id |
+---------+-------------+
| 0 | 1 |
| 1 | 0 |
| 2 | 0 |
| 2 | 1 |
+---------+-------------+
输出：
+---------+----------------+
| user_id | followers_count|
+---------+----------------+
| 0 | 1 |
| 1 | 1 |
| 2 | 2 |
+---------+----------------+
解释：
0 的关注者有 {1}
1 的关注者有 {0}
2 的关注者有 {0,1}
?? 答案
# Write your MySQL query statement below
SELECT user_id, COUNT(DISTINCT follower_id) followers_count
FROM Followers
GROUP BY user_id
ORDER BY user_id;
/* Write your T-SQL query statement below */
SELECT user_id, COUNT(DISTINCT follower_id) followers_count
FROM Followers
GROUP BY user_id
ORDER BY user_id;
/* Write your PL/SQL query statement below */
SELECT user_id "user_id",
COUNT(DISTINCT follower_id) "followers_count"
FROM Followers
GROUP BY user_id
ORDER BY user_id;
第8天 计算函数
?586. 订单最多的客户
? 表: Orders
+-----------------+----------+
| Column Name | Type |
+-----------------+----------+
| order_number | int |
| customer_number | int |
+-----------------+----------+
Order_number是该表的主键。
此表包含关于订单ID和客户ID的信息。
? 需求
编写一个SQL查询，为下了 最多订单 的客户查找 customer_number 。测试用例生成后， 恰好有一个客户 比任何其他客户下了更多的订单。
查询结果格式如下所示。
示例 1:
输入:
Orders 表:
+--------------+-----------------+
| order_number | customer_number |
+--------------+-----------------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 3 |
+--------------+-----------------+
输出:
+-----------------+
| customer_number |
+-----------------+
| 3 |
+-----------------+
解释:
customer_number 为 '3' 的顾客有两个订单，比顾客 '1' 或者 '2' 都要多，因为他们只有一个订单。
所以结果是该顾客的 customer_number ，也就是 3 。
?? 答案
# Write your MySQL query statement below
select customer_number from Orders
group by customer_number
order by count(*) desc limit 1
/* Write your T-SQL query statement below */
select top 1 customer_number from Orders
group by customer_number
order by count(*) desc
/* Write your PL/SQL query statement below */
select * from (
select customer_number "customer_number" from Orders
group by customer_number
order by count(*) desc
) where rownum =1
?511. 游戏玩法分析 I
? 活动表 Activity：
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| player_id | int |
| device_id | int |
| event_date | date |
| games_played | int |
+--------------+---------+
表的主键是 (player_id, event_date)。
这张表展示了一些游戏玩家在游戏平台上的行为活动。
每行数据记录了一名玩家在退出平台之前，当天使用同一台设备登录平台后打开的游戏的数目（可能是 0 个）。
? 需求
写一条 SQL 查询语句获取每位玩家 第一次登陆平台的日期。
查询结果的格式如下所示：
Activity 表：
+-----------+-----------+------------+--------------+
| player_id | device_id | event_date | games_played |
+-----------+-----------+------------+--------------+
| 1 | 2 | 2016-03-01 | 5 |
| 1 | 2 | 2016-05-02 | 6 |
| 2 | 3 | 2017-06-25 | 1 |
| 3 | 1 | 2016-03-02 | 0 |
| 3 | 4 | 2018-07-03 | 5 |
+-----------+-----------+------------+--------------+
Result 表：
+-----------+-------------+
| player_id | first_login |
+-----------+-------------+
| 1 | 2016-03-01 |
| 2 | 2017-06-25 |
| 3 | 2016-03-02 |
+-----------+-------------+
?? 答案
# Write your MySQL query statement below
select player_id,min(event_date) first_login from Activity
group by player_id
/* Write your T-SQL query statement below */
select player_id,min(event_date) first_login from Activity
group by player_id
/* Write your PL/SQL query statement below */
select
player_id "player_id",
to_char(event_date,'yyyy-mm-dd') "first_login"
from (
select
player_id,
event_date,
rank() over(partition by player_id order by event_date) dk
from Activity
) where dk =1
order by 1
?1890.2020年最后一次登录
? 表: Logins
+----------------+----------+
| 列名 | 类型 |
+----------------+----------+
| user_id | int |
| time_stamp | datetime |
+----------------+----------+
(user_id, time_stamp) 是这个表的主键。
每一行包含的信息是user_id 这个用户的登录时间。
? 需求
编写一个 SQL 查询，该查询可以获取在 2020 年登录过的所有用户的本年度 最后一次 登录时间。结果集 不 包含 2020 年没有登录过的用户。
返回的结果集可以按 任意顺序 排列。
查询结果格式如下例。
示例 1:
输入：
Logins 表:
+---------+---------------------+
| user_id | time_stamp |
+---------+---------------------+
| 6 | 2020-06-30 15:06:07 |
| 6 | 2021-04-21 14:06:06 |
| 6 | 2019-03-07 00:18:15 |
| 8 | 2020-02-01 05:10:53 |
| 8 | 2020-12-30 00:46:50 |
| 2 | 2020-01-16 02:49:50 |
| 2 | 2019-08-25 07:59:08 |
| 14 | 2019-07-14 09:00:00 |
| 14 | 2021-01-06 11:59:59 |
+---------+---------------------+
输出：
+---------+---------------------+
| user_id | last_stamp |
+---------+---------------------+
| 6 | 2020-06-30 15:06:07 |
| 8 | 2020-12-30 00:46:50 |
| 2 | 2020-01-16 02:49:50 |
+---------+---------------------+
解释：
6号用户登录了3次，但是在2020年仅有一次，所以结果集应包含此次登录。
8号用户在2020年登录了2次，一次在2月，一次在12月，所以，结果集应该包含12月的这次登录。
2号用户登录了2次，但是在2020年仅有一次，所以结果集应包含此次登录。
14号用户在2020年没有登录，所以结果集不应包含。
?? 答案
# Write your MySQL query statement below
SELECT user_id, max(time_stamp) last_stamp
FROM Logins
WHERE year(time_stamp) = 2020
GROUP BY user_id
/* Write your T-SQL query statement below */
select user_id,max(time_stamp) as last_stamp from Logins
where time_stamp between '2020-01-01 00:00:00' and '2020-12-31 12:59:59'
group by user_id
/* Write your PL/SQL query statement below */
SELECT user_id "user_id",
max(time_stamp) "last_stamp"
FROM Logins
WHERE to_char(time_stamp, 'yyyy' ) = '2020'
GROUP BY user_id
?1741.查找每个员工花费的总时间
? 表: Employees
+-------------+------+
| Column Name | Type |
+-------------+------+
| emp_id | int |
| event_day | date |
| in_time | int |
| out_time | int |
+-------------+------+
(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。
返回结果表单的顺序无要求。
查询结果的格式如下：
Employees table:
+--------+------------+---------+----------+
| emp_id | event_day | in_time | out_time |
+--------+------------+---------+----------+
| 1 | 2020-11-28 | 4 | 32 |
| 1 | 2020-11-28 | 55 | 200 |
| 1 | 2020-12-03 | 1 | 42 |
| 2 | 2020-11-28 | 3 | 33 |
| 2 | 2020-12-09 | 47 | 74 |
+--------+------------+---------+----------+
Result table:
+------------+--------+------------+
| day | emp_id | total_time |
+------------+--------+------------+
| 2020-11-28 | 1 | 173 |
| 2020-11-28 | 2 | 30 |
| 2020-12-03 | 1 | 41 |
| 2020-12-09 | 2 | 27 |
+------------+--------+------------+
雇员 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。
?? 答案
# Write your MySQL query statement below
select
event_day day,
emp_id,
sum(out_time-in_time) total_time
from Employees
group by event_day,emp_id
/* Write your T-SQL query statement below */
select
event_day day,
emp_id,
sum(out_time-in_time) total_time
from Employees
group by event_day,emp_id
/* Write your PL/SQL query statement below */
select
to_char(event_day,'yyyy-mm-dd') "day",
emp_id "emp_id",
sum(out_time-in_time) "total_time"
from Employees
group by to_char(event_day,'yyyy-mm-dd'),emp_id
第9天 控制流
?1393.股票的资本损益
? 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美元。
?? 答案
# Write your MySQL query statement below
select
stock_name,
sum(case when operation='Sell' then price else 0 end)
-sum(case when operation='Buy' then price else 0 end)
capital_gain_loss
from Stocks
group by stock_name
/* Write your T-SQL query statement below */
select stock_name,
sum(case when operation='buy' then -price
else price end ) as 'capital_gain_loss'
from Stocks
group by stock_name
/* Write your PL/SQL query statement below */
select
stock_name "stock_name",
sum(
case
when operation = 'Sell' then price
when operation = 'Buy' then -price
end
) as "capital_gain_loss"
from Stocks
group by stock_name
?1407.排名靠前的旅行者
?表：Users
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| name | varchar |
+---------------+---------+
id 是该表单主键。
name 是用户名字。
表：Rides
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| user_id | int |
| distance | int |
+---------------+---------+
id 是该表单主键。
user_id 是本次行程的用户的 id, 而该用户此次行程距离为 distance 。
? 需求
写一段 SQL , 报告每个用户的旅行距离。
返回的结果表单，以 travelled_distance 降序排列 ，如果有两个或者更多的用户旅行了相同的距离, 那么再以 name 升序排列 。
查询结果格式如下例所示。
Users 表：
+------+-----------+
| id | name |
+------+-----------+
| 1 | Alice |
| 2 | Bob |
| 3 | Alex |
| 4 | Donald |
| 7 | Lee |
| 13 | Jonathan |
| 19 | Elvis |
+------+-----------+
Rides 表：
+------+----------+----------+
| id | user_id | distance |
+------+----------+----------+
| 1 | 1 | 120 |
| 2 | 2 | 317 |
| 3 | 3 | 222 |
| 4 | 7 | 100 |
| 5 | 13 | 312 |
| 6 | 19 | 50 |
| 7 | 7 | 120 |
| 8 | 19 | 400 |
| 9 | 7 | 230 |
+------+----------+----------+
Result 表：
+----------+--------------------+
| name | travelled_distance |
+----------+--------------------+
| Elvis | 450 |
| Lee | 450 |
| Bob | 317 |
| Jonathan | 312 |
| Alex | 222 |
| Alice | 120 |
| Donald | 0 |
+----------+--------------------+
Elvis 和 Lee 旅行了 450 英里，Elvis 是排名靠前的旅行者，因为他的名字在字母表上的排序比 Lee 更小。
Bob, Jonathan, Alex 和 Alice 只有一次行程，我们只按此次行程的全部距离对他们排序。
Donald 没有任何行程, 他的旅行距离为 0。
?? 答案
# Write your MySQL query statement below
select max(name) name,ifnull(sum(distance),0) travelled_distance
from Users u left join Rides r on r.user_id=u.id
group by r.user_id
order by travelled_distance desc,name
/* Write your T-SQL query statement below */
select max(name) name,isnull(sum(distance),0) travelled_distance
from Users u left join Rides r on r.user_id=u.id
group by r.user_id
order by travelled_distance desc,name
/* Write your PL/SQL query statement below */
select max(name) "name",
sum(nvl(distance,0)) "travelled_distance"
from Users u left join Rides r on r.user_id=u.id
group by r.user_id
order by 2 desc,1
?1158.市场分析 I
? Table: Users
+----------------+---------+
| Column Name | Type |
+----------------+---------+
| user_id | int |
| join_date | date |
| favorite_brand | varchar |
+----------------+---------+
此表主键是 user_id。
表中描述了购物网站的用户信息，用户可以在此网站上进行商品买卖。
Table: Orders
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| order_id | int |
| order_date | date |
| item_id | int |
| buyer_id | int |
| seller_id | int |
+---------------+---------+
此表主键是 order_id。
外键是 item_id 和（buyer_id，seller_id）。
Table: Items
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| item_id | int |
| item_brand | varchar |
+---------------+---------+
此表主键是 item_id。
? 需求
请写出一条SQL语句以查询每个用户的注册日期和在 2019 年作为买家的订单总数。
以 任意顺序 返回结果表。
查询结果格式如下。
示例 1:
输入：
Users 表:
+---------+------------+----------------+
| user_id | join_date | favorite_brand |
+---------+------------+----------------+
| 1 | 2018-01-01 | Lenovo |
| 2 | 2018-02-09 | Samsung |
| 3 | 2018-01-19 | LG |
| 4 | 2018-05-21 | HP |
+---------+------------+----------------+
Orders 表:
+----------+------------+---------+----------+-----------+
| order_id | order_date | item_id | buyer_id | seller_id |
+----------+------------+---------+----------+-----------+
| 1 | 2019-08-01 | 4 | 1 | 2 |
| 2 | 2018-08-02 | 2 | 1 | 3 |
| 3 | 2019-08-03 | 3 | 2 | 3 |
| 4 | 2018-08-04 | 1 | 4 | 2 |
| 5 | 2018-08-04 | 1 | 3 | 4 |
| 6 | 2019-08-05 | 2 | 2 | 4 |
+----------+------------+---------+----------+-----------+
Items 表:
+---------+------------+
| item_id | item_brand |
+---------+------------+
| 1 | Samsung |
| 2 | Lenovo |
| 3 | LG |
| 4 | HP |
+---------+------------+
输出：
+-----------+------------+----------------+
| buyer_id | join_date | orders_in_2019 |
+-----------+------------+----------------+
| 1 | 2018-01-01 | 1 |
| 2 | 2018-02-09 | 2 |
| 3 | 2018-01-19 | 0 |
| 4 | 2018-05-21 | 0 |
+-----------+------------+----------------+
?? 答案
# Write your MySQL query statement below
SELECT user_id AS buyer_id, join_date, IFNULL(Buy.cnt, 0) AS orders_in_2019
FROM Users
LEFT JOIN (
SELECT buyer_id, COUNT(order_id) AS cnt
FROM Orders
WHERE Year(order_date)='2019'
GROUP BY buyer_id
) AS Buy
ON user_id=Buy.buyer_id
/* Write your T-SQL query statement below */
SELECT user_id AS buyer_id, join_date, isnull(Buy.cnt,0) AS orders_in_2019
FROM Users
LEFT JOIN (
SELECT buyer_id, COUNT(order_id) AS cnt
FROM Orders
WHERE Year(order_date)='2019'
GROUP BY buyer_id
) Buy
ON user_id=Buy.buyer_id
/* Write your PL/SQL query statement below */
select
buyer_id "buyer_id",
to_char(max(join_date),'yyyy-mm-dd') "join_date",
count(case when to_number(to_char(order_date,'yyyy')) = 2019 then order_id
else null end) "orders_in_2019"
from Users a,Orders b
where a.user_id = b. buyer_id
group by buyer_id
order by 1
第10天 过滤
?182.查找重复的电子邮箱
? 编写一个 SQL 查询，查找 Person 表中所有重复的电子邮箱。
示例：
+----+---------+
| Id | Email |
+----+---------+
| 1 | [email protected] |
| 2 | [email protected] |
| 3 | [email protected] |
+----+---------+
根据以上输入，你的查询应返回以下结果：
+---------+
| Email |
+---------+
| [email protected] |
+---------+
说明：所有电子邮箱都是小写字母。
?? 答案
# Write your MySQL query statement below
select Email from Person
group by Email
having count(*)>1
/* Write your T-SQL query statement below */
select distinct(p1.Email) Email from Person p1
join Person p2 on p1.Email = p2.Email AND p1.Id!=p2.Id
/* Write your PL/SQL query statement below */
select Email "Email" from(
select Email,count(Email) as num
from Person
group by Email
) where num>1
?1050. 合作过至少三次的演员和导演
? ActorDirector 表：
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| actor_id | int |
| director_id | int |
| timestamp | int |
+-------------+---------+
timestamp 是这张表的主键.
? 需求
写一条SQL查询语句获取合作过至少三次的演员和导演的 id 对 (actor_id, director_id)
示例：
ActorDirector 表：
+-------------+-------------+-------------+
| actor_id | director_id | timestamp |
+-------------+-------------+-------------+
| 1 | 1 | 0 |
| 1 | 1 | 1 |
| 1 | 1 | 2 |
| 1 | 2 | 3 |
| 1 | 2 | 4 |
| 2 | 1 | 5 |
| 2 | 1 | 6 |
+-------------+-------------+-------------+
Result 表：
+-------------+-------------+
| actor_id | director_id |
+-------------+-------------+
| 1 | 1 |
+-------------+-------------+
唯一的 id 对是 (1, 1)，他们恰好合作了 3 次。
?? 答案
# Write your MySQL query statement below
select actor_id,director_id
from ActorDirector
group by actor_id,director_id
having count(timestamp)>=3
/* Write your T-SQL query statement below */
select actor_id,director_id
from ActorDirector
group by actor_id,director_id
having count(timestamp)>=3
/* Write your PL/SQL query statement below */
select
actor_id "actor_id",
director_id "director_id"
from ActorDirector
group by actor_id,director_id
having count(*) > =3
?1587.银行账户概要 II
? 表: Users
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| account | int |
| name | varchar |
+--------------+---------+
account 是该表的主键.
表中的每一行包含银行里中每一个用户的账号.
? 需求
表: Transactions
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| trans_id | int |
| account | int |
| amount | int |
| transacted_on | date |
+---------------+---------+
trans_id 是该表主键.
该表的每一行包含了所有账户的交易改变情况.
如果用户收到了钱, 那么金额是正的; 如果用户转了钱, 那么金额是负的.
所有账户的起始余额为 0.
写一个 SQL, 报告余额高于 10000 的所有用户的名字和余额. 账户的余额等于包含该账户的所有交易的总和.
返回结果表单没有顺序要求.
查询结果格式如下例所示.
Users table:
+------------+--------------+
| account | name |
+------------+--------------+
| 900001 | Alice |
| 900002 | Bob |
| 900003 | Charlie |
+------------+--------------+
Transactions table:
+------------+------------+------------+---------------+
| trans_id | account | amount | transacted_on |
+------------+------------+------------+---------------+
| 1 | 900001 | 7000 | 2020-08-01 |
| 2 | 900001 | 7000 | 2020-09-01 |
| 3 | 900001 | -3000 | 2020-09-02 |
| 4 | 900002 | 1000 | 2020-09-12 |
| 5 | 900003 | 6000 | 2020-08-07 |
| 6 | 900003 | 6000 | 2020-09-07 |
| 7 | 900003 | -4000 | 2020-09-11 |
+------------+------------+------------+---------------+
Result table:
+------------+------------+
| name | balance |
+------------+------------+
| Alice | 11000 |
+------------+------------+
Alice 的余额为(7000 + 7000 - 3000) = 11000.
Bob 的余额为1000.
Charlie 的余额为(6000 + 6000 - 4000) = 8000.
?? 答案
# Write your MySQL query statement below
WITH t AS
(
SELECT account, SUM(amount) balance
FROM Transactions GROUP BY account HAVING SUM(amount)>10000
)
SELECT name, balance
FROM t JOIN Users USING(account)
/* Write your T-SQL query statement below */
select
name,
sum(isnull(b.amount, 0)) balance from Users a
left join Transactions b
on a.account = b. account
group by name
having sum(isnull(b.amount, 0))>10000
/* Write your PL/SQL query statement below */
select
name,
sum(nvl(b.amount, 0)) balance from Users a
left join Transactions b
on a.account = b. account
group by name
having sum(nvl(b.amount, 0))>10000
?1084. 销售分析III
? Table: Product
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| product_id | int |
| product_name | varchar |
| unit_price | int |
+--------------+---------+
Product_id是该表的主键。
该表的每一行显示每个产品的名称和价格。
Table: Sales
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| seller_id | int |
| product_id | int |
| buyer_id | int |
| sale_date | date |
| quantity | int |
| price | int |
+------ ------+---------+
这个表没有主键，它可以有重复的行。
product_id 是 Product 表的外键。
该表的每一行包含关于一个销售的一些信息。
? 需求
编写一个SQL查询，报告2019年春季才售出的产品。即仅在2019-01-01至2019-03-31（含）之间出售的商品。
以任意顺序 返回结果表。
查询结果格式如下所示。
示例 1:
输入：
Product table:
+------------+--------------+------------+
| product_id | product_name | unit_price |
+------------+--------------+------------+
| 1 | S8 | 1000 |
| 2 | G4 | 800 |
| 3 | iPhone | 1400 |
+------------+--------------+------------+
Sales table:
+-----------+------------+----------+------------+----------+-------+
| seller_id | product_id | buyer_id | sale_date | quantity | price |
+-----------+------------+----------+------------+----------+-------+
| 1 | 1 | 1 | 2019-01-21 | 2 | 2000 |
| 1 | 2 | 2 | 2019-02-17 | 1 | 800 |
| 2 | 2 | 3 | 2019-06-02 | 1 | 800 |
| 3 | 3 | 4 | 2019-05-13 | 2 | 2800 |
+-----------+------------+----------+------------+----------+-------+
输出：
+-------------+--------------+
| product_id | product_name |
+-------------+--------------+
| 1 | S8 |
+-------------+--------------+
解释:
id为1的产品仅在2019年春季销售。
id为2的产品在2019年春季销售，但也在2019年春季之后销售。
id为3的产品在2019年春季之后销售。
我们只返回产品1，因为它是2019年春季才销售的产品。
?? 答案
# Write your MySQL query statement below
select p.product_id, p.product_name
from Product p, Sales s
where p.product_id = s.product_id
group by p.product_id, p.product_name
having(sum(sale_date between '2019-01-01' and '2019-03-31') = count(*))
/* Write your T-SQL query statement below */
SELECT s.product_id , product_name
FROM Sales s JOIN Product p
ON s.product_id = p.product_id
GROUP BY s.product_id,product_name
HAVING MIN(sale_date) >= '2019-01-01' AND MAX(sale_date) <= '2019-03-31'
/* Write your PL/SQL query statement below */
SELECT s.product_id "product_id", product_name "product_name"
FROM Sales s JOIN Product p
ON s.product_id = p.product_id
GROUP BY s.product_id,product_name
HAVING MIN(sale_date) >= '2019-01-01' AND MAX(sale_date) <= '2019-03-31'
体系化学习SQL，请到经典高频面试题库，参加实训，提高你的SQL技能吧~
https://www.nowcoder.com/link/pc_csdncpt_itbd_sql