SQL实现LeetCode(197.上升温度)
[LeetCode] 197.Rising Temperature 上升温度
Given a Weather table, write a SQL query to find all dates' Ids with higher temperature compared to its previous (yesterday's) dates.
+---------+------------+------------------+
| Id(INT) | Date(DATE) | Temperature(INT) |
+---------+------------+------------------+
| 1 | 2015-01-01 | 10 |
| 2 | 2015-01-02 | 25 |
| 3 | 2015-01-03 | 20 |
| 4 | 2015-01-04 | 30 |
+---------+------------+------------------+
For example, return the following Ids for the above Weather table:
+----+
| Id |
+----+
| 2 |
| 4 |
+----+
这道题给了我们一个Weather表,让我们找出比前一天温度高的Id,由于Id的排列未必是按顺序的,所以我们要找前一天就得根据日期来找,我们可以使用MySQL的函数Datadiff来计算两个日期的差值,我们的限制条件是温度高且日期差1,参见代码如下:
解法一:
SELECT w1.Id FROM Weather w1, Weather w2WHERE w1.Temperature > w2.Temperature AND DATEDIFF(w1.Date, w2.Date) = 1;
解法二:
SELECT w1.Id FROM Weather w1, Weather w2WHERE w1.Temperature > w2.Temperature AND TO_DAYS(w1.Date) = TO_DAYS(w2.Date) + 1;
解法三:
SELECT w1.Id FROM Weather w1, Weather w2WHERE w1.Temperature > w2.Temperature AND SUBDATE(w1.Date, 1) = w2.Date;
解法四:
SELECT Id FROM (SELECT CASE WHEN Temperature > @pre_t AND DATEDIFF(Date, @pre_d) = 1 THEN Id ELSE NULL END AS Id,@pre_t := Temperature, @pre_d := Date FROM Weather, (SELECT @pre_t := NULL, @pre_d := NULL) AS init ORDER BY Date ASC) id WHERE Id IS NOT NULL;
https://leetcode.com/discuss/33641/two-solutions
https://leetcode.com/discuss/52370/my-simple-solution-using-inner-join
https://leetcode.com/discuss/86435/a-simple-straightforward-solution-and-its-very-fast
更多相关文章
- MySQL系列多表连接查询92及99语法示例详解教程
- Linux下MYSQL 5.7 找回root密码的问题(亲测可用)
- MySQL 什么时候使用INNER JOIN 或 LEFT JOIN
- Android(安卓)-- Android(安卓)JUint 与 Sqlite
- android中SqLite query中用selectionArgs处理字符传值
- android从服务器下载文件(php+apache+win7+MySql)
- Android(安卓)ORM SQL Top 5
- android SQLiteDatebase 实践
- Android(安卓)SQLiteDatabase的使用