一道 sql 题目

[xx19941215]

为何我使用的交叉连接无法通过？

select distinct `w2`.`Id` from `Weather` `w1`, `Weather` `w2`
where datediff(`w1`.`RecordDate`, `w2`.`RecordDate`) = 1
and `w2`.`Temperature` >`w1`.`Temperature`;

[hand515]

datediff(`w2`.`RecordDate`, `w1`.`RecordDate`) = 1

应该是反了吧？

[1069401249]

报什么错？我测试没问题啊

[xx19941215]

@hand515 哎呦 还真是 😂

[Vegetable]

弱弱的问一句这个语句时间复杂度是 O(N^2)吗?

[xx19941215]

@Vegetable cross join 就是笛卡尔积，理论上就是 O(N^2)吧 😂可能 inner join 复杂度会低点，还是我理解的都是错的？

[xx19941215]

@1069401249 你力扣提交试试 😁

[Vegetable]

@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;

```

[xx19941215]

@Vegetable 我回头试试

[liprais]

@Vegetable mysql 不支持函数索引，所以这种写法要谨慎使用

[Vegetable]

@xx19941215 我试过了,的确是 datediff 快,10000 条数据的时候一个 10s 一个 15s

[mwiker]

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

[wind3110991]

这个天气一看就是广州了 = =

[abusizhishen]

试试把数据全拿出来，id 错一位比较温度再取 id 值