【MySQL LeetCode】102题学习整理中

---

1. 重复的电子邮件：

编写一个解决方案来报告所有重复的电子邮件。请注意，保证电子邮件字段不为 NULL。以任意顺序返回结果表。结果格式如下例所示。
示例1：
输入:
Person table:

id	email
1	a@b.com
2	c@d.com
3	a@b.com

输出:

Email
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 值等信息。

如果出现以下情况，一个国家就是大国

1. 面积至少有 300 万（即 3000000 平方公里），或
2. 人口至少有两千五百万（即 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: