数据库中删除重复数据

-- 创建一个临时表来存储唯一的email
CREATE TEMPORARY TABLE unique_users AS
SELECT DISTINCT email
FROM users;

-- 清空原表
TRUNCATE TABLE users;

-- 将临时表中的数据插入原表
INSERT INTO users (email)
SELECT email FROM unique_users;

-- 删除临时表
DROP TABLE unique_users;

-- 删除重复记录，保留每组中id最小的记录
DELETE FROM orders o1
WHERE o1.id NOT IN (
    SELECT MIN(o2.id)
    FROM orders o2
    WHERE o1.customer_id = o2.customer_id AND o1.order_date = o2.order_date
);

from sqlalchemy import create_engine, Table, MetaData, select

# 连接数据库
engine = create_engine('your_database_connection_string')
metadata = MetaData(bind=engine)
metadata.reflect(engine)

# 获取表
users = Table('users', metadata, autoload_with=engine)

# 查询重复数据
stmt = select([users]).group_by(users.c.email).having(users.c.email.count() > 1)
duplicates = engine.execute(stmt).fetchall()

# 删除重复数据
for dup in duplicates:
    delete_stmt = users.delete().where(users.c.email == dup.email)
    engine.execute(delete_stmt)