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

---

思路：
（注：to_days(datex)将datex转换成int可进行大小比较的类型）
思路一：
这是最基本最首先应该想到的思路：从一个表中选择一些special的元组，我们要通过where来对其拥有的属性进行筛选，符合条件的才能够选择出来。在这题里面就是选出“Temperature”这个属性值大于
“Date”属性值比自己“Date”属性值小1的那个元组的“Temperature”属性值。然后进行一次嵌套选择即OK
思路二：
这是角度和思路一稍微不同的一种解法，我的理解是这种解法是通过“空间”上的复杂度来避免了嵌套select带来的时间上的复杂度，至于二者究竟谁更快现在还不太清楚。

---

思路一：

select a.Id from Weather as a where a.Temperature > (select b.Temperature from Weather as b where to_days(b.Date)+1 = to_days(a.Date))

 思路二：

select b.Id
from Weather as a,Weather as b
where TO_DAYS(a.Date) +1 = TO_DAYS(b.Date) and a.Temperature < b.Temperature

转载于:https://www.cnblogs.com/immortal-worm/p/5088178.html