文章目录
- 1. 重复的电子邮件：
- 解决方案：
- 2. 游戏玩法分析
- 解决方案
- 3. 获得最高回答率问题
- 解决方案
- 4. 寻找客户推荐人
- 解决方案
- 5. 下单数量最多的顾客
- 解决方案
- 6. 大国
- 解决方案
- 7. 超过 5 名学生的班级
- 解决方案
- 8. 最大的单个数字
- 解决方案
- 9. 不无聊的电影
- 解决方案
- 10. 至少合作三次的演员和导演
- 解决方案
- 11. 产品销售分析
- 解决方案
- 12. 项目员工
- 解决方案
- 13. 销售分析
- 解决方案
- 14. 过去 30 天的用户活动
- 解决方案
- 15. 文章浏览量
- 解决方案
- 16. 文章浏览量
- 解决方案
- 17. 删除重复邮件
- 解决方案
- 18. 交换工资
- 解决方案
- 19. 组合两个表
- 解决方案
- 20. 找出薪水比经理高的员工
- 解决方案
- 21. 从来没有下过订单的顾客
- 解决方案
- 22. 部门最高薪水
- 解决方案
- 23. 气温比前一天高
- 解决方案
- 24. 行程和用户
- 解决方案
- 25. 游戏数据分析
- 解决方案
- 26. 游戏数据分析
- 解决方案
- 27. 拥有至少 5 名直接下属的经理
- 解决方案
- 28. 赢家候选
- 解决方案
- 29.
- 解决方案
- 30. 统计各系学生人数
- 解决方案
- 31. 销售人员
- 解决方案
- 32. 二级追随者
- 解决方案
- 33. 平均工资：部门 VS 公司
- 解决方案
- 34.
- 35.
- 36.
- 37.
- 38.
- 39.
- 40.
- 41.
- 42.
- 43.
- 44.
- 45.
- 46.
- 47.
- 48.
- 49.
- 50.
- 51.
- 52.
- 53.
- 54.
- 55.
- 56.
- 57.
- 58.
- 59.
- 60.
- 61.
- 62.
- 63.
- 64.
- 65.
- 66.
- 67.
- 68.
- 69.
- 70.
- 71.
- 72.
- 73.
- 74.
- 75.
- 76.
- 77.
- 78.
- 79.
- 80.
- 81.
- 82.
- 83.
- 84.
- 85.
- 86.
- 87.
- 88.
- 89.
- 90.
- 91.
- 92.
- 93.
- 94.
- 95.
- 96.
- 97.
- 98.
- 99.
- 100.
- 101.
- 102.
1. 重复的电子邮件：
编写一个解决方案来报告所有重复的电子邮件。请注意，保证电子邮件字段不为 NULL。以任意顺序返回结果表。结果格式如下例所示。
示例1：
输入:
Person table:
| id | |
|---|---|
| 1 | a@b.com |
| 2 | c@d.com |
| 3 | a@b.com |
输出:
| a@b.com |
解释： a@b.com is repeated two times.
解决方案：
SELECT email
FROM person
GROUP BY email
HAVING COUNT(*) > 1;
2. 游戏玩法分析
活动表 (Activity)
| Column Name | Type |
|---|---|
| player_id | int |
| device_id | int |
| event_date | date |
| games_played | int |
在 SQL 中，表的主键是 (player_id, event_date)。
这张表展示了一些游戏玩家在游戏平台上的行为活动。每行数据记录了一名玩家在退出平台之前，当天使用同一台设备登录平台后打开的游戏的数目（可能是 0 个）。
查询每位玩家第一次登录平台的日期。
示例1：
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 |
结果表:
| player_id | first_login |
|---|---|
| 1 | 2016-03-01 |
| 2 | 2017-06-25 |
| 3 | 2016-03-02 |
解决方案
SELECT player_id, MIN(event_date) AS first_login
FROM activity
GROUP BY player_id;
3. 获得最高回答率问题
从具有以下列的表 survey_log 中获取回答率最高的问题：uid、action、question_id、answer_id、q_num、timestamp。
uid表示用户 ID；action有以下几种值：show、answer、skip；- 当
action列为answer时answer_id不为null，而show和skip为null； q_num是当前会话中问题的数字顺序。
编写一个sql查询来找出回答率最高的问题。
Input:
| uid | action | question_id | answer_id | q_num | timestamp |
|---|---|---|---|---|---|
| 5 | show | 285 | null | 1 | 123 |
| 5 | answer | 285 | 124124 | 1 | 124 |
| 5 | show | 369 | null | 2 | 125 |
| 5 | skip | 369 | null | 2 | 126 |
Output:
| survey_log |
|---|
| 285 |
Explanation:
问题 285 的答对率为 1/1，而问题 369 的答对率为 0/1，因此输出结果为 285。
解决方案
SELECT TOP 1 question_id AS survey_log
FROM survey_log
GROUP BY question_id
ORDER BY COUNT(answer_id)*1.0/(COUNT(*)-COUNT(answer_id)) DESC;
4. 寻找客户推荐人
Table: Customer
| Column Name | Type |
|---|---|
| id | int |
| name | varchar |
| referee_id | int |
在 SQL 中，"id "是该表的主键列。表中的每一行都显示客户的 id 、客户的 name 和推荐客户的 id 。
查找不是由客户通过 id = 2 引用的客户姓名。以任意顺序返回结果表。结果格式如下例所示。
示例 1:
Input:
Customer table:
| id | name | referee_id |
|---|---|---|
| 1 | Will | null |
| 2 | Jane | null |
| 3 | Alex | 2 |
| 4 | Bill | null |
| 5 | Zack | 1 |
| 6 | Mark | 2 |
Output:
| name |
|---|
| Will |
| Jane |
| Bill |
| Zack |
解决方案
-- Solution 1: Basics
SELECT name
FROM customer
WHERE ISNULL(referee_id,0) != 2;
-- Solution 2: Basics
SELECT name
FROM customer
WHERE COALESCE(referee_id,0) != 2;
-- Solution 3: Basics
SELECT name
FROM customer
WHERE referee_id != 2 OR referee_id IS NULL;
5. 下单数量最多的顾客
Table: Orders
| Column Name | Type |
|---|---|
| order_number | int |
| customer_number | int |
order_number 是该表的主键（具有唯一值的列）。该表包含有关订单 ID 和客户 ID 的信息。
编写一个解决方案来查找下订单数量最多的客户的 customer_number 。生成测试用例是为了确保一个客户下的订单比任何其他客户都多。结果格式如下例所示。
示例1:
Input:
Orders table:
| order_number | customer_number |
|---|---|
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 3 |
Output:
| customer_number |
|---|
| 3 |
Explanation:
编号为 3 的客户有两个订单，大于客户 1 或客户 2，因为他们每人只有一个订单。因此，结果是 “客户编号 3”。
追问：
如果不止一个客户的订单数量最多，在这种情况下，您能找到所有的 `客户编号’吗？
解决方案
SELECT TOP 1 customer_number
FROM orders
GROUP BY customer_number
ORDER BY COUNT(*) DESC;
6. 大国
Table: World
| Column Name | Type |
|---|---|
| name | varchar |
| continent | varchar |
| area | int |
| population | int |
| gdp | bigint |
name "是该表的主键（具有唯一值的列）。该表的每一行都提供了一个国家的名称、所属大洲、面积、人口和 GDP 值等信息。
如果出现以下情况，一个国家就是大国
- 面积至少有 300 万（即 3000000 平方公里），或
- 人口至少有两千五百万（即 25000000）。
写出一个解决方案，找出大国的名称、人口和面积。按任意顺序返回结果表。
结果格式见下例。
示例 1 :
Input:
World table:
| 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 |
Output:
| name | population | area |
|---|---|---|
| Afghanistan | 25500100 | 652230 |
| Algeria | 37100000 | 2381741 |
解决方案
SELECT name, population, area
FROM World
WHERE area > 3000000 OR population > 25000000;
7. 超过 5 名学生的班级
Table: Courses
| Column Name | Type |
|---|---|
| student | varchar |
| class | varchar |
(student, class) 是此表的主键（具有唯一值的列组合）。该表的每一行都显示了学生的姓名和所在班级。
写出一个解决方案，找出所有至少有 5 名学生的班级。以任意顺序返回结果表。结果格式如下例所示。
示例 1：
Input:
Courses table:
| student | class |
|---|---|
| A | Math |
| B | English |
| C | Math |
| D | Biology |
| E | Math |
| F | Computer |
| G | Math |
| H | Math |
| I | Math |
Output:
| class |
|---|
| Math |
Explanation:
- 数学有 6 名学生，因此我们将其包括在内。
- 英语有 1 名学生，所以不包括在内。
- 生物有 1 名学生，因此我们不将其包括在内。
- 计算机有 1 名学生，因此不包括在内。
解决方案
SELECT class
FROM courses
GROUP BY class
HAVING COUNT(DISTINCT student) >= 5;
8. 最大的单个数字
Table: MyNumbers
| Column Name | Type |
|---|---|
| num | int |
该表可能包含重复数据（换句话说，该表在 SQL 中没有主键）。该表的每一行都包含一个整数。
单一数字是指只在 MyNumbers 表中出现过一次的数字。找出最大的单个数字。如果没有单个数字，则报告 null。结果格式如下例所示。
示例1 :
Input:
MyNumbers table:
| num |
|---|
| 8 |
| 8 |
| 3 |
| 3 |
| 1 |
| 4 |
| 5 |
| 6 |
Output:
| num |
|---|
| 6 |
Explanation:
单个数字是 1、4、5 和 6。
由于 6 是最大的单个数字，因此我们返回它。
示例 2 :
Input:
MyNumbers table:
| num |
|---|
| 8 |
| 8 |
| 7 |
| 7 |
| 3 |
| 3 |
| 3 |
Output:
| num |
|---|
| null |
Explanation:
输入表中没有单个数字，因此我们返回 null。
解决方案
SELECT MAX(num) AS num
FROM (
-- Select numbers appear only once
SELECT num
FROM my_numbers
GROUP BY num
HAVING COUNT(*) = 1
) tb1;
9. 不无聊的电影
Table: Cinema
| Column Name | Type |
|加粗样式-------------|---------|
| id | int |
| movie | varchar |
| description | varchar |
| rating | float |
id 是该表的主键（具有唯一值的列）。每一行都包含电影名称、类型和评分信息。rating是一个小数点后 2 位的浮点数，取值范围为 [0, 10] 。
编写一个解决方案，报告 ID 为奇数且描述不 "无聊 "的电影。返回按评分降序排列的结果表。结果格式如下例所示。
示例 1：
Input:
Cinema table:
| id | movie | description | rating |
|---|---|---|---|
| 1 | War | great 3D | 8.9 |
| 2 | Science | fiction | 8.5 |
| 3 | irish | boring | 6.2 |
| 4 | Ice song | Fantacy | 8.6 |
| 5 | House card | Interesting | 9.1 |
Output:
| id | movie | description | rating |
|---|---|---|---|
| 5 | House card | Interesting | 9.1 |
| 1 | War | great 3D | 8.9 |
Explanation:
我们有三部 ID 为奇数的电影：1、3 和 5。ID = 3 的电影很无聊，因此我们不将其列入答案。
解决方案
SELECT *
FROM cinema
WHERE id % 2 = 1 AND description != 'boring'
ORDER BY rating DESC;
10. 至少合作三次的演员和导演
Table: ActorDirector
| Column Name | Type |
|---|---|
| actor_id | int |
| director_id | int |
| timestamp | int |
timestamp是该表的主键（具有唯一值的列）。写一个解决方案，找出演员与导演至少合作过三次的所有配对（actor_id, director_id）。按任意顺序返回结果表。结果格式如下例所示。
示例 1：
Input:
ActorDirector table:
| 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 |
Output:
| actor_id | director_id |
|---|---|
| 1 | 1 |
Explanation:
唯一的一对是（1，1），他们正好合作了 3 次。
解决方案
SELECT actor_id, director_id
FROM ActorDirector
GROUP BY actor_id, director_id
HAVING COUNT(*) >= 3;
11. 产品销售分析
Table: Sales
| Column Name | Type |
|---|---|
| sale_id | int |
| product_id | int |
| year | int |
| quantity | int |
| price | int |
sale_id 是该表的主键。product_id是 "Product"表的外键。请注意，price是按单位计算的。
Table: Product
| Column Name | Type |
|---|---|
| product_id | int |
| product_name | varchar |
product_id 是该表的主键。编写一条 SQL 查询，报告每个产品 ID 的总销售量。查询结果格式如下：
Sales table:
| sale_id | product_id | year | quantity | price |
|---|---|---|---|---|
| 1 | 100 | 2008 | 10 | 5000 |
| 2 | 100 | 2009 | 12 | 5000 |
| 7 | 200 | 2011 | 15 | 9000 |
Product table:
| product_id | product_name |
|---|---|
| 100 | Nokia |
| 200 | Apple |
| 300 | Samsung |
Result table:
| product_id | total_quantity |
|---|---|
| 100 | 22 |
| 200 | 15 |
解决方案
SELECT product_id, SUM(quantity) AS total_quantity
FROM Sales
GROUP BY product_id;
12. 项目员工
Table: Project
| Column Name | Type |
|---|---|
| project_id | int |
| employee_id | int |
(project_id,employee_id)是该表的主键。
employee_id 是 Employee 表的外键。
Table: Employee
| Column Name | Type |
|---|---|
| employee_id | int |
| name | varchar |
| experience_years | int |
employee_id 是该表的主键。编写一个 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 名员工，因此第一个项目是员工人数最多的项目。
解决方案
SELECT TOP 1 WITH TIES project_id
FROM Project
GROUP BY project_id
ORDER BY COUNT(employee_id) DESC;
13. 销售分析
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 查询，按总销售价格报告最畅销的产品。如果出现并列，则全部报告。查询结果格式如下：
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:
| seller_id |
|---|
| 1 |
| 3 |
id为 1 和 3 的卖家都卖出了总价最高的 2800 件产品。
解决方案
SELECT TOP 1 WITH TIES seller_id
FROM Sales
GROUP BY seller_id
ORDER BY SUM(price) DESC;
14. 过去 30 天的用户活动
Table: Activity
| Column Name | Type |
|---|---|
| user_id | int |
| session_id | int |
| activity_date | date |
| activity_type | enum |
此表可能有重复行。
activity_type 列是一个 ENUM（类别）类 (open_session, end_session, scroll_down, send_message).
该表显示了一个社交媒体网站的用户活动。
请注意，每个会话只属于一个用户。
写一个解决方案，找出截至 2019-07-27 日（含）的 30 天内的每日活跃用户数。如果用户在某天至少进行了一次活动，则该用户在该天为活跃用户。以任意顺序返回结果表。结果格式如下例所示。
示例 1：
Input:
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 |
Output:
| day | active_users |
|---|---|
| 2019-07-20 | 2 |
| 2019-07-21 | 2 |
Explanation:
请注意，我们并不关心活跃用户为零的天数。
解决方案
SELECT activity_date AS day, COUNT(DISTINCT user_id) AS active_users
FROM Activity
GROUP BY activity_date
HAVING activity_date BETWEEN DATEADD(day,-29,'2019-07-27') and '2019-07-27';
15. 文章浏览量
Table: Views
| Column Name | Type |
|---|---|
| article_id | int |
| author_id | int |
| viewer_id | int |
| view_date | date |
该表没有主键（具有唯一值的列），因此可能有重复行。
该表中的每一行都表示某个读者在某个日期浏览了某篇文章（由某个作者撰写）。
请注意，相同的 author_id 和 viewer_id 表示同一个人。
写一个解决方案，找出所有至少浏览过一篇自己文章的作者。返回按 id 升序排序的结果表。结果格式如下。
示例 1：
Input:
Views table:
| 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 |
Output:
| id |
|---|
| 4 |
| 7 |
解决方案
SELECT DISTINCT author_id AS id
FROM Views
WHERE author_id = viewer_id
ORDER BY id;
16. 文章浏览量
Table: Views
| Column Name | Type |
|---|---|
| article_id | int |
| author_id | int |
| viewer_id | int |
| view_date | date |
该表没有主键，可能有重复行。该表中的每一行都表示某个读者在某个日期浏览了某篇文章（由某个作者撰写）。请注意，相同的 author_id 和 viewer_id 表示同一个人。
编写一条 SQL 查询，找出所有在同一日期浏览过一篇以上文章的人，并按其 id 升序排序。查询结果格式如下：
Views table:
| article_id | author_id | viewer_id | view_date |
|---|---|---|---|
| 1 | 3 | 5 | 2019-08-01 |
| 3 | 4 | 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 |
Result table:
| id |
|---|
| 5 |
| 6 |
ID 为 5 和 6 的用户在 2019-08-01 浏览了不止一篇文章。
解决方案
SELECT DISTINCT viewer_id AS id
FROM Views
GROUP BY viewer_id, view_date
-- When viewer viewed the same article more than once in the same day, using DISTINCT could avoid count that article twice
HAVING COUNT(DISTINCT article_id) > 1
ORDER BY id;
17. 删除重复邮件
Table: Person
| Column Name | Type |
|---|---|
| id | int |
| varchar |
id 是该表的主键（具有唯一值的列）。
该表的每一行都包含一个电子邮件。电子邮件不包含大写字母。
请编写一个解决方案，删除所有重复的电子邮件，只保留一个具有最小 id 的唯一电子邮件。
对于 SQL 用户，请注意应编写 DELETE 语句，而不是 SELECT 语句。
个人表的最终顺序并不重要。
结果格式如下例所示。
示例 1：
Input:
Person table:
| id | |
|---|---|
| 1 | john@example.com |
| 2 | bob@example.com |
| 3 | john@example.com |
Output:
| id | |
|---|---|
| 1 | john@example.com |
| 2 | bob@example.com |
Explanation:
john@example.com 重复两次。我们保留最小 Id = 1 的一行。
解决方案
-- Solution 1: SQL Command, Join
DELETE p1
FROM person p1
JOIN person p2
ON p1.email = p2.email AND p1.id > p2.id;
-- Solution 2: SQL Command, Subquery
DELETE FROM person
WHERE id NOT IN (
SELECT *
FROM (
SELECT MIN(id) AS id
FROM person
GROUP BY email
) tb1
);
18. 交换工资
Table: Salary
| Column Name | Type |
|---|---|
| id | int |
| name | varchar |
| sex | ENUM |
| salary | int |
id 是该表的主键（具有唯一值的列）。
sex 列是类型为('m', 'f')的 ENUM（类别）值。
该表包含一名雇员的信息。
编写一个解决方案，用一条更新语句交换所有的 "f "和 "m "值（即把所有的 "f "值改为 “m”，反之亦然），并且不使用中间临时表。请注意，您必须编写一条更新语句，不要为这个问题编写任何选择语句。结果格式如下例所示。
示例 1：
Input:
Salary table:
| id | name | sex | salary |
|---|---|---|---|
| 1 | A | m | 2500 |
| 2 | B | f | 1500 |
| 3 | C | m | 5500 |
| 4 | D | f | 500 |
Output:
| id | name | sex | salary |
|---|---|---|---|
| 1 | A | f | 2500 |
| 2 | B | m | 1500 |
| 3 | C | f | 5500 |
| 4 | D | m | 500 |
Explanation:
(1、A）和（3、C）中的 "m "改为 “f”。
(2、B）和（4、D）中的 "f "改为 “m”。
解决方案
UPDATE salary
SET sex = CASE WHEN sex = 'm' THEN 'f' ELSE 'm' END;
19. 组合两个表
表: Person
| 列名 | 类型 |
|---|---|
| PersonId | int |
| FirstName | varchar |
| LastName | varchar |
personId 是该表的主键（具有唯一值的列）。该表包含一些人的 ID 和他们的姓和名的信息。
表: Address
| 列名 | 类型 |
|---|---|
| AddressId | int |
| PersonId | int |
| City | varchar |
| State | varchar |
addressId 是该表的主键（具有唯一值的列）。该表的每一行都包含一个 ID = PersonId 的人的城市和州的信息。
编写解决方案，报告 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 的地址信息。
解决方案
SELECT p.firstname, p.lastname, a.city, a.state
FROM person p
-- LEFT JOIN return all records from the left table, and the matched records from the right table
LEFT JOIN address a
ON p.personid = a.personid;
20. 找出薪水比经理高的员工
Table: Employee
| Column Name | Type |
|---|---|
| id | int |
| name | varchar |
| salary | int |
| managerId | int |
id 是该表的主键（具有唯一值的列）。该表的每一行都包含员工的 ID、姓名、工资及其经理的 ID。
编写一个解决方案，找出收入高于其经理的员工。按任意顺序返回结果表。结果格式如下例所示。
例 1：
Input:
Employee table:
| id | name | salary | managerId |
|---|---|---|---|
| 1 | Joe | 70000 | 3 |
| 2 | Henry | 80000 | 4 |
| 3 | Sam | 60000 | Null |
| 4 | Max | 90000 | Null |
Output:
| Employee |
|---|
| Joe |
解释： 乔是唯一一个收入比经理高的员工。
解决方案
SELECT e1.name AS Employee
FROM employee e1
JOIN employee e2
ON e1.Managerid = e2.id
WHERE e1.salary > e2.salary;
21. 从来没有下过订单的顾客
Table: Customers
| Column Name | Type |
|---|---|
| id | int |
| name | varchar |
id 是该表的主键（具有唯一值的列）。该表的每一行都表示一位客户的 ID 和姓名。
Table: Orders
| Column Name | Type |
|---|---|
| id | int |
| customerId | int |
id 是该表的主键（具有唯一值的列）。customerId 是客户表 ID 的外键（引用列）。该表的每一行都表示订单的 ID 和订购该订单的客户的 ID。
编写一个解决方案，找出所有从未订购过任何东西的客户。按任意顺序返回结果表。结果格式如下例所示。
Example 1:
Input:
Customers table:
| id | name |
|---|---|
| 1 | Joe |
| 2 | Henry |
| 3 | Sam |
| 4 | Max |
Orders table:
| id | customerId |
|---|---|
| 1 | 3 |
| 2 | 1 |
Output:
| Customers |
|---|
| Henry |
| Max |
解决方案
SELECT c.name AS customers
FROM customers c
LEFT JOIN orders o
ON c.id = o.customerid
WHERE o.id IS NULL;
22. 部门最高薪水
Table: Employee
| Column Name | Type |
|---|---|
| id | int |
| name | varchar |
| salary | int |
| departmentId | int |
id 是该表的主键（具有唯一值的列）。departmentId 是部门表中 ID 的外键（引用列）。该表的每一行都显示了员工的 ID、姓名和工资。它还包含其部门的 ID。
Table: Department
| Column Name | Type |
|---|---|
| id | int |
| name | varchar |
id 是该表的主键（具有唯一值的列）。保证部门名称不是空值。该表的每一行都表示一个部门的 ID 及其名称。
编写一个解决方案，找出每个部门中工资最高的员工。按任意顺序返回结果表。结果格式如下例所示。
Example 1:
Input:
Employee table:
| id | name | salary | departmentId |
|---|---|---|---|
| 1 | Joe | 70000 | 1 |
| 2 | Jim | 90000 | 1 |
| 3 | Henry | 80000 | 2 |
| 4 | Sam | 60000 | 2 |
| 5 | Max | 90000 | 1 |
Department table:
| id | name |
|---|---|
| 1 | IT |
| 2 | Sales |
Output:
| Department | Employee | Salary |
|---|---|---|
| IT | Jim | 90000 |
| Sales | Henry | 80000 |
| IT | Max | 90000 |
解释：麦克斯和吉姆都是信息技术部门工资最高的人，而亨利是销售部门工资最高的人。
解决方案
WITH max_salary AS (
SELECT departmentid, max(salary) AS max_salary
FROM employee
GROUP BY departmentid
)
SELECT d.name department, e.name employee, e.salary
FROM max_salary ms
JOIN employee e
ON ms.departmentid = e.departmentid AND ms.max_salary = e.salary
JOIN department d
ON e.departmentid = d.id;
23. 气温比前一天高
Table: Weather
| Column Name | Type |
|---|---|
| id | int |
| recordDate | date |
| temperature | int |
id 是该表具有唯一值的列。没有记录日期相同的不同行。该表包含某天的温度信息。
编写一个解决方案，找出与前一天（昨天）相比温度较高的所有日期的 Id。以任意顺序返回结果表。结果格式见下例。
Example 1:
Input:
Weather table:
| id | recordDate | temperature |
|---|---|---|
| 1 | 2015-01-01 | 10 |
| 2 | 2015-01-02 | 25 |
| 3 | 2015-01-03 | 20 |
| 4 | 2015-01-04 | 30 |
Output:
| id |
|---|
| 2 |
| 4 |
Explanation:
2015-01-02，气温高于前一天（10 -> 25）。
2015-01-04，气温高于前一天（20 -> 30）。
解决方案
SELECT w2.id
FROM weather w1
JOIN weather w2
ON DATEADD(day,1,w1.recorddate) = w2.recorddate
WHERE w1.temperature < w2.temperature;
24. 行程和用户
Table: Trips
| Column Name | Type |
|---|---|
| id | int |
| client_id | int |
| driver_id | int |
| city_id | int |
| status | enum |
| request_at | date |
id 是该表的主键（具有唯一值的列）。该表保存所有出租车行程。每个行程都有一个唯一的 id，而客户_id 和司机_id 是用户表中 users_id 的外键。状态是一个 ENUM（类别）类型 (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’).
Table: Users
| Column Name | Type |
|---|---|
| users_id | int |
| banned | enum |
| role | enum |
users_id 是该表的主键（具有唯一值的列）。该表包含所有用户。每个用户都有唯一的 users_id，角色是 ENUM 类型（“客户”、“司机”、“合作伙伴”）。禁止是一个 ENUM（类别）类型（“是”、“否”）。
取消率的计算方法是：当天未被禁用用户的（客户或司机）取消请求数除以未被禁用用户的总请求数。
请写出一个解决方案，求出 "2013-10-01 "至 "2013-10-03 "期间每天未被禁用用户（客户端和驱动程序都必须未被禁用）的请求取消率。将取消率四舍五入到小数点后两位。按任意顺序返回结果表。
结果格式如下。
Example 1:
Input:
Trips table:
| id | client_id | driver_id | city_id | status | request_at |
|---|---|---|---|---|---|
| 1 | 1 | 10 | 1 | completed | 2013-10-01 |
| 2 | 2 | 11 | 1 | cancelled_by_driver | 2013-10-01 |
| 3 | 3 | 12 | 6 | completed | 2013-10-01 |
| 4 | 4 | 13 | 6 | cancelled_by_client | 2013-10-01 |
| 5 | 1 | 10 | 1 | completed | 2013-10-02 |
| 6 | 2 | 11 | 6 | completed | 2013-10-02 |
| 7 | 3 | 12 | 6 | completed | 2013-10-02 |
| 8 | 2 | 12 | 12 | completed | 2013-10-03 |
| 9 | 3 | 10 | 12 | completed | 2013-10-03 |
| 10 | 4 | 13 | 12 | cancelled_by_driver | 2013-10-03 |
Users table:
| users_id | banned | role |
|---|---|---|
| 1 | No | client |
| 2 | Yes | client |
| 3 | No | client |
| 4 | No | client |
| 10 | No | driver |
| 11 | No | driver |
| 12 | No | driver |
| 13 | No | driver |
Output:
| Day | Cancellation Rate |
|---|---|
| 2013-10-01 | 0.33 |
| 2013-10-02 | 0.00 |
| 2013-10-03 | 0.50 |
Explanation:
2013-10-01：
- 共有 4 个请求，其中 2 个被取消。
- 但是，Id=2 的请求是由一个被禁用的客户端（User_Id=2）提出的，因此在计算中被忽略。
- 因此，共有 3 个未被禁用的请求，其中 1 个被取消。
- 取消率为 (1 / 3) = 0.33
在 2013-10-02： - 共有 3 个请求，其中 0 个被取消。
- Id=6 的请求是由一个被禁用的客户端提出的，因此被忽略。
- 因此，共有 2 个未被禁用的请求，其中 0 个被取消。
- 取消率为 (0 / 2) = 0.00
2013-10-03： - 共有 3 个请求，其中 1 个被取消。
- Id=8 的请求是由被禁用的客户端提出的，因此被忽略。
- 因此，共有 2 个未被禁用的请求，其中 1 个被取消。
- 取消率为 (1 / 2) = 0.50
解决方案
SELECT t.Request_at AS 'Day',
CAST(
AVG(CASE
WHEN status = 'completed' then 0
ELSE 1.0
END)
AS DECIMAL(3,2)
) AS 'Cancellation Rate'
FROM Trips AS t
INNER JOIN Users c
ON t.Client_Id=c.Users_Id
INNER JOIN Users d
ON t.Driver_Id=d.Users_Id
-- filter our banned client and driver and limit time frame
WHERE c.Banned='no' AND
d.Banned='no' AND
t.Request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY t.Request_at;
25. 游戏数据分析
Table: Activity
| Column Name | Type |
|---|---|
| player_id | int |
| device_id | int |
| event_date | date |
| games_played | int |
(player_id，event_date）是该表的主键。
该表显示了某款游戏的玩家活动。
每一行都是一个玩家的记录，该玩家在某天使用某个设备登录并玩了若干游戏（可能为 0），然后注销。
编写一个 SQL 查询，报告每个玩家首次登录的设备。
查询结果格式如下：
Activity table:
| 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 table:
| player_id | device_id |
|---|---|
| 1 | 2 |
| 2 | 3 |
| 3 | 1 |
解决方案
SELECT a.player_id, device_id
FROM Activity a
JOIN (
SELECT player_id, MIN(event_date) min_date
FROM Activity
GROUP BY player_id
) tb1
ON a.player_id = tb1.player_id AND a.event_date = tb1.min_date;
26. 游戏数据分析
Table: Activity
| Column Name | Type |
|---|---|
| player_id | int |
| device_id | int |
| event_date | date |
| games_played | int |
(player_id，event_date）是该表的主键（具有唯一值的列组合）。此表显示某些游戏的玩家活动。
每一行都是一个玩家的记录，该玩家登录并玩了若干游戏（可能为 0），然后在某天使用某种设备注销。
请写一个解决方案，以报告在首次登录后的第二天再次登录的玩家比例，四舍五入到小数点后 2 位。换句话说，您需要计算从首次登录日期开始至少连续两天登录的玩家人数，然后用该人数除以玩家总数。
结果格式如下。
Example 1:
Input:
Activity table:
| player_id | device_id | event_date | games_played |
|---|---|---|---|
| 1 | 2 | 2016-03-01 | 5 |
| 1 | 2 | 2016-03-02 | 6 |
| 2 | 3 | 2017-06-25 | 1 |
| 3 | 1 | 2016-03-02 | 0 |
| 3 | 4 | 2018-07-03 | 5 |
Output:
| fraction |
|---|
| 0.33 |
Explanation:
只有id为1的玩家在登录第一天后重新登录，因此答案为 1/3 = 0.33
解决方案
SELECT CAST(
-- using DISTINCT to avoid double counting
COUNT(DISTINCT a2.player_id)*1.0/COUNT(DISTINCT a1.player_id)
AS DECIMAL(3,2)
) AS fraction
FROM (
-- get the first-logged-in date of each player
SELECT player_id, MIN(event_date) AS event_date
FROM Activity
GROUP BY player_id
) a1
-- if a player logged back in on the day right after the first-logged-in date,
-- he/she would get a matched record from table a2
LEFT JOIN Activity a2
ON a1.player_id = a2.player_id AND DATEADD(day,1,a1.event_date) = a2.event_date;
27. 拥有至少 5 名直接下属的经理
Table: Employee
| Column Name | Type |
|---|---|
| id | int |
| name | varchar |
| department | varchar |
| managerId | int |
id 是该表的主键（具有唯一值的列）。
该表的每一行都显示了员工姓名、部门及其经理的 id。
如果 managerId 为空，则表示该员工没有经理。
没有员工会成为自己的经理。
编写一个解决方案，查找至少有五名直接下属的经理。
以任意顺序返回结果表。
结果格式如下。
Example 1:
Input:
Employee table:
| id | name | department | managerId |
|---|---|---|---|
| 101 | John | A | null |
| 102 | Dan | A | 101 |
| 103 | James | A | 101 |
| 104 | Amy | A | 101 |
| 105 | Anne | A | 101 |
| 106 | Ron | B | 101 |
Output:
| name |
|---|
| John |
解决方案
SELECT e2.name
FROM employee e1
JOIN employee e2
ON e1.managerid = e2.id
GROUP BY e2.id, e2.name
HAVING COUNT(e1.id) >= 5;
28. 赢家候选
Table: Candidate
| id | Name |
|---|---|
| 1 | A |
| 2 | B |
| 3 | C |
| 4 | D |
| 5 | E |
Table: Vote
| id | CandidateId |
|---|---|
| 1 | 2 |
| 2 | 4 |
| 3 | 3 |
| 4 | 2 |
| 5 | 5 |
id 是自动递增主键、
CandidateId 是候选人表中出现的 id。
编写一个 sql 查找获胜候选人的姓名，上面的示例将返回获胜者 B。
| Name |
|---|
| B |
Notes:
你可以假设没有平局，换句话说，最多会有一个获胜的候选人。
解决方案
SELECT TOP 1 c.Name
FROM Candidate c
JOIN Vote v
ON c.id = v.CandidateId
GROUP BY c.id, c.Name
ORDER BY COUNT(*) DESC;
29.
Table: Employee
| Column Name | Type |
|---|---|
| empId | int |
| name | varchar |
| supervisor | int |
| salary | int |
empId 是该表唯一值列。
该表的每一行都显示了雇员的姓名和 ID，以及他们的工资和经理 ID。
Table: Bonus
| Column Name | Type |
|---|---|
| empId | int |
| bonus | int |
empId 是该表的唯一值列。
empId 是雇员表中 empId 的外键（引用列）。
该表的每一行都包含一名雇员的 id 和他们各自的奖金。
编写一个解决方案，报告奖金少于 1000 的每位员工的姓名和奖金数额。
以任意顺序返回结果表。
结果格式如下例所示。
Example 1:
Input:
Employee table:
| empId | name | supervisor | salary |
|---|---|---|---|
| 3 | Brad | null | 4000 |
| 1 | John | 3 | 1000 |
| 2 | Dan | 3 | 2000 |
| 4 | Thomas | 3 | 4000 |
Bonus table:
| empId | bonus |
|---|---|
| 2 | 500 |
| 4 | 2000 |
Output:
| name | bonus |
|---|---|
| Brad | null |
| John | null |
| Dan | 500 |
解决方案
SELECT e.name, b.bonus
FROM Employee e
LEFT JOIN Bonus b
ON e.empId = b.empId
-- when employee has no bonus, his/her bonus will be null after left join
WHERE b.bonus IS NULL
OR b.bonus < 1000;
30. 统计各系学生人数
某大学使用两个数据表（学生表和院系表）来存储有关学生和各专业相关院系的数据。
编写一个查询，打印系表中所有系（即使是没有在校学生的系）各自的系名和每个系的学生人数。
按学生人数从多到少排序；如果两个或多个系的学生人数相同，则按系名的字母顺序排序。
学生表如下：
| Column Name | Type |
|---|---|
| student_id | Integer |
| student_name | String |
| gender | Character |
| dept_id | Integer |
其中 student_id 是学生的 ID 编号，student_name 是学生的姓名，gender 是学生的性别，dept_id 是与学生申报专业相关的系 ID。
系表描述如下：
| Column Name | Type |
|---|---|
| dept_id | Integer |
| dept_name | String |
其中，dept_id 是部门的 ID 编号，dept_name 是部门名称。
下面是一个输入示例：
学生表：
| student_id | student_name | gender | dept_id |
|---|---|---|---|
| 1 | Jack | M | 1 |
| 2 | Jane | F | 1 |
| 3 | Mark | M | 2 |
department table:
| dept_id | dept_name |
|---|---|
| 1 | Engineering |
| 2 | Science |
| 3 | Law |
The Output should be:
| dept_name | student_number |
|---|---|
| Engineering | 2 |
| Science | 1 |
| Law | 0 |
解决方案
SELECT d.dept_name, COUNT(student_id) AS student_number
FROM department d
LEFT JOIN student s
ON d.dept_id = s.dept_id
GROUP BY d.dept_id, d.dept_name
ORDER BY student_number DESC, d.dept_name;
31. 销售人员
Table: SalesPerson
| Column Name | Type |
|---|---|
| sales_id | int |
| name | varchar |
| salary | int |
| commission_rate | int |
| hire_date | date |
sales_id 是该表的主键（具有唯一值的列）。
该表的每一行都显示销售人员的姓名和 ID，以及他们的工资、佣金率和雇用日期。
Table: Company
| Column Name | Type |
|---|---|
| com_id | int |
| name | varchar |
| city | varchar |
com_id 是该表的主键（具有唯一值的列）。
该表中的每一行都表示一家公司的名称和 ID 以及该公司所在的城市。
Table: Orders
| Column Name | Type |
|---|---|
| order_id | int |
| order_date | date |
| com_id | int |
| sales_id | int |
| amount | int |
order_id 是该表的主键（具有唯一值的列）。
com_id 是公司表中 com_id 的外键（引用列）。
sales_id 是销售人员表中 sales_id 的外键（引用列）。
该表的每一行都包含一个订单的信息。其中包括公司 ID、销售人员 ID、订单日期和支付金额。
编写一个解决方案，找出与名称为 "RED "的公司没有任何订单的所有销售人员的姓名。
按任意顺序返回结果表。
结果格式见下例。
Example 1:
Input:
SalesPerson table:
| 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 table:
| com_id | name | city |
|---|---|---|
| 1 | RED | Boston |
| 2 | ORANGE | New York |
| 3 | YELLOW | Boston |
| 4 | GREEN | Austin |
Orders table:
| 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 |
Output:
| name |
|---|
| Amy |
| Mark |
| Alex |
解决方案
SELECT name
FROM salesperson
WHERE sales_id NOT IN (
SELECT o.sales_id
FROM orders o
JOIN company c
ON o.com_id = c.com_id
WHERE c.name = 'RED'
);
32. 二级追随者
Table: Follow
| Column Name | Type |
|---|---|
| followee | varchar |
| follower | varchar |
(followee，follower）是该表的主键（具有唯一值的列组合）。
该表中的每一行都表示用户关注者在社交网络上关注了用户被关注者。
不会出现用户关注自己的情况。
二级关注者是指具备以下条件的用户
- 至少关注一个用户，且
- 被至少一个用户关注。
编写一个解决方案，报告二级用户及其关注者的数量。
按关注者的字母顺序返回结果表。
结果格式如下。
Example 1:
Input:
Follow table:
| followee | follower |
|---|---|
| Alice | Bob |
| Bob | Cena |
| Bob | Donald |
| Donald | Edward |
Output:
| follower | num |
|---|---|
| Bob | 2 |
| Donald | 1 |
Explanation:
用户 Bob 有 2 个关注者。鲍勃是二级关注者，因为他关注了爱丽丝，所以我们把他包括在结果表中。
用户 Donald 有 1 个关注者。唐纳德是二级关注者，因为他关注了鲍勃，所以我们将他包含在结果表中。
用户 Alice 有 1 个关注者。爱丽丝不是二级关注者，因为她没有关注任何人，所以我们没有将她包括在结果表中。
解决方案
SELECT f1.follower, COUNT(DISTINCT f2.follower) AS num
FROM follow f1
JOIN follow f2
ON f1.follower = f2.followee
GROUP BY f1.follower
ORDER BY f1.follower;
33. 平均工资：部门 VS 公司
给定以下两个表格，编写一个查询，以显示部门员工平均工资与公司平均工资的比较结果（较高/较低/相同）。
Table: salary
| id | employee_id | amount | pay_date |
|---|---|---|---|
| 1 | 1 | 9000 | 2017-03-31 |
| 2 | 2 | 6000 | 2017-03-31 |
| 3 | 3 | 10000 | 2017-03-31 |
| 4 | 1 | 7000 | 2017-02-28 |
| 5 | 2 | 6000 | 2017-02-28 |
| 6 | 3 | 8000 | 2017-02-28 |
employee_id 列指的是下表 employee 中的 employee_id。
| employee_id | department_id |
|---|---|
| 1 | 1 |
| 2 | 2 |
| 3 | 2 |
因此，上述样本数据的结果是
| pay_month | department_id | comparison |
|---|---|---|
| 2017-03 | 1 | higher |
| 2017-03 | 2 | lower |
| 2017-02 | 1 | same |
| 2017-02 | 2 | same |
Explanation：
三月份，公司的平均工资为 (9000+6000+10000)/3 = 8333.33…
部门’1’的平均工资是 9000，也就是员工编号’1’的工资，因为该部门只有一名员工。因此，比较结果是 “更高”，因为 9000 显然大于 8333.33。
部门’2’的平均工资为 (6000 + 10000)/2 = 8000，这是员工编号’2’和’3’的平均工资。因此，比较结果是 “较低”，因为 8000 < 8333.33。
用同样的公式比较二月份的平均工资，结果是 “相同”，因为部门 "1 "和 "2 "与公司的平均工资相同，都是 7000。
解决方案
-- If need to extract date in other format in the future, FORMAT() function could be used
-- Solution 1: Join, Window Function, Subquery, CASE WHEN
WITH tb1 AS (
SELECT DISTINCT department_id, LEFT(pay_date,7) AS pay_month,
AVG(amount) OVER (PARTITION BY department_id, LEFT(pay_date,7))AS avg_dept,
AVG(amount) OVER (PARTITION BY LEFT(pay_date,7)) AS avg_comp
FROM salary s
JOIN employee e
ON s.employee_id = e.employee_id
)
SELECT pay_month, department_id,
CASE
WHEN avg_dept > avg_comp THEN 'higher'
WHEN avg_dept < avg_comp THEN 'lower'
ELSE 'same'
END AS comparison
FROM tb1;
-- Solution 2: Join, Subquery, CASE WHEN
WITH dept AS (
SELECT e.department_id, LEFT(s.pay_date,7) AS pay_month, AVG(s.amount) AS avg_dept
FROM salary s
JOIN employee e
ON s.employee_id = e.employee_id
GROUP BY e.department_id, LEFT(s.pay_date,7)
),
comp AS (
SELECT LEFT(pay_date,7) AS pay_month, AVG(amount) AS avg_comp
FROM salary
GROUP BY LEFT(pay_date,7)
)
SELECT d.department_id, d.pay_month,
CASE
WHEN d.avg_dept > c.avg_comp THEN 'higher'
WHEN d.avg_dept < c.avg_comp THEN 'lower'
ELSE 'same'
END AS comparison
FROM dept d
JOIN comp c
ON d.pay_month = c.pay_month;
