每日一道Leetcode算法——sql题——2019.02.26

一题：  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 Employeetable.

+----+--------+
| 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 ;