mysql - 删除重复数据

仅保留一行

如下表：

 1. 查询某一列重复的记录（查询姓名重复的行）

select * from tb_1 where stu_name in (select stu_name from tb_1 group by stu_name having count(1) > 1)

2. 查询某一列不重复的记录（查询姓名不重复的所有数据）

select * from tb_1 where id in (select min(id) from tb_1 group by stu_name) -- 注意：子句中一定要加上min，否则查出所有行

3. 删除某一列重复的数据（删除姓名重复的行）

delete from tb_1 where id not in (select a.min_id from (select min(id) as min_id from tb_1 group by stu_name) as a)

保留多行

如下表：删除相同医院、相同科室5个以上的医生数据（删除红框中的数据）

 结合sql和python实现

1. 以下sql可以将相同医院相同科室符合条件的数据删除一行

delete from tb_test_doctor_1 where phone in 
(
select b.phone from 
(select phone from tb_test_doctor_1 group by hospital, department having count(1) > 5 ORDER BY count(1) desc) as b 
)

2. 使用python循环，直到删完

import MySQLdb

conn = MySQLdb.connect(
    host='192.168.1.0',
    port=3306,
    user='root',
    passwd='123',
    db='test'
)
cur = conn.cursor()

# sql = 'select version()'
#
# cur.execute(sql)
# a = cur.fetchall()
# print(a)


sql_sum = 'select count(1) from tb_test_doctor_1 group by hospital, department having count(1) > 5 ORDER BY count(1) DESC limit 1'
cur.execute(sql_sum)
sum_tuple = cur.fetchone()
sum = sum_tuple[0]
print(sum)

sql2 = """
delete from tb_test_doctor_1 where phone in 
(
select b.phone from 
(select phone from tb_test_doctor_1 group by hospital, department having count(1) > 5 ORDER BY count(1) desc) as b 

)
"""
for n in range(sum):
    cur.execute(sql2)
    conn.commit()


cur.close()
conn.close()