如何在MySQL中删除amount=0的重复name行并保留无重复记录？

Got it, let's break down how to solve this problem exactly as you need it. The core requirement is: remove rows where a name has duplicate entries (with at least one non-zero amount), but keep rows where a name only exists with amount=0 (like the "Pack of rice" entry in your sample data).

Method 1: Using EXISTS Subquery (Works in all MySQL versions) #

This approach checks if a given name has any non-zero amount records before deleting its amount=0 rows. It's compatible with older MySQL versions that don't support window functions.

DELETE t1
FROM Table1 t1
WHERE t1.amount = 0
AND EXISTS (
    SELECT 1
    FROM Table1 t2
    WHERE t2.name = t1.name
    AND t2.amount != 0
);

How it works:

• The WHERE clause first targets all rows with amount=0.
• The EXISTS subquery checks if there's at least one other row with the same name but a non-zero amount.
• If both conditions are true, the row gets deleted. For entries like "Pack of rice" (no non-zero amount rows), the subquery returns false, so the row stays.

Method 2: Using Window Functions (MySQL 8.0+) #

If you're using MySQL 8.0 or newer, window functions make this logic more readable and explicit. We first calculate how many non-zero entries exist per name, then use that to decide which rows to delete.

WITH name_amount_stats AS (
    SELECT 
        t_id, p_id, name, amount,
        -- Count how many non-zero amount entries exist for this name
        COUNT(CASE WHEN amount != 0 THEN 1 END) OVER (PARTITION BY name) AS non_zero_count
    FROM Table1
)
DELETE FROM Table1
WHERE (t_id, p_id, name, amount) IN (
    SELECT t_id, p_id, name, amount
    FROM name_amount_stats
    WHERE amount = 0 AND non_zero_count > 0
);

How it works:

• The CTE (name_amount_stats) adds a column non_zero_count that counts how many non-zero amount rows exist for each name.
• We then delete all amount=0 rows where non_zero_count is greater than 0 (meaning there are other valid non-zero entries for that name).

Important Precaution #

Before running any DELETE statement, always verify which rows will be deleted by replacing DELETE with SELECT * to preview the results:

-- Preview rows to delete with Method 1
SELECT *
FROM Table1 t1
WHERE t1.amount = 0
AND EXISTS (
    SELECT 1
    FROM Table1 t2
    WHERE t2.name = t1.name
    AND t2.amount != 0
);

This helps you avoid accidental data loss. After confirming the preview matches the rows you want to remove, you can run the DELETE statement safely.

内容的提问来源于stack exchange，提问作者Hispalis Guadal