Simple Solution


  • 56
    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.


Log in to reply
 

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