RisingTemperature
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-12-15 | 3 |
| 2 | 2000-12-16 | 5 |
| 3 | 2000-12-17 | 1 |
| 4 | 2000-12-14 | 5 |
| 5 | 2000-12-18 | 0 |
+------+------------+-------------+
2、排序
mysql> select * from Weather order by date;
+------+------------+-------------+
| id | date | temperature |
+------+------------+-------------+
| 4 | 2000-12-14 | 5 |
| 1 | 2000-12-15 | 3 |
| 2 | 2000-12-16 | 5 |
| 3 | 2000-12-17 | 1 |
| 5 | 2000-12-18 | 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-12-14 |
| 1 | 1 | 3 | -2 | 3 | 2000-12-15 |
| 2 | 1 | 5 | 2 | 5 | 2000-12-16 |
| 3 | 1 | 1 | -4 | 1 | 2000-12-17 |
| 5 | 1 | 0 | -1 | 0 | 2000-12-18 |
+------+----------+-------------+----------+---------+------------+
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 |
+------+
