58 - 上升的温度

题目

Create table If Not Exists Weather (Id int, RecordDate date, Temperature int)
Truncate table Weather
insert into Weather (Id, RecordDate, Temperature) values ('1', '2015-01-01', '10')
insert into Weather (Id, RecordDate, Temperature) values ('2', '2015-01-02', '25')
insert into Weather (Id, RecordDate, Temperature) values ('3', '2015-01-03', '20')
insert into Weather (Id, RecordDate, Temperature) values ('4', '2015-01-04', '30')

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

+---------+------------------+------------------+ | Id(INT) | RecordDate(DATE) | Temperature(INT) | +---------+------------------+------------------+ | 1 | 2015-01-01 | 10 | | 2 | 2015-01-02 | 25 | | 3 | 2015-01-03 | 20 | | 4 | 2015-01-04 | 30 | +---------+------------------+------------------+

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

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

解答

比较日期的函数：datediff()，可以算出两个日期相差的天数

作者：LeetCode

链接：https://leetcode-cn.com/problems/rising-temperature/solution/shang-sheng-de-wen-du-by-leetcode/

select a.id 
from weather as a join weather as b 
on datediff(a.recorddate, b.recorddate)=1 and a.temperature > b.temperature

Runtime: 398 ms, faster than 41.07% of MySQL online submissions forRising Temperature.

Memory Usage: N/A

如果join可以的话，那么where可以不？

select a.id from weather as a , weather as b where datediff(a.recorddate, b.recorddate)=1 and a.temperature > b.temperature

Runtime: 886 ms, faster than 6.73% of MySQL online submissions forRising Temperature.

Memory Usage: N/A

可以是可以，但不知道为啥要慢那么多

又看到一种解法：

作者：rotcod-wang 链接：https://leetcode-cn.com/problems/rising-temperature/solution/ling-fa-di-qia-er-lian-jie-shi-yong-whereshai-xuan/

select a.id from weather as a cross join weather as b where datediff(a.recorddate, b.recorddate)=1 and a.temperature > b.temperature

Runtime: 337 ms, faster than 66.03% of MySQL online submissions forRising Temperature.

Memory Usage: N/A

不知道为啥能快那么多，仅仅是把join换成了cross join

但是在这篇回答里面提到，官方文件说join, cross join, inner join都一样。就是排列组合一下两个表。

In MySQL, JOIN, CROSS JOIN, and INNER JOIN are syntactic equivalents (they can replace each other). In standard SQL, they are not equivalent. INNER JOIN is used with an ON clause, CROSS JOIN is used otherwise.

但cross join不能用on来筛选，甚至就不能判断。能加都是mysql加上去的。