需求：编写一个 SQL 查询，来查找与之前（昨天的）日期相比温度更高的所有日期的 Id。
1 Create table If Not Exists Weather (Id int, RecordDate date, Temperature int); 2 3 insert into Weather (Id, RecordDate, Temperature) values (1, ‘2015-01-01‘, 10); 4 insert into Weather (Id, RecordDate, Temperature) values (2, ‘2015-01-02‘, 25); 5 insert into Weather (Id, RecordDate, Temperature) values (3, ‘2015-01-03‘, 20); 6 insert into Weather (Id, RecordDate, Temperature) values (4, ‘2015-01-04‘, 30);
最终SQL:
1 SELECT 2 weather.id AS ‘Id‘ 3 FROM 4 weather 5 JOIN 6 weather w 7 ON 8 DATEDIFF(weather.RecordDate, w.RecordDate) = 1 9 AND weather.Temperature > w.Temperature;