一题: Rising Temperature
中文:
给定一个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 |
+----+
英文:
Given a Weather
table, write a SQL query to find all dates' Ids with higher temperature compared to its previous (yesterday's) dates.
+---------+------------------+------------------+
| 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 |
+---------+------------------+------------------+
For example, return the following Ids for the above Weather
table:
+----+
| Id |
+----+
| 2 |
| 4 |
+----+
解题思路:
使用自身表关联,通过DATEDIFF()函数将自身信息和前一天的信息进行关联。并且温度高于前一天的温度。
# 创建一个天气表
Create table If Not Exists Weather (Id int, RecordDate date, Temperature int);
# 删除Weather表中的所有数据,类似于delete from Weather
Truncate table Weather;
# 往天气表中插入数据
insert into Weather (Id, RecordDate, Temperature) values ('1', '2015-01-01', '10');
insert into Weather (Id, RecordDate, Temperature) values ('2', '2015-01-02', '25');
insert into Weather (Id, RecordDate, Temperature) values ('3', '2015-01-03', '20');
insert into Weather (Id, RecordDate, Temperature) values ('4', '2015-01-04', '30');
insert into Weather (Id, RecordDate, Temperature) values ('5', '2014-01-04', '23');
insert into Weather (Id, RecordDate, Temperature) values ('6', '2014-01-05', '30');
# num:197
# 解题方法 :
# =============================
# 使用自身关联,并使用DATEDIFF() 函数查询 差为1天(即今天和昨天),且温度高于前一天的数据
SELECT
A.Id Id
FROM
Weather A,
Weather B
WHERE
DATEDIFF( A.RecordDate, B.RecordDate ) = 1
AND A.Temperature > B.Temperature;
二题: Delete Duplicate Emails
中文:
写一个SQL语句,删除名为person的表中所有重复的电子邮件条目,只保留基于其最小ID的唯一电子邮件。
+----+------------------+ | Id | Email | +----+------------------+ | 1 | john@example.com | | 2 | bob@example.com | | 3 | john@example.com | +----+------------------+ Id 是此表的唯一主键列。
例如,运行查询后,上面的人员表应该有以下行:
+----+------------------+ | Id | Email | +----+------------------+ | 1 | john@example.com | | 2 | bob@example.com | +----+------------------+ 注意: 您的输出是执行SQL后的整个person表。使用删除语句。
英文:
Write a SQL query to delete all duplicate email entries in a table named Person
, keeping only unique emails based on its smallest Id.
+----+------------------+ | Id | Email | +----+------------------+ | 1 | john@example.com | | 2 | bob@example.com | | 3 | john@example.com | +----+------------------+ Id is the primary key column for this table.
For example, after running your query, the above Person
table should have the following rows:
+----+------------------+ | Id | Email | +----+------------------+ | 1 | john@example.com | | 2 | bob@example.com | +----+------------------+
Note:
Your output is the whole Person
table after executing your sql. Use delete
statement.
解题思路:
由于题目写的是删除重复的email,并保留Id更小的那一个。
所以我们使用表自身使用email关联,并找到p1表Id>p2表Id的数据,该条数据为要删除的数据。
# 如果不存在Person表,则创建Person表
CREATE TABLE
IF
NOT EXISTS Person (Id INT, Email VARCHAR ( 50 ));
# 清除Person表中的全部数据
Truncate table Person;
# 向Person表中插入数据
insert into Person (Id, Email) values (1, 'john@example.com');
insert into Person (Id, Email) values (2, 'bob@example.com');
insert into Person (Id, Email) values (3, 'john@example.com');
# num : 196题
# 解题方法:
# =======================================
# 由于题目写的是删除重复的email,并保留Id更小的那一个。
# 所以我们使用表自身使用email关联,并找到p1表Id>p2表Id的数据,该条数据为要删除的数据。
# 如下SELECT 到的数据为待删除的数据
SELECT p1.*
FROM Person p1,
Person p2
WHERE
p1.Email = p2.Email
AND
p1.Id > p2.Id;
# 完整的删除语句,使用上面的条件进行删除
DELETE p1 FROM Person p1,
Person p2
WHERE
p1.Email = p2.Email AND p1.Id > p2.Id;
三题: Second Highest Salary
中文:
编写SQL查询以从Employeetable获得第二高的薪水。
+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
例如,给定上面的Employee表,查询应返回200作为第二高薪。如果没有第二高工资,则查询应返回null。
+---------------------+
| SecondHighestSalary |
+---------------------+
| 200 |
+---------------------+
英文:
Write a SQL query to get the second highest salary from the Employee
table.
+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
For example, given the above Employee table, the query should return 200
as the second highest salary. If there is no second highest salary, then the query should return null
.
+---------------------+
| SecondHighestSalary |
+---------------------+
| 200 |
+---------------------+
解题思路:
根据Salary进行由大到小的排序,使用DISTINCT关键字去重,并使用Limit截取排序完排在第二位的数据。
如果没有排序第二的Salary,比如表中只有一个数据,根据下图提示要求,要返回null,而不是返回空,所以我们再加一层子查询。
# 如果不存在Employee员工表,则创建一个员工表
Create table If Not Exists Employee (Id int, Salary int);
# 清除Employee表中的全部数据
Truncate table Employee;
# 向Employee表中插入数据
insert into Employee (Id, Salary) values ('1', '100');
insert into Employee (Id, Salary) values ('2', '200');
insert into Employee (Id, Salary) values ('3', '300');
# num: 176题
# 解题方法 :
# =================================
# 初始sql,使用DISTINCT关键字去重
SELECT DISTINCT
Salary AS SecondHighestSalary
FROM
employee
# 使用ORDER BY salary DESC 语句根据薪资由大到小进行排序
ORDER BY
Salary DESC
# 从第一个开始,选择一个,表示排名第二高的薪水
LIMIT 1 OFFSET 1;
# 由于如果没有排序第二的Salary,比如表中只有一个数据,要返回null,而不是返回空,所以我们再加一层子查询进行优化。
SELECT (SELECT DISTINCT Salary FROM employee ORDER BY Salary DESC LIMIT 1 OFFSET 1) AS SecondHighestSalary ;