程序员人生 网站导航

Rising Temperature

栏目:数据库应用时间:2015-06-04 07:51:18
    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⑴2⑴5 |           3 |
            |    2 | 2000⑴2⑴6 |           5 |
            |    3 | 2000⑴2⑴7 |           1 |
            |    4 | 2000⑴2⑴4 |           5 |
            |    5 | 2000⑴2⑴8 |           0 |
            +------+------------+-------------+
        2、排序
            mysql> select * from Weather order by date;
            +------+------------+-------------+
            | id   | date       | temperature |
            +------+------------+-------------+
            |    4 | 2000⑴2⑴4 |           5 |
            |    1 | 2000⑴2⑴5 |           3 |
            |    2 | 2000⑴2⑴6 |           5 |
            |    3 | 2000⑴2⑴7 |           1 |
            |    5 | 2000⑴2⑴8 |           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⑴2⑴4 |
            |    1 |        1 |           3 |       ⑵ |       3 | 2000⑴2⑴5 |
            |    2 |        1 |           5 |        2 |       5 | 2000⑴2⑴6 |
            |    3 |        1 |           1 |       ⑷ |       1 | 2000⑴2⑴7 |
            |    5 |        1 |           0 |       ⑴ |       0 | 2000⑴2⑴8 |
            +------+----------+-------------+----------+---------+------------+ 
        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 |
            +------+
------分隔线----------------------------
------分隔线----------------------------

最新技术推荐