SQL中查找同水果同颜色对应多口味的错误重复数据

Hey there! No worries at all—we all start somewhere, and cleaning up messy duplicate data is a routine task even for experienced engineers. Let’s break this down into actionable steps to get your table back in shape.

1. First: Identify the Duplicate Records #

Before making any changes, it’s smart to confirm exactly which (fruit, color) pairs have conflicting tastes. Run this query to spot duplicates:

SELECT fruit, color, COUNT(*) AS duplicate_count
FROM your_table_name
GROUP BY fruit, color
HAVING COUNT(*) > 1;

This will show you every fruit-color combo with multiple taste entries, plus how many duplicates exist for each.

2. Clean Up the Duplicate Data #

Next, we’ll remove the extra entries. The exact syntax depends on your database, but here are common, reliable solutions:

For MySQL (with a primary key like id) #

If your table has a unique primary key (e.g., an auto-increment id column), you can keep the record with the highest id (or lowest, if you prefer) and delete the rest:

DELETE t1
FROM your_table_name t1
JOIN your_table_name t2
ON t1.fruit = t2.fruit AND t1.color = t2.color AND t1.id < t2.id;

For MySQL 8+, PostgreSQL, or SQL Server (using window functions) #

If you don’t have a primary key, or want more flexibility, use window functions to rank records per fruit-color pair and delete all but the first entry:

WITH ranked_records AS (
    SELECT 
        fruit, color, taste,
        -- Replace (SELECT NULL) with a column like `created_at` if you want to keep the newest/oldest entry specifically
        ROW_NUMBER() OVER (PARTITION BY fruit, color ORDER BY (SELECT NULL)) AS rn
    FROM your_table_name
)
DELETE FROM ranked_records WHERE rn > 1;

3. Prevent Future Duplicates #

Once your table is clean, add a unique constraint to stop this problem from happening again. This will throw an error if anyone tries to insert a duplicate (fruit, color) pair:

ALTER TABLE your_table_name
ADD CONSTRAINT uc_fruit_color UNIQUE (fruit, color);

Critical Reminder! #

Before running any delete queries, always back up your table first (e.g., create a copy with CREATE TABLE your_table_backup AS SELECT * FROM your_table_name;). Deleting data is irreversible, so it’s better to be safe than sorry.

内容的提问来源于stack exchange，提问作者Min Lee