Rising Temperature，risingtemperature

Rising Temperature，risingtemperature

    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 |    +----+    思想：需要知道兩個變數，首先是前後兩個date的差，然後是前後兩個date之間的溫差。        1、select * from weather            +------+------------+-------------+            | id   | date       | temperature |            +------+------------+-------------+            |    1 | 2000-12-15 |           3 |            |    2 | 2000-12-16 |           5 |            |    3 | 2000-12-17 |           1 |            |    4 | 2000-12-14 |           5 |            |    5 | 2000-12-18 |           0 |            +------+------------+-------------+        2、排序            mysql> select * from Weather order by date;            +------+------------+-------------+            | id   | date       | temperature |            +------+------------+-------------+            |    4 | 2000-12-14 |           5 |            |    1 | 2000-12-15 |           3 |            |    2 | 2000-12-16 |           5 |            |    3 | 2000-12-17 |           1 |            |    5 | 2000-12-18 |           0 |            +------+------------+-------------+        3、擷取溫度差和時間差        select id,datediff(date,@predate) as datediff,temperature,@tempdiff:=temperature-@pretemp as 'tempdiff',@pretemp:=temperature as 'pretemp' ,@predate:=date  as 'predate' from (select * from Weather order by date ) t,(select @predate := '1000-01-01') l,(select @pretemp:=0) pret;            +------+----------+-------------+----------+---------+------------+            | id   | datediff | temperature | tempdiff | pretemp | predate    |            +------+----------+-------------+----------+---------+------------+            |    4 |   365590 |           5 |        5 |       5 | 2000-12-14 |            |    1 |        1 |           3 |       -2 |       3 | 2000-12-15 |            |    2 |        1 |           5 |        2 |       5 | 2000-12-16 |            |    3 |        1 |           1 |       -4 |       1 | 2000-12-17 |            |    5 |        1 |           0 |       -1 |       0 | 2000-12-18 |            +------+----------+-------------+----------+---------+------------+         4、提取id        select id  from (            select * from(                select id,datediff(date,@predate) as diff,temperature,@tempdiff:=temperature-@pretemp as 'tempdiff',@pretemp:=temperature as 'pretemp' ,@predate:=date  as 'predate'                from (select * from Weather order by date ) h            ) t,(select @predate := '1000-01-01') l,(select @pretemp:=0) pret) w             where w.diff=1 and w.tempdiff>0;            +------+            | id   |            +------+            |    2 |            +------+