心得

題目要求找出溫度比前一天高的資料，這裡用了DATE_SUB與TO_DAYS兩種方式來解答。

題目

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

答案

1. Sub Query

   # Write your MySQL query statement below
   SELECT  a.Id
   FROM    Weather AS a
   WHERE   (   SELECT  z.Temperature
               FROM    Weather AS z
               WHERE   DATE_SUB(a.Date, INTERVAL 1 DAY) = z.Date) < a.Temperature

2. Join

   # Write your MySQL query statement below
   SELECT  a.Id
   FROM    Weather AS a
   JOIN    Weather AS b
   ON      DATE_SUB(a.Date, INTERVAL 1 DAY) = b.Date
   WHERE   a.Temperature > b.Temperature

3. TO_DAYS

   # Write your MySQL query statement below
   SELECT  a.Id
   FROM    Weather AS a
   JOIN    Weather AS b
   ON      TO_DAYS(a.Date) - TO_DAYS(b.Date) = 1
   WHERE   a.Temperature > b.Temperature

    

參考：

1. DATE_ADD() 與 DATE_SUB() 日期的加法與減法
2. Mysql日期和時間函數不求人