- 练习题目来自：leetcode-cn.com/
项目员工系列
项目表 Project：
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| project_id | int |
| employee_id | int |
+-------------+---------+
主键为 (project_id, employee_id)。
employee_id 是员工表 Employee 表的外键。
员工表 Employee：
+------------------+---------+
| Column Name | Type |
+------------------+---------+
| employee_id | int |
| name | varchar |
| experience_years | int |
+------------------+---------+
主键是 employee_id。
1、请写一个 SQL 语句，查询每一个项目中员工的 平均 工作年限，精确到小数点后两位。
查询结果的格式如下：
Project 表：
+-------------+-------------+
| project_id | employee_id |
+-------------+-------------+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 4 |
+-------------+-------------+
Employee 表：
+-------------+--------+------------------+
| employee_id | name | experience_years |
+-------------+--------+------------------+
| 1 | Khaled | 3 |
| 2 | Ali | 2 |
| 3 | John | 1 |
| 4 | Doe | 2 |
+-------------+--------+------------------+
Result 表：
+-------------+---------------+
| project_id | average_years |
+-------------+---------------+
| 1 | 2.00 |
| 2 | 2.50 |
+-------------+---------------+
第一个项目中，员工的平均工作年限是 (3 + 2 + 1) / 3 = 2.00；第二个项目中，员工的平均工作年限是 (3 + 2) / 2 = 2.50
- SQL1：
- 子查询：
-
select t.project_id, round(avg(t.experience_years), 2) average_years from (select p.project_id, e.employee_id, e.experience_years from Project p join Employee e on p.employee_id = e.employee_id) t group by project_id
- SQL2：
- 窗口函数：
-
select distinct t.project_id, round(avg(t.experience_years) over(partition by project_id), 2) average_years from (select p.project_id, e.employee_id, e.experience_years from Project p join Employee e on p.employee_id = e.employee_id) t
2、编写一个SQL查询，报告所有雇员最多的项目。
查询结果格式如下所示：
Project table:
+-------------+-------------+
| project_id | employee_id |
+-------------+-------------+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 4 |
+-------------+-------------+
Employee table:
+-------------+--------+------------------+
| employee_id | name | experience_years |
+-------------+--------+------------------+
| 1 | Khaled | 3 |
| 2 | Ali | 2 |
| 3 | John | 1 |
| 4 | Doe | 2 |
+-------------+--------+------------------+
Result table:
+-------------+
| project_id |
+-------------+
| 1 |
+-------------+
第一个项目有3名员工，第二个项目有2名员工。
- SQL1：
-
select project_id from Project group by project_id having count(employee_id) >= all(select count(employee_id) from Project group by project_id)
-
- SQL2：
-
select project_id from Project group by project_id having count(employee_id) = (select count(employee_id) from Project group by project_id order by count(employee_id) DESC limit 1)
-
3、写 一个 SQL 查询语句，报告在每一个项目中经验最丰富的雇员是谁。如果出现经验年数相同的情况，请报告所有具有最大经验年数的员工。
查询结果格式在以下示例中：
Project 表：
+-------------+-------------+
| project_id | employee_id |
+-------------+-------------+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 4 |
+-------------+-------------+
Employee 表：
+-------------+--------+------------------+
| employee_id | name | experience_years |
+-------------+--------+------------------+
| 1 | Khaled | 3 |
| 2 | Ali | 2 |
| 3 | John | 3 |
| 4 | Doe | 2 |
+-------------+--------+------------------+
Result 表：
+-------------+---------------+
| project_id | employee_id |
+-------------+---------------+
| 1 | 1 |
| 1 | 3 |
| 2 | 1 |
+-------------+---------------+
employee_id 为 1 和 3 的员工在 project_id 为 1 的项目中拥有最丰富的经验。在 project_id 为 2 的项目中，employee_id 为 1 的员工拥有最丰富的经验。
- SQL1：
-
select p.project_id, e.employee_id from Project p join Employee e on p.employee_id = e.employee_id where (p.project_id, e.experience_years) in (select p.project_id, max(e.experience_years) from Project p join Employee e on p.employee_id = e.employee_id group by p.project_id)
-
- SQL2：
- 窗口函数
-
select t.project_id, t.employee_id from (select p.project_id, e.employee_id, rank() over(partition by p.project_id order by e.experience_years DESC) rk from Project p join Employee e on p.employee_id = e.employee_id) t where rk = 1
销售分析系列
产品表：Product
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| product_id | int |
| product_name | varchar |
| unit_price | int |
+--------------+---------+
product_id 是这个表的主键.
销售表：Sales
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| seller_id | int |
| product_id | int |
| buyer_id | int |
| sale_date | date |
| quantity | int |
| price | int |
+------ ------+---------+
这个表没有主键，它可以有重复的行.
product_id 是 Product 表的外键.
1、编写一个 SQL 查询，查询总销售额最高的销售者，如果有并列的，就都展示出来。
查询结果格式如下所示：
Product 表：
+------------+--------------+------------+
| product_id | product_name | unit_price |
+------------+--------------+------------+
| 1 | S8 | 1000 |
| 2 | G4 | 800 |
| 3 | iPhone | 1400 |
+------------+--------------+------------+
Sales 表：
+-----------+------------+----------+------------+----------+-------+
| 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 |
+-----------+------------+----------+------------+----------+-------+
Result 表：
+-------------+
| seller_id |
+-------------+
| 1 |
| 3 |
+-------------+
Id 为 1 和 3 的销售者，销售总金额都为最高的 2800。
-
SQL1：
-
select seller_id from Sales group by seller_id having sum(price) in (select max(sum) from (select seller_id, sum(price) sum from Sales group by seller_id order by sum DESC) t)
-
-
SQL2：
- 窗口函数
-
select distinct t1.seller_id from (select seller_id, RANK() over(order by t.sum DESC) rk from (select seller_id, sum(price) over(partition by seller_id) sum from Sales) t ) t1 where t1.rk = 1
-
SQL3：
- 窗口函数优化
-
select seller_id from (select seller_id, RANK() over(order by sum(price) DESC) rk from Sales group by seller_id) t where t.rk = 1
-
SQL4：
- 不小于最大的销售总金额
-
select seller_id from Sales group by seller_id having sum(price) >= all(select sum(price) from sales group by seller_id)
2、编写一个 SQL 查询，查询购买了 S8 手机却没有购买 iPhone 的买家。注意这里 S8 和 iPhone 是 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 | 1 | 3 | 2019-06-02 | 1 | 800 |
| 3 | 3 | 3 | 2019-05-13 | 2 | 2800 |
+-----------+------------+----------+------------+----------+-------+
Result table:
+-------------+
| buyer_id |
+-------------+
| 1 |
+-------------+
id 为 1 的买家购买了一部 S8，但是却没有购买 iPhone，而 id 为 3 的买家却同时购买了这 2 部手机。
- SQL1：
- 数量关系
-
select s.buyer_id buyer_id from Sales s join Product p on s.product_id = p.product_id group by s.buyer_id having sum(if(p.product_name = 'S8', 1, 0)) > 0 and sum(if(p.product_name = 'iPhone', 1, 0)) = 0
- SQL2：
-
select s.buyer_id buyer_id from Sales s join Product p on s.product_id = p.product_id group by s.buyer_id having count(if(p.product_name = 'S8', 1, null)) > 0 and count(if(p.product_name = 'iPhone', 1, null)) = 0
-
- SQL3：
- 窗口函数
-
select t.buyer_id buyer_id from (select buyer_id, sum(if(p.product_name = 'S8', 1, 0)) cnt1, sum(if(p.product_name = 'iPhone', 1, 0)) cnt2 from Sales s join Product p on s.product_id = p.product_id group by s.buyer_id having cnt1 > 0 and cnt2 = 0) t
- SQL4：
- 子查询
-
select distinct s.buyer_id buyer_id from Sales s join Product p on s.product_id = p.product_id where p.product_name = 'S8' and buyer_id not in (select buyer_id from Sales s join Product p on s.product_id = p.product_id WHERE p.product_name = 'iPhone')
3、编写一个SQL查询，报告2019年春季才售出的产品。即仅在2019-01-01至2019-03-31（含）之间出售的商品。
查询结果格式如下所示：
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 |
+-----------+------------+----------+------------+----------+-------+
Result table:
+-------------+--------------+
| product_id | product_name |
+-------------+--------------+
| 1 | S8 |
+-------------+--------------+
id为1的产品仅在2019年春季销售，其他两个产品在之后销售。
- SQL1：
- Join方法
-
select s.product_id, p.product_name from Sales s join Product p on s.product_id = p.product_id group by p.product_id having min(s.sale_date) >= '2019-01-01' and max(s.sale_date) <= '2019-03-31'
- SQL2：
-
select product_id, product_name from Product where product_id not in (select product_id from Sales where sale_date not between '2019-01-01' and '2019-03-31')
-
小众书籍
书籍表 Books：
+----------------+---------+
| Column Name | Type |
+----------------+---------+
| book_id | int |
| name | varchar |
| available_from | date |
+----------------+---------+
book_id 是这个表的主键。
订单表 Orders：
+----------------+---------+
| Column Name | Type |
+----------------+---------+
| order_id | int |
| book_id | int |
| quantity | int |
| dispatch_date | date |
+----------------+---------+
order_id 是这个表的主键。
book_id 是 Books 表的外键。
你需要写一段 SQL 命令，筛选出过去一年中订单总量 少于10本 的 书籍 。
注意：不考虑 上架（available from）距今 不满一个月 的书籍。并且 假设今天是 2019-06-23 。
下面是样例输出结果：
Books 表：
+---------+--------------------+----------------+
| book_id | name | available_from |
+---------+--------------------+----------------+
| 1 | "Kalila And Demna" | 2010-01-01 |
| 2 | "28 Letters" | 2012-05-12 |
| 3 | "The Hobbit" | 2019-06-10 |
| 4 | "13 Reasons Why" | 2019-06-01 |
| 5 | "The Hunger Games" | 2008-09-21 |
+---------+--------------------+----------------+
Orders 表：
+----------+---------+----------+---------------+
| order_id | book_id | quantity | dispatch_date |
+----------+---------+----------+---------------+
| 1 | 1 | 2 | 2018-07-26 |
| 2 | 1 | 1 | 2018-11-05 |
| 3 | 3 | 8 | 2019-06-11 |
| 4 | 4 | 6 | 2019-06-05 |
| 5 | 4 | 5 | 2019-06-20 |
| 6 | 5 | 9 | 2009-02-02 |
| 7 | 5 | 8 | 2010-04-13 |
+----------+---------+----------+---------------+
Result 表：
+-----------+--------------------+
| book_id | name |
+-----------+--------------------+
| 1 | "Kalila And Demna" |
| 2 | "28 Letters" |
| 5 | "The Hunger Games" |
+-----------+--------------------+
- SQL1：
-
select b.book_id, b.name from Books b left join Orders o on b.book_id = o.book_id and dispatch_date >= '2018-06-23' where available_from < '2019-05-23' group by book_id having ifnull(sum(quantity), 0) < 10
-
- SQL2：
-
select b.book_id, b.name from Books b left join (select book_id, sum(quantity) sum from Orders where datediff('2019-06-23', dispatch_date) < 365 group by book_id) t on b.book_id = t.book_id where datediff('2019-06-23', available_from) > 30 and (sum < 10 or sum is null)
-
- SQL3：
-
select b.book_id, b.name from Books b left join (select book_id, sum(quantity) sum from Orders where datediff('2019-06-23', dispatch_date) < 365 group by book_id) t on b.book_id = t.book_id where datediff('2019-06-23', available_from) > 30 and (sum < 10 or sum is null)
-
- SQL4：
-
select t1.book_id, t1.name from (select book_id, name from Books where datediff('2019-06-23', available_from) > 30) t1 left join (select book_id, sum(quantity) sum from Orders where datediff('2019-06-23', dispatch_date) < 365 group by book_id) t2 on t1.book_id = t2.book_id where ifnull(sum, 0) < 10
-
- SQL5：
-
select t1.book_id, t1.name from (select book_id, name from Books where datediff('2019-06-23', available_from) > 30) t1 left join orders o on t1.book_id = o.book_id and ifnull(datediff('2019-06-23', dispatch_date), 0) < 365 group by t1.book_id, t1.name having ifnull(sum(quantity), 0) < 10
-
每日新用户统计
Traffic 表：
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| user_id | int |
| activity | enum |
| activity_date | date |
+---------------+---------+
该表没有主键，它可能有重复的行。
activity 列是 ENUM 类型，可能取 ('login', 'logout', 'jobs', 'groups', 'homepage') 几个值之一。
编写一个 SQL 查询，以查询从今天起最多 90 天内，每个日期该日期首次登录的用户数。假设今天是 2019-06-30.
查询结果格式如下例所示：
Traffic 表：
+---------+----------+---------------+
| user_id | activity | activity_date |
+---------+----------+---------------+
| 1 | login | 2019-05-01 |
| 1 | homepage | 2019-05-01 |
| 1 | logout | 2019-05-01 |
| 2 | login | 2019-06-21 |
| 2 | logout | 2019-06-21 |
| 3 | login | 2019-01-01 |
| 3 | jobs | 2019-01-01 |
| 3 | logout | 2019-01-01 |
| 4 | login | 2019-06-21 |
| 4 | groups | 2019-06-21 |
| 4 | logout | 2019-06-21 |
| 5 | login | 2019-03-01 |
| 5 | logout | 2019-03-01 |
| 5 | login | 2019-06-21 |
| 5 | logout | 2019-06-21 |
+---------+----------+---------------+
Result 表：
+------------+-------------+
| login_date | user_count |
+------------+-------------+
| 2019-05-01 | 1 |
| 2019-06-21 | 2 |
+------------+-------------+
请注意，我们只关心用户数非零的日期.
ID 为 5 的用户第一次登陆于 2019-03-01，因此他不算在 2019-06-21 的的统计内。
- SQL1：
-
select activity_date login_date, count(distinct user_id) user_count from (select user_id, activity_date from Traffic where datediff('2019-06-30', activity_date) <= 90 and (user_id, activity_date) in (select user_id, min(activity_date) from Traffic where activity = 'login' group by user_id) ) t group by activity_date order by activity_date
-
- SQL2：
-
select login_date, count(distinct user_id) user_count from (select min(activity_date) login_date, user_id from Traffic where activity = 'login' group by user_id) t where datediff('2019-6-30',login_date) <= 90 group by login_date order by login_date
-
- SQL3：
- 窗口函数
-
select activity_date login_date, count(distinct user_id) user_count from (select user_id, activity_date, ROW_NUMBER() over(partition by user_id order by activity_date) rn from Traffic where activity = 'login') t where activity_date between date_add('2019-06-30', interval -90 day) and '2019-06-30' and rn = 1 group by activity_date order by activity_date
每位学生的最高成绩
表：Enrollments
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| student_id | int |
| course_id | int |
| grade | int |
+---------------+---------+
(student_id, course_id) 是该表的主键。
编写一个 SQL 查询，查询每位学生获得的最高成绩和它所对应的科目，若科目成绩并列，取 course_id 最小的一门。查询结果需按 student_id 增序进行排序。
查询结果格式如下所示：
Enrollments 表：
+------------+-------------------+
| student_id | course_id | grade |
+------------+-----------+-------+
| 2 | 2 | 95 |
| 2 | 3 | 95 |
| 1 | 1 | 90 |
| 1 | 2 | 99 |
| 3 | 1 | 80 |
| 3 | 2 | 75 |
| 3 | 3 | 82 |
+------------+-----------+-------+
Result 表：
+------------+-------------------+
| student_id | course_id | grade |
+------------+-----------+-------+
| 1 | 2 | 99 |
| 2 | 2 | 95 |
| 3 | 3 | 82 |
+------------+-----------+-------+
- SQL1：
-
select student_id, min(course_id) course_id, grade from Enrollments where (student_id, grade) in(select student_id, max(grade) from Enrollments group by student_id ) group by student_id, grade order by student_id
-
- SQL2：
- 窗口函数
-
select t.student_id, t.course_id, t.grade from (select student_id, course_id, grade, ROW_NUMBER() over(partition by student_id order by grade DESC, course_id) rn from Enrollments) t where rn = 1
查询活跃业务
事件表：Events
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| business_id | int |
| event_type | varchar |
| occurences | int |
+---------------+---------+
此表的主键是 (business_id, event_type)。
表中的每一行记录了某种类型的事件在某些业务中多次发生的信息。
写一段 SQL 来查询所有活跃的业务。
如果一个业务的某个事件类型的发生次数大于此事件类型在所有业务中的平均发生次数，并且该业务至少有两个这样的事件类型，那么该业务就可被看做是活跃业务。
查询结果格式如下所示：
Events table:
+-------------+------------+------------+
| business_id | event_type | occurences |
+-------------+------------+------------+
| 1 | reviews | 7 |
| 3 | reviews | 3 |
| 1 | ads | 11 |
| 2 | ads | 7 |
| 3 | ads | 6 |
| 1 | page views | 3 |
| 2 | page views | 12 |
+-------------+------------+------------+
结果表
+-------------+
| business_id |
+-------------+
| 1 |
+-------------+
'reviews'、 'ads' 和 'page views' 的总平均发生次数分别是 (7+3)/2=5, (11+7+6)/3=8, (3+12)/2=7.5。
id 为 1 的业务有 7 个 'reviews' 事件（大于 5）和 11 个 'ads' 事件（大于 8），所以它是活跃业务。
- SQL：
-
select extra report_reason, count(distinct post_id) report_count from Actions where action = 'report' and action_date = '2019-07-04' and extra is not null group by extra
-
