2019-12-16 LeetCode SQL练习: 使用笛卡尔积自关联

1. Consecutive Numbers（180）
连续出现三次则自关联三次（自关联后会出现重复值，所以需使用distinct）

select distinct a.Num as ConsecutiveNums from logs a, logs b, logs c where 
a.id=b.id-1 and b.id=c.id-1 and a.num =b.num  and b.num =c.num;

方法一：自关联，找到需要删除的内容：具有相同email，ID更大的行；

SELECT p1.*
FROM Person p1,
    Person p2
WHERE
    p1.Email = p2.Email AND p1.Id > p2.Id
;

方法二：根据 Email 分组，找到 Id 最小的，然后在找到 Id 不在最小的Id 集合里的记录，然后删除即可（不能用target表Person 作为更新表再次使用）

delete from Person where Id not in (select p.minid from (select min(Id) as minid 
from Person group by Email) as p);

3. Rising Temperature（197）
MS SQL server Datediff(interval, date1, date2)=date2-date1
MySQL Datediff(date1,date2)=date1-date2

select b.Id as id  from weather as a, weather as b where 
DateDiff(Dd,a.RecordDate,b.RecordDate)=1 and b.temperature >a.Temperature;