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 |
    +----+
    思想：需要知道两个变量，首先是前后两个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 |
            +------+