Mysql刷题笔记 12.21 删除重复电话号码，重复的电话号码保留id最小的那个 两种方法

-- 12.21 删除重复电话号码，重复的电话号码保留id最小的那个 
/*
T1221
+------+-------------+
| ID   | Phone       |
+------+-------------+
|    1 | 13512345678 |
|    2 | 13512345678 |
|    3 | 13012345678 |
|    4 | 13512345678 |
+------+-------------+
T1221
+------+-------------+
| ID   | Phone       |
+------+-------------+
|    1 | 13512345678 |
|    3 | 13012345678 |
+------+-------------+
*/
方法1: 分组
查询：
select min(ID),Phone from T1221 group by Phone
删除：
delete from T1221 where ID not in (
  select a.minid from (select min(ID) as minid from T1221 group by Phone) a
)
   
方法2：窗口函数
查询：
select a.ID, a.Phone from (
	select ID,Phone,row_number() over(partition by Phone order by ID) as rk from T1221
)as a where a.rk = 1
删除：
delete from T1221 where ID not in (
	select a.ID from(
		select ID,Phone,row_number() over(partition by Phone order by ID) as rk from T1221
	)as a where a.rk = 1
)