如何用SQL检测同一行多列（如Addon列）中的重复值？

Got it, let's tackle this problem—you need to scan each row for duplicate values across multiple Addon columns (like Addon10, Addon20, etc.) and do this in bulk for all your rows. I’ve dealt with this exact scenario when auditing product add-on data, so here are a few reliable approaches depending on your SQL dialect:

Approach 1: Unpivot Columns to Rows (Most Scalable) #

This method converts your horizontal Addon columns into vertical rows, then checks for duplicates per row identifier (like your RFFF product code). It works no matter how many Addon columns you have.

WITH unpivoted_addons AS (
    -- Unpivot each Addon column into a single "AddonValue" column
    SELECT 
        ProductCode, -- Replace with your row identifier column
        Addon10 AS AddonValue
    FROM your_table
    UNION ALL
    SELECT ProductCode, Addon20 AS AddonValue FROM your_table
    UNION ALL
    SELECT ProductCode, Addon30 AS AddonValue FROM your_table
    -- Add more UNION ALL lines for every additional Addon column you need to check
)
-- Find all product codes with duplicate Addon values across their columns
SELECT DISTINCT 
    ProductCode,
    AddonValue AS Duplicate_Value
FROM unpivoted_addons
WHERE AddonValue IS NOT NULL -- Skip null values if they don't count as duplicates
GROUP BY ProductCode, AddonValue
HAVING COUNT(*) > 1;

How this works: #

• The CTE (unpivoted_addons) takes each Addon column and turns it into a separate row tied to the same product code.
• We then group by product code and addon value, counting occurrences. Any count greater than 1 means that value appears in multiple Addon columns for that row.

Approach 2: Direct Column Comparison (For Small Number of Columns) #

If you only have a handful of Addon columns, you can directly compare them pairwise. This is simpler but doesn’t scale well if you have many columns.

SELECT 
    ProductCode,
    Addon10,
    Addon20,
    Addon30
FROM your_table
WHERE 
    -- Check each pair of columns for matches (skip nulls)
    (Addon10 = Addon20 AND Addon10 IS NOT NULL)
    OR (Addon10 = Addon30 AND Addon10 IS NOT NULL)
    OR (Addon20 = Addon30 AND Addon20 IS NOT NULL);

How this works: #

• We explicitly compare every combination of Addon columns. If any pair matches (and isn’t null), the row is returned with all the columns so you can see exactly where the duplicate is.

Approach 3: Aggregate Values to Detect Duplicates #

Another way is to aggregate all Addon values for a row and check if the count of distinct values is less than the total count of non-null values. This tells you if there’s a duplicate, even if you don’t need to see which value it is.

For PostgreSQL (using STRING_AGG):

SELECT 
    ProductCode,
    STRING_AGG(AddonValue, '|') AS All_Addon_Values
FROM (
    SELECT ProductCode, Addon10 AS AddonValue FROM your_table
    UNION ALL
    SELECT ProductCode, Addon20 AS AddonValue FROM your_table
    UNION ALL
    SELECT ProductCode, Addon30 AS AddonValue FROM your_table
) AS sub
WHERE AddonValue IS NOT NULL
GROUP BY ProductCode
HAVING COUNT(AddonValue) != COUNT(DISTINCT AddonValue);

For MySQL (using GROUP_CONCAT):

SELECT 
    ProductCode,
    GROUP_CONCAT(AddonValue SEPARATOR '|') AS All_Addon_Values
FROM (
    SELECT ProductCode, Addon10 AS AddonValue FROM your_table
    UNION ALL
    SELECT ProductCode, Addon20 AS AddonValue FROM your_table
    UNION ALL
    SELECT ProductCode, Addon30 AS AddonValue FROM your_table
) AS sub
WHERE AddonValue IS NOT NULL
GROUP BY ProductCode
HAVING COUNT(AddonValue) != COUNT(DISTINCT AddonValue);

How this works: #

• We unpivot the columns like in Approach 1, then aggregate all values into a string.
• The HAVING clause checks if the total number of values is different from the number of distinct values—if so, there’s at least one duplicate in that row.

Why Your Original SQL Might Have Failed #

If your initial query didn’t work, it’s likely because:

• You tried to compare columns horizontally without unpivoting, which doesn’t account for cross-column duplicates easily.
• You didn’t handle null values, which can skew counts or comparisons.
• You used GROUP BY on the original row instead of grouping by the individual addon values.

内容的提问来源于stack exchange，提问作者Roberto Flores