问题

给定一个 Weather 表，编写一个 SQL 查询，来查找与之前（昨天的）日期相比温度更高的所有日期的 Id。

例如，根据上述给定的 Weather 表格，返回如下 Id:

准备

create database leecote197;


use leecote197;


create table Weather (
Id int(20),
RecordDate date,
Temperature int(20));


insert into Weather values
(1,'2015-01-01',10),
(2,'2015-01-02',25),
(3,'2015-01-03',20),
(4,'2015-01-04',30);

解答

SELECT a.Id
FROM Weather AS a CROSS JOIN Weather AS b
ON DATEDIFF(a.RecordDate, b.RecordDate) = 1
AND a.Temperature > b.Temperature;