SQL实现LeetCode(197.上升温度)

[LeetCode] 197.Rising Temperature 上升温度

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) | Date(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 |
+----+

这道题给了我们一个Weather表,让我们找出比前一天温度高的Id,由于Id的排列未必是按顺序的,所以我们要找前一天就得根据日期来找,我们可以使用MySQL的函数Datadiff来计算两个日期的差值,我们的限制条件是温度高且日期差1,参见代码如下: 

解法一:

SELECT w1.Id FROM Weather w1, Weather w2
WHERE w1.Temperature > w2.Temperature AND DATEDIFF(w1.Date, w2.Date) = 1;

下面这种解法我们使用了MySQL的TO_DAYS函数,用来将日期换算成天数,其余跟上面相同:

解法二:

SELECT w1.Id FROM Weather w1, Weather w2
WHERE w1.Temperature > w2.Temperature AND TO_DAYS(w1.Date) = TO_DAYS(w2.Date) + 1;

我们也可以使用Subdate函数,来实现日期减1,参见代码如下:

解法三:

SELECT w1.Id FROM Weather w1, Weather w2
WHERE w1.Temperature > w2.Temperature AND SUBDATE(w1.Date, 1) = w2.Date;

最后来一种完全不一样的解法,使用了两个变量pre_t和pre_d分别表示上一个温度和上一个日期,然后当前温度要大于上一温度,且日期差为1,满足上述两条件的话选出来为Id,否则为NULL,然后更新pre_t和pre_d为当前的值,最后选出的Id不为空即可:

解法四:

SELECT Id FROM (
SELECT CASE WHEN Temperature > @pre_t AND DATEDIFF(Date, @pre_d) = 1 THEN Id ELSE NULL END AS Id,
@pre_t := Temperature, @pre_d := Date
FROM Weather, (SELECT @pre_t := NULL, @pre_d := NULL) AS init ORDER BY Date ASC
) id WHERE Id IS NOT NULL;

参考资料:

https://leetcode.com/discuss/33641/two-solutions

https://leetcode.com/discuss/52370/my-simple-solution-using-inner-join

https://leetcode.com/discuss/86435/a-simple-straightforward-solution-and-its-very-fast

到此这篇关于SQL实现LeetCode(197.上升温度)的文章就介绍到这了,更多相关SQL实现上升温度内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • SQL实现LeetCode(184.系里最高薪水)

    [LeetCode] 184.Department Highest Salary 系里最高薪水 The Employee table holds all employees. Every employee has an Id, a salary, and there is also a column for the department Id. +----+-------+--------+--------------+ | Id | Name  | Salary | DepartmentId

  • SQL实现LeetCode(182.重复的邮箱)

    [LeetCode] 182.Duplicate Emails 重复的邮箱 Write a SQL query to find all duplicate emails in a table named Person. +----+---------+ | Id | Email   | +----+---------+ | 1  | a@b.com | | 2  | c@d.com | | 3  | a@b.com | +----+---------+ For example, your que

  • SQL实现LeetCode(181.员工挣得比经理多)

    [LeetCode] 181.Employees Earning More Than Their Managers 员工挣得比经理多 The Employee table holds all employees including their managers. Every employee has an Id, and there is also a column for the manager Id. +----+-------+--------+-----------+ | Id | Na

  • C++实现LeetCode(179.最大组合数)

    [LeetCode] 179. Largest Number 最大组合数 Given a list of non negative integers, arrange them such that they form the largest number. Example 1: Input: [10,2] Output: "210" Example 2: Input: [3,30,34,5,9] Output: "9534330" Note: The result

  • SQL实现LeetCode(180.连续的数字)

    [LeetCode] 180.Consecutive Numbers 连续的数字 Write a SQL query to find all numbers that appear at least three times consecutively. +----+-----+ | Id | Num | +----+-----+ | 1  |  1  | | 2  |  1  | | 3  |  1  | | 4  |  2  | | 5  |  1  | | 6  |  2  | | 7  |

  • SQL实现LeetCode(196.删除重复邮箱)

    [LeetCode] 196.Delete Duplicate Emails 删除重复邮箱 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            | +----+------------

  • SQL实现LeetCode(185.系里前三高薪水)

    [LeetCode] 185.Department Top Three Salaries 系里前三高薪水 The Employee table holds all employees. Every employee has an Id, and there is also a column for the department Id. +----+-------+--------+--------------+ | Id | Name  | Salary | DepartmentId | +--

  • SQL实现LeetCode(183.从未下单订购的顾客)

    [LeetCode] 183.Customers Who Never Order 从未下单订购的顾客 Suppose that a website contains two tables, the Customers table and the Orders table. Write a SQL query to find all customers who never order anything. Table: Customers. +----+-------+ | Id | Name  |

  • SQL实现LeetCode(197.上升温度)

    [LeetCode] 197.Rising Temperature 上升温度 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) | Date(DATE) | Temperat

  • SQL实现LeetCode(178.分数排行)

    [LeetCode] 178.Rank Scores 分数排行 Write a SQL query to rank scores. If there is a tie between two scores, both should have the same ranking. Note that after a tie, the next ranking number should be the next consecutive integer value. In other words, th

  • SQL实现LeetCode(177.第N高薪水)

    [LeetCode] 177.Nth Highest Salary 第N高薪水 Write a SQL query to get the nth highest salary from the Employee table. +----+--------+ | Id | Salary | +----+--------+ | 1  | 100    | | 2  | 200    | | 3  | 300    | +----+--------+ For example, given the ab

  • SQL实现LeetCode(176.第二高薪水)

    [LeetCode] 176.Second Highest Salary 第二高薪水 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

  • SQL实现LeetCode(175.联合两表)

    [LeetCode] 175.Combine Two Tables 联合两表 Table: Person +-------------+---------+ | Column Name | Type    | +-------------+---------+ | PersonId    | int     | | FirstName   | varchar | | LastName    | varchar | +-------------+---------+ PersonId is the

随机推荐