此MySQL查询缺少哪些边缘/极端情况？

问：

今天早些时候，这个 leetcode sql 问题给我带来了问题。问题如下：

Table: Weather

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| recordDate    | date    |
| temperature   | int     |
+---------------+---------+
id is the column with unique values for this table.
This table contains information about the temperature on a certain day.

 

Write a solution to find all dates' Id with higher temperatures compared to its previous dates (yesterday).

Return the result table in any order.

The result format is in the following example.

我现在已经使用 leftjoin 和一些 where 子句解决了它，但我最初的方法是：

SELECT w.id As id
FROM 
(
  SELECT id, recordDate, temperature, LAG(temperature) OVER() as prev_temp, recordDate - LAG(recordDate) OVER() as day_diff
  FROM Weather
  ORDER BY recordDate ASC
) w
WHERE w.temperature > w.prev_temp AND day_diff = 1

这通过了除最终测试用例之外的所有测试用例（太大而无法在此处发布，请参阅链接）

这是我的最终解决方案：

Select w1.id as id
FROM Weather AS w1
LEFT JOIN Weather w2 ON DATEDIFF(w1.recordDate, w2.recordDate) = 1
WHERE w1.temperature > w2.temperature 

谁能确定为什么我的初始解决方案没有通过？ 此外，如果有人有调试 SQL 查询的过程，我将不胜感激