为何我使用的交叉连接无法通过？
select distinct `w2`.`Id` from `Weather` `w1`, `Weather` `w2` where datediff(`w1`.`RecordDate`, `w2`.`RecordDate`) = 1 and `w2`.`Temperature` >`w1`.`Temperature`; This topic created in 2658 days ago, the information mentioned may be changed or developed.
13 replies 2019-01-31 14:39:24 +08:00
 | 1 Finest Jan 31, 2019 datediff(`w2`.`RecordDate`, `w1`.`RecordDate`) = 1 应该是反了吧？ |
 | 2 1069401249 Jan 31, 2019 报什么错？我测试没问题啊 |
 | 3 xx19941215 OP @hand515 哎呦 还真是 |
 | 4 Vegetable Jan 31, 2019 弱弱的问一句这个语句时间复杂度是 O(N^2)吗? |
| 5 xx19941215 OP @Vegetable cross join 就是笛卡尔积，理论上就是 O(N^2)吧 可能 inner join 复杂度会低点，还是我理解的都是错的？ |
| 6 xx19941215 OP @1069401249 你力扣提交试试 |
| 7 Vegetable Jan 31, 2019 @xx19941215 我觉得这种解法看起来能快一些,不过 LeetCode 里 datediff 的更快. ``` SELECT * FROM Weather AS a JOIN (SELECT * FROM Weather) AS b ON DATE_SUB(a.RecordDate, INTERVAL 1 DAY) = b.RecordDate WHERE a.Temperature > b.Temperature; ``` |
| 8 xx19941215 OP @Vegetable 我回头试试 |
 | 9 F281M6Dh8DXpD1g2 Jan 31, 2019 via iPhone @Vegetable mysql 不支持函数索引，所以这种写法要谨慎使用 |
| 10 Vegetable Jan 31, 2019 @xx19941215 我试过了,的确是 datediff 快,10000 条数据的时候一个 10s 一个 15s |
 | 11 mwiker Jan 31, 2019 oracle 里支持窗口函数，用这种方式效率也不错 SELECT Id FROM ( select Id,Temperature,RecordDate, lag(RecordDate) over(order by RecordDate) as Last_RecordDate, lag(Temperature) over(order by RecordDate) as Last_Temperature from Weather) WHERE Temperature > Last_Temperature AND RecordDate - Last_RecordDate = 1 |
 | 12 wind3110991 Jan 31, 2019 这个天气一看就是广州了 = = |
 | 13 abusizhishen Jan 31, 2019 试试把数据全拿出来，id 错一位比较温度再取 id 值 |
Select Language