数据库专题三
196.删除重复的电子邮箱
编写一个 SQL 查询，来删除 Person 表中所有重复的电子邮箱，重复的邮箱里只保留 Id 最小 的那个。
| Id | |
|---|---|
| 1 | john @example.com |
| 2 | bob @example.com |
| 3 | john @example.com |
Id 是这个表的主键。
例如，在运行你的查询语句之后，上面的 Person 表应返回以下几行:
| Id | |
|---|---|
| 1 | john @example.com |
| 2 | bob @example.com |
题目中提到了是删除重复邮箱，因此采用DELETE方法。
DELETE p1 -- 从p1中删除一些项
FROM Person p1
INNER JOIN Person p2 -- 连接p1 p2两表
ON p1.Email = p2.Email -- 连接条件：同邮箱
WHERE p1.Id > p2.Id -- 连接条件2：不是最小Id
197.上升的温度
给定一个 Weather 表，编写一个 SQL 查询，来查找与之前（昨天的）日期相比温度更高的所有日期的 Id。
| Id(INT) | RecordDate(DATE) | Temperature(INT) |
|---|---|---|
| 1 | 2015-01-01 | 10 |
| 2 | 2015-01-02 | 25 |
| 3 | 2015-01-03 | 20 |
| 4 | 2015-01-04 | 30 |
例如，根据上述给定的 Weather 表格，返回如下 Id:
| Id |
|---|
| 2 |
| 4 |
参考资料《【LeetCode--数据库】上升的温度》
使用TO_DAYS函数，把date类型改变为可计算的数字
SELECT w1.Id -- 建立表头
FROM Weather w1
INNER JOIN Weather w2 -- 内连接
ON TO_DAYS(w1.RecordDate) = TO_DAYS(w2.RecordDate) + 1 -- w1：今天;w2：昨天
WHERE w1.Temperature > w2.Temperature
262.行程和用户
Trips 表中存所有出租车的行程信息。每段行程有唯一键 Id，Client_Id 和 Driver_Id 是 Users 表中 Users_Id 的外键。Status 是枚举类型，枚举成员为 (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’)。
| 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 表存所有用户。每个用户有唯一键 Users_Id。Banned 表示这个用户是否被禁止，Role 则是一个表示（‘client’, ‘driver’, ‘partner’）的枚举类型。
| 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 |
写一段 SQL 语句查出 2013年10月1日 至 2013年10月3日 期间非禁止用户的取消率。基于上表，你的 SQL 语句应返回如下结果，取消率（Cancellation Rate）保留两位小数。
| Day | Cancellation Rate |
|---|---|
| 2013-10-01 | 0.33 |
| 2013-10-02 | 0.00 |
| 2013-10-03 | 0.50 |
首先按时间分组+筛选段时间内条目
接着找到没完成单号的
接着找到其中司机和乘客都没有被禁止的
SELECT DISTINCT t.Request_at AS DAY,
ROUND(COUNT(IF(t.Status != 'completed', TRUE, NULL)) / COUNT(*), 2) AS 'Cancellation Rate'
-- IF(A,B,C)，A是判断条件，B是A为真结果，C为A为假结果
-- ROUND（A，2），A保留2位小数
-- 由于表头存在空格，因此需要用单引号括起来
FROM Trips t
WHERE t.Request_at BETWEEN '2013-10-01' AND '2013-10-03'
-- between and 用来截取
-- 判断Client是否被禁
AND t.Client_Id IN (
SELECT Users_Id
FROM Users
WHERE Banned = 'No'
)
AND t.Driver_Id IN (
SELECT Users_Id
FROM Users
WHERE Banned = 'No'
)
-- 按时间分组
GROUP BY t.Request_at
595.大的国家
这里有张 World 表
| name | continent | area | population | gdp |
|---|---|---|---|---|
| Afghanistan | Asia | 652230 | 25500100 | 20343000 |
| Albania | Europe | 28748 | 2831741 | 12960000 |
| Algeria | Africa | 2381741 | 37100000 | 188681000 |
| Andorra | Europe | 468 | 78115 | 3712000 |
| Angola | Africa | 1246700 | 20609294 | 100990000 |
如果一个国家的面积超过300万平方公里，或者人口超过2500万，那么这个国家就是大国家。
编写一个SQL查询，输出表中所有大国家的名称、人口和面积。
例如，根据上表，我们应该输出:
| name | population | area |
|---|---|---|
| Afghanistan | 25500100 | 652230 |
| Algeria | 37100000 | 2381741 |
就是一个OR判断，两个符合的可能条件
SELECT name, population, area -- 建表头
FROM World w
WHERE population > 25000000 -- 判断依据1
OR area > 3000000 -- 判断依据2
596.超过5名学生的课
有一个courses 表 ，有: student (学生) 和 class (课程)。
请列出所有超过或等于5名学生的课。
例如,表:
| student | class |
|---|---|
| A | Math |
| B | English |
| C | Math |
| D | Biology |
| E | Math |
| F | Computer |
| G | Math |
| H | Math |
| I | Math |
应该输出:
| class |
|---|
| Math |
Note:
学生在每个课中不应被重复计算。
按课程分组，计数每组中不同学生个数
使用GROUP BY...HAVING组合
SELECT class -- 建表头
FROM courses -- 来源表
GROUP BY class -- 分组
HAVING COUNT(DISTINCT student) >= 5 -- 筛选依据
