# LeetCode刷题100道，让你滚瓜烂熟拿下SQL

??????

✨ 如果有对【数据库】感兴趣的【小可爱】，欢迎关注【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 是这张表的主键。

? 需求

? 示例：

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' 表示不可回收。

? 需求

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           |
+-------------+

?? 答案
# 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 |
+------+------+-----------+

? 需求

+------+
| 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 是这个表的主键。

? 需求

Return the result table ordered by 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  |
+-------------+-------+

?? 答案
# 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') 中取。

? 需求

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是该表的主键列。

? 需求

Person 表:
+----+------------------+
| id | email            |
+----+------------------+
| 1  | [email protected] |
| 2  | [email protected]  |
| 3  | [email protected] |
+----+------------------+

+----+------------------+
| id | email            |
+----+------------------+
| 1  | [email protected] |
| 2  | [email protected]  |
+----+------------------+

?? 答案
# 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 是该表的主键。

? 需求

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 |
+-------------+---------+

? 需求

Activities 表：
+------------+-------------+
| sell_date  | product     |
+------------+-------------+
| 2020-06-01 | Pencil      |
| 2020-06-01 | Bible       |
| 2020-05-30 | T-Shirt     |
+------------+-------------+

+------------+----------+------------------------------+
| sell_date  | num_sold | products                     |
+------------+----------+------------------------------+
| 2020-06-01 | 2        | Bible,Pencil                 |
| 2020-06-02 | 1        | 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)
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;

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 个或以上的疾病代码，以空格分隔。

I 类糖尿病的代码总是包含前缀 DIAB1 。

? 需求

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 |
+------------+--------------+--------------+

?? 答案
# Write your MySQL query statement below
select * FROM Patients
WHERE conditions REGEXP '^DIAB1|\sDIAB1'

/* 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')

值	描述
^	匹配一个字符串的开始。如果与“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 是这个表的主键。

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| employee_id | int     |
| salary      | int     |
+-------------+---------+
employee_id is 这个表的主键。

? 需求

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号雇员的薪水信息丢失了。

?? 答案
# 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     |
+-------------+---------+

? 需求

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    |
+------------+--------+-------+

?? 答案

# 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
/
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 是这个表的主键。

? 需求

Employee 表：
+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

+---------------------+
| SecondHighestSalary |
+---------------------+
| 200                 |
+---------------------+

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 是该表的主键列。

+-------------+---------+
| 列名         | 类型    |
+-------------+---------+
| PersonId    | int     |
| City        | varchar |
| State       | varchar |
+-------------+---------+

? 需求

Person表:
+----------+----------+-----------+
| personId | lastName | firstName |
+----------+----------+-----------+
| 1        | Wang     | Allen     |
| 2        | Alice    | Bob       |
+----------+----------+-----------+
+-----------+----------+---------------+------------+
| 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 |
+-----------+----------+---------------+----------+

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"
where a.PersonId = b.PersonId(+)
order by 1
``````

## ?1581. 进店却未进行过交易的顾客

``````? 表：Visits
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| visit_id    | int     |
| customer_id | int     |
+-------------+---------+
visit_id 是该表的主键。

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| transaction_id | int     |
| visit_id       | int     |
| amount         | int     |
+----------------+---------+
transaction_id 是此表的主键。

? 需求

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 的顾客曾经去过购物中心，并且没有进行任何交易。

?? 答案

# 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    |
+---------------+---------+

? 需求

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 是这个表的主键

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 是该表的主键列。

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| com_id      | int     |
| name        | varchar |
| city        | varchar |
+-------------+---------+
com_id 是该表的主键列。

+-------------+------+
| 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 的外键。

? 需求

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 |
+------+

?? 答案
# 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')。

? 需求

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 |
| partner_id  | int     |
+-------------+---------+

? 需求

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               |
+-----------+-----------+--------------+-----------------+

?? 答案
# Write your MySQL query statement below
select date_id,make_name,
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 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) 是这个表的主键。

? 需求

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是该表的主键。

? 需求

Orders 表:
+--------------+-----------------+
| order_number | customer_number |
+--------------+-----------------+
| 1            | 1               |
| 2            | 2               |
| 3            | 3               |
| 4            | 3               |
+--------------+-----------------+

+-----------------+
| customer_number |
+-----------------+
| 3               |
+-----------------+

customer_number 为 '3' 的顾客有两个订单，比顾客 '1' 或者 '2' 都要多，因为他们只有一个订单。

?? 答案
# 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     |
+--------------+---------+

? 需求

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 表：
+-----------+-------------+
+-----------+-------------+
| 1         | 2016-03-01  |
| 2         | 2017-06-25  |
| 3         | 2016-03-02  |
+-----------+-------------+

?? 答案
# Write your MySQL query statement below
group by player_id

/* Write your T-SQL query statement below */
group by player_id

/* Write your PL/SQL query statement below */
select
player_id "player_id",
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 | 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
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"
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之间。

? 需求

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         |
+------------+--------+------------+

?? 答案
# 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) 是这张表的主键

? 需求

SQL查询结果的格式如下例所示：
Stocks 表:
+---------------+-----------+---------------+--------+
| stock_name    | operation | operation_day | price  |
+---------------+-----------+---------------+--------+
| Leetcode      | Buy       | 1             | 1000   |
| Leetcode      | Sell      | 5             | 9000   |
| Handbags      | Buy       | 17            | 30000  |
| Corona Masks  | Sell      | 3             | 1010   |
| Corona Masks  | Sell      | 5             | 500    |
| 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美元。

?? 答案
# 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,
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 是用户名字。

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| user_id       | int     |
| distance      | int     |
+---------------+---------+
id 是该表单主键。
user_id 是本次行程的用户的 id, 而该用户此次行程距离为 distance 。

? 需求

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 |
+----------------+---------+

Table: Orders
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| order_id      | int     |
| order_date    | date    |
| item_id       | int     |
| seller_id     | int     |
+---------------+---------+

Table: Items
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| item_id       | int     |
| item_brand    | varchar |
+---------------+---------+

? 需求

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
FROM Users
LEFT JOIN (
FROM Orders
WHERE Year(order_date)='2019'

/* Write your T-SQL query statement below */
FROM Users
LEFT JOIN (
FROM Orders
WHERE Year(order_date)='2019'

/* Write your PL/SQL query statement below */
select
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
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 是这张表的主键.

? 需求

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           |
+-------------+-------------+

?? 答案
# 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 是该表的主键.

? 需求

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| trans_id      | int     |
| account       | int     |
| amount        | int     |
| transacted_on | date    |
+---------------+---------+
trans_id 是该表主键.

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

/* 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     |
| sale_date   | date    |
| quantity    | int     |
| price       | int     |
+------ ------+---------+

product_id 是 Product 表的外键。

? 需求

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年春季之后销售。

?? 答案
# 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'
``````