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