题目：

题目
编写一个 SQL 查询，来删除Customers表中所有重复的电话号码，重复的电话号码只保留 Id 最小 的那个。

image Id 是这个表的主键。例如，在运行你的查询语句之后，上面的Customers表应返回以下几行: image

请至少使用两种方法解答

参考答案：

数据库版本：Server version: 8.0.20 MySQL Community Server - GPL

建表语句

create table dailytest_20200522
(
    id    int,
    phone bigint
);

数据准备

insert into dailytest_20200522 values(1,13512345678);
insert into dailytest_20200522 values(2,13512345678);
insert into dailytest_20200522 values(3,15012345678);

方法1

delete
from dailytest_20200522
where id in (
    select A.id
    from (
             select id, phone, row_number() over (partition by phone order by id ) as cn from dailytest_20200522) A
    where A.cn <> 1);

方法2

#通过创建临时表，写入去重后数据，再插入原表
create temporary table dailytest_20200522_temp as select * from dailytest_20200522 where 1=2;

insert into dailytest_20200522_temp select A.id,A.phone
    from (
             select id, phone, row_number() over (partition by phone order by id ) as cn from dailytest_20200522) A
    where A.cn = 1;

truncate table dailytest_20200522;

insert into dailytest_20200522 select * from dailytest_20200522_temp;

drop temporary table dailytest_20200522_temp;

附：
题目来源：https://mp.weixin.qq.com/s/kGWBuFihl3hOk91SiGATKw