Simple Solution


  • 59
    SELECT wt1.Id 
    FROM Weather wt1, Weather wt2
    WHERE wt1.Temperature > wt2.Temperature AND 
          TO_DAYS(wt1.DATE)-TO_DAYS(wt2.DATE)=1;
    

    EXPLANATION:

    TO_DAYS(wt1.DATE) return the number of days between from year 0 to date DATE
    TO_DAYS(wt1.DATE)-TO_DAYS(wt2.DATE)=1 check if wt2.DATE is yesterday respect to wt1.DATE

    We select from the joined tables the rows that have

    wt1.Temperature > wt2.Temperature

    and difference between dates in days of 1 (yesterday):

    TO_DAYS(wt1.DATE)-TO_DAYS(wt2.DATE)=1;


  • 9
    M

    Just for sharing. Interesting function subdate().

    SELECT w1.Id FROM Weather w1, Weather w2
    WHERE subdate(w1.Date, 1)=w2.Date AND w1.Temperature>w2.Temperature

  • 0
    L

    Please see my answers


  • 4
    L

    select a.id from Weather a inner join Weather b on datediff(a.Date, b.Date) = 1 and a.Temperature>b.Temperature;


  • 2
    K

    syntactic sugar version for your reference.

    select cur.Id
    from Weather cur
    join Weather prev
    on prev.Date + interval 1 day = cur.Date
    where cur.Temperature > prev.Temperature
    

  • 1
    Z

    the problem is when you put field in function you can't use index


  • 1
    D
    select w1.Id 
    from Weather w1
    where w1.Temperature > (select w2.Temperature from Weather w2 where DATE_SUB(w1.Date,INTERVAL 1 DAY) = w2.Date);
    

  • 0
    S

    My solution:

    select w1.Id
    from Weather w1 left join Weather w2
    on w1.date = w2.date + interval 1 day
    where w1.Temperature > w2.Temperature


  • 2
    L
    mysql> select * FROM Weather;
    +----+------------+-------------+
    | Id | Date       | Temperature |
    +----+------------+-------------+
    |  1 | 2015-01-01 |          10 |
    |  2 | 2015-01-02 |          25 |
    |  3 | 2015-01-03 |          20 |
    |  4 | 2015-01-04 |          30 |
    +----+------------+-------------+
    4 rows in set (0.00 sec)
    
    mysql> SELECT w2.Id, w2.Date, w1.Id, w1.Date 
        -> FROM Weather w1,Weather w2
        -> WHERE w2.Date = DATE_ADD(w1.Date, INTERVAL 1 DAY);
    +----+------------+----+------------+
    | Id | Date       | Id | Date       |
    +----+------------+----+------------+
    |  2 | 2015-01-02 |  1 | 2015-01-01 |
    |  3 | 2015-01-03 |  2 | 2015-01-02 |
    |  4 | 2015-01-04 |  3 | 2015-01-03 |
    +----+------------+----+------------+
    3 rows in set (0.00 sec)
    
    mysql> SELECT w2.Id, w2.Date, w1.Id, w1.Date  FROM Weather w1,Weather w2 WHERE w2.Date = DATE_ADD(w1.Date, INTERVAL 1 DAY) AND w2.Temperature > w1.Temperature;
    +----+------------+----+------------+
    | Id | Date       | Id | Date       |
    +----+------------+----+------------+
    |  2 | 2015-01-02 |  1 | 2015-01-01 |
    |  4 | 2015-01-04 |  3 | 2015-01-03 |
    +----+------------+----+------------+
    2 rows in set (0.01 sec)
    

    so this can work

    SELECT w2.Id  FROM Weather w1,Weather w2 
    WHERE w2.Date = DATE_ADD(w1.Date, INTERVAL 1 DAY) 
    AND w2.Temperature > w1.Temperature;
    

  • 0
    V

    @lvxinwei datediff(a.Date,b.Date)=1 means a.Date is earlier than b.Date , right ? e.g: a.Date = 2016-10-29, b.Date=2016-10-30 . How come it works ?


  • 0
    select Id
    from Weather w
    where exists (select 1 from Weather iw where TO_DAYS(w.DATE)-TO_DAYS(iw.DATE)=1 and iw.Temperature < w.Temperature)
    

  • 0
    S

    @lalahahaane This solution got "Time Limit Exceeded", just don't know why.


  • 0
    S

    @fabrizio3 neat solution. However I find lacking of window function in MySQL really cumbersome


Log in to reply
 

Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.