leetcode rising Temperature SQL语句

SQL语句效率也有高有低，这道题目中，如果使用两次select语句，相当于双层循环，

但是实际上这种情况只需要一层循环就能搞定的。

第一种解法：1500ms

select a.Id from Weather a wherea.Temperature > (select b.Temperature from Weather b where to_days(b.Date) =to_days(a.Date) - 1)

 

第二种解法，只有一层循环，888ms

SELECT Id FROM (

   SELECT CASE

       WHEN Temperature > @prevtemp AND DATEDIFF(Date, @prevdate) = 1 THENId ELSE NULL END AS Id,

       @prevtemp:=Temperature,

       @prevdate:=Date

   FROM Weather, (SELECT @prevtemp:=NULL) AS A, (SELECT @prevdate:=NULL) ASB ORDER BY Date ASC

) AS D WHERE Id IS NOT NULL