mysql 删除重复的行 （包括delete inner join）

#  需求:删除 name 重复数据,只保留一条即可

select *
from boy;

# +--+----+--------+-----+
# |id|name|del_flag|name0|
# +--+----+--------+-----+
# |1 |慕容皝 |0       |ba   |
# |2 |慕容垂 |0       |bb   |
# |3 |慕容博 |1       |bc   |
# |4 |慕容复 |1       |bd   |
# |5 |慕容复 |1       |bd   |
# |6 |慕容博 |1       |bd   |
# +--+----+--------+-----+


# 思路： 根据 name 分组后，组内排序，删除 序号大于 1 的

SELECT id, name, del_flag, name0, ROW_NUMBER() OVER (PARTITION BY name ORDER BY (id)) AS rn
FROM boy;

# +--+----+--------+-----+--+
# |id|name|del_flag|name0|rn|
# +--+----+--------+-----+--+
# |3 |慕容博 |1       |bc   |1 |
# |6 |慕容博 |1       |bd   |2 |
# |2 |慕容垂 |0       |bb   |1 |
# |4 | |1       |bd   |1 |
# |5 |慕慕容复容复 |1       |bd   |2 |
# |1 |慕容皝 |0       |ba   |1 |
# +--+----+--------+-----+--+


delete
from boy
where id in (
    select id
    from (SELECT id, name, del_flag, name0, ROW_NUMBER() OVER (PARTITION BY name ORDER BY (id)) AS rn
          FROM boy) Tem
    where rn > 1
);

# 查看结果：
SELECT id, name, del_flag, name0, ROW_NUMBER() OVER (PARTITION BY name ORDER BY (id)) AS rn
FROM boy;


# +--+----+--------+-----+--+
# |id|name|del_flag|name0|rn|
# +--+----+--------+-----+--+
# |3 |慕容博 |1       |bc   |1 |
# |2 |慕容垂 |0       |bb   |1 |
# |4 |慕容复 |1       |bd   |1 |
# |1 |慕容皝 |0       |ba   |1 |
# +--+----+--------+-----+--+

# 思路二： 查询出name 重复的数据，把id , 拼接在一起，留下第一个，其他的删除
select *
from boy;
# +--+----+--------+-----+
# |id|name|del_flag|name0|
# +--+----+--------+-----+
# |1 |慕容皝 |0       |ba   |
# |2 |慕容垂 |0       |bb   |
# |3 |慕容博 |1       |bc   |
# |4 |慕容复 |1       |bd   |
# |5 |慕容复 |1       |bd   |
# |6 |慕容博 |1       |bd   |
# |7 |慕容博 |1       |bd   |
# +--+----+--------+-----+


select name, group_concat(id)
from boy
group by name;
# +----+----------------+
# |name|group_concat(id)|
# +----+----------------+
# |慕容博 |3,6,7           |
# |慕容垂 |2               |
# |慕容复 |4,5             |
# |慕容皝 |1               |
# +----+----------------+


select name, group_concat(id), substring(group_concat(id), 2) del
from boy
group by name;

# +----+----------------+----+
# |name|group_concat(id)|del |
# +----+----------------+----+
# |慕容博 |3,6,7           |,6,7|
# |慕容垂 |2               |    |
# |慕容复 |4,5             |,5  |
# |慕容皝 |1               |    |
# +----+----------------+----+


select name, group_concat(id), substr(group_concat(id), 1, 1) remain, substring(group_concat(id), 2) del
from boy
group by name;
# +----+----------------+------+----+
# |name|group_concat(id)|remain|del |
# +----+----------------+------+----+
# |慕容博 |3,6,7           |3     |,6,7|
# |慕容垂 |2               |2     |    |
# |慕容复 |4,5             |4     |,5  |
# |慕容皝 |1               |1     |    |
# +----+----------------+------+----+


select substr(group_concat(id), 1, 1) remain
from boy
group by name;


#  删除 del 里的数据(反过来 留下 remain 里的数据)
# 这条sql 报错
delete
from boy
where id not in (select substr(group_concat(id), 1, 1) remain from boy A group by name);
# You can't specify target table 'boy' for update in FROM clause

# 成功
delete
from boy
where id not in (select remain from (select substr(group_concat(id), 1, 1) remain from boy A group by name) B);

# 查询结果：
select * from boy ;

# +--+----+--------+-----+
# |id|name|del_flag|name0|
# +--+----+--------+-----+
# |1 |慕容皝 |0       |ba   |
# |2 |慕容垂 |0       |bb   |
# |3 |慕容博 |1       |bc   |
# |4 |慕容复 |1       |bd   |
# +--+----+--------+-----+


# 思路三： 相同行的数据肯定 id 是不一样的， 留下 最小id 的就可以

select * from boy ;
# +--+----+--------+-----+
# |id|name|del_flag|name0|
# +--+----+--------+-----+
# |1 |慕容皝 |0       |ba   |
# |2 |慕容垂 |0       |bb   |
# |3 |慕容博 |1       |bc   |
# |4 |慕容复 |1       |bd   |
# |5 |慕容复 |1       |bd   |
# +--+----+--------+-----+
# 以最小id 为例
select * from boy  group by name;

select min(id) from boy  group by name;

# +-------+
# |min(id)|
# +-------+
# |1      |
# |2      |
# |3      |
# |4      |
# +-------+

delete
from boy
where id not in (select minId from (select min(id) minId from boy  group by name  ) B);


select * from boy  ;

# +--+----+--------+-----+
# |id|name|del_flag|name0|
# +--+----+--------+-----+
# |1 |慕容皝 |0       |ba   |
# |2 |慕容垂 |0       |bb   |
# |3 |慕容博 |1       |bc   |
# |4 |慕容复 |1       |bd   |
# +--+----+--------+-----+

# 思路四： DELETE JOIN 语句删除重复的行

select * from boy  ;
# +--+----+--------+-----+
# |id|name|del_flag|name0|
# +--+----+--------+-----+
# |1 |慕容皝 |0       |ba   |
# |2 |慕容垂 |0       |bb   |
# |3 |慕容博 |1       |bc   |
# |4 |慕容复 |1       |bd   |
# |5 |慕容复 |1       |bd   |
# +--+----+--------+-----+

#  留下每组name 的最小 id
delete
 boy1
from   boy  boy1 inner join   boy  boy2 on   boy1.name =  boy2.name and boy1.id >  boy2.id ;


# 类似：
   for(Map b:boy1 ){

     for(Map b2:boy2){
        if(b.get("name").equals(b2.get("name")) &&   b.get("id).compareTo(b2.get("id")) >0
        ){
           删除b 这条数据
 
        }


      }

   }
select * from boy  ;

# +--+----+--------+-----+
# |id|name|del_flag|name0|
# +--+----+--------+-----+
# |1 |慕容皝 |0       |ba   |
# |2 |慕容垂 |0       |bb   |
# |3 |慕容博 |1       |bc   |
# |4 |慕容复 |1       |bd   |
# +--+----+--------+-----+