Leetcode:rising Temperature-Floating temperature

1. Title

Rising temperature (floating temperature)

2. Address of the topic

Https://leetcode.com/problems/rising-temperature

3. Topic content

Give a set of daily temperature data to return the day when the temperature is higher than yesterday's temperature.

For example, the data in the weather table is as follows:

+---------+------------+------------------+| 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 | |+---------+------------+------------------+

The return result should be:

+----+|  Id |+----+|  2 | | 4 |+----+

4. Initializing Database Scripts

Create a database named Leetcode in the MySQL database and execute the following script with the source command in the MySQL command line:

--  must establish a databaseuse leetcode; drop table if exists weather; named Leetcode before executing the script create table weather  (  Id INT NOT NULL PRIMARY KEY,   date date,  temperature int);insert into weather  (Id, Date,  temperature)  VALUES  (1,  ' 2015-01-01 ',  10);insert into weather  (Id,  date, temperature)  VALUES  (2,  ' 2015-01-02 ',  25); insert into weather   (id, date, temperature)  VALUES  (3,  ' 2015-01-03 ',  20); Insert into  Weather  (id, date, temperature)  VALUES  (4,  ' 2015-01-04 ',  30); INSERT  INTO Weather  (id, date, temperature)  VALUES  (5,  ' 1998-10-31 ',  29 );insert into weather  (id, date, temperature)  VALUES  (6,  ' 1998-11-01 ',  30);

5. Solving SQL

Before giving the correct SQL statement, give me a wrong way to write it:

SELECT W2. Idfrom Weather W1, Weather w2where W1. Date = W2. Date-1 and W1. Temperature < W2. temperature;

The reason for this error is that the date type of data minus one does not produce the correct result, such as the result of November 1, 1998 minus one is 19981100, rather than the October 31, 1998 we would like to see:

The correct wording should be to use the DATEDIFF function to solve the problem of increasing the date, the following two kinds of writing can be AC:

1) Notation 1

SELECT W2. Idfrom Weather W1, Weather w2where DATEDIFF (W2. Date, W1. Date) = 1 and W1. Temperature < W2. temperature;

2) Notation 2

SELECT W2. Idfrom Weather w1join Weather W2 on DATEDIFF (W2. Date, W1. Date) = 1WHERE W1. Temperature < W2. Temperature

END

Leetcode:rising Temperature-Floating temperature