delete 的用法(Hive)(SQL)

解法如下，采用窗口函数

delete from Person
where Id 
in
    (
    select
    t1.Id
    from(
        select
        Id,Email,
        row_number()over(partition by Email order by Id) rn
        from Person
        ) t1
    where t1.rn>1
    );

不采用窗口函数的解法

首先查询出要删除的数据
SELECt p1.*
FROM Person p1,
    Person p2
WHERe
    p1.Email = p2.Email AND p1.Id > p2.Id
；
然后删掉
DELETe p1.* FROM Person p1,
    Person p2
WHERe
    p1.Email = p2.Email AND p1.Id > p2.Id
;