[大数据]delete 的用法(Hive)(SQL)

此题需要注意的是题目要求是删除，故而不能使用group by，来查询

解法如下，采用窗口函数

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
;