基于多字段组合值（无序）删除表中重复记录

Got it, let's tackle this problem where you need to remove duplicate records based on the unordered combination of service1, service2, service3. The key here is to create a consistent "signature" for each group of records that share the same three values (regardless of their original order), then keep only one record per signature.

Below are practical implementations for the most common databases, with step-by-step explanations:

1. MySQL 实现 #

MySQL doesn't have built-in array sorting, so we'll use LEAST() and GREATEST() to fix the order of the three service values, plus a CASE statement to capture the middle value:

-- First: Verify which records will be deleted (safe to run first!)
WITH ranked_records AS (
    SELECT 
        *,
        -- Generate a consistent ordered signature for the service trio
        CONCAT(
            LEAST(service1, service2, service3),
            ',',
            CASE 
                WHEN (service1 BETWEEN service2 AND service3) OR (service1 BETWEEN service3 AND service2) THEN service1
                WHEN (service2 BETWEEN service1 AND service3) OR (service2 BETWEEN service3 AND service1) THEN service2
                ELSE service3
            END,
            ',',
            GREATEST(service1, service2, service3)
        ) AS service_signature,
        -- Assign a rank to each record in the same signature group
        ROW_NUMBER() OVER (PARTITION BY 
            LEAST(service1, service2, service3),
            CASE 
                WHEN (service1 BETWEEN service2 AND service3) OR (service1 BETWEEN service3 AND service2) THEN service1
                WHEN (service2 BETWEEN service1 AND service3) OR (service2 BETWEEN service3 AND service1) THEN service2
                ELSE service3
            END,
            GREATEST(service1, service2, service3)
        ORDER BY (SELECT NULL)) AS rn
    FROM your_table_name
)
-- Delete duplicates (keep only the first record per signature)
DELETE FROM your_table_name
WHERE id IN (SELECT id FROM ranked_records WHERE rn > 1);

Notes for MySQL: #

• Replace your_table_name with your actual table name, and id with your primary/unique identifier field (critical for safe deletion).
• If your service fields can be NULL, use COALESCE() to replace NULL with a placeholder (e.g., COALESCE(service1, 'empty')) to avoid inconsistent signatures.

2. PostgreSQL 实现 #

PostgreSQL makes this simpler with array sorting support:

-- First: Verify duplicates
WITH ranked_records AS (
    SELECT 
        *,
        -- Create a sorted array of services, then convert to a string signature
        ARRAY_TO_STRING(ARRAY[service1, service2, service3] ORDER BY 1, ',') AS service_signature,
        -- Rank records in each signature group
        ROW_NUMBER() OVER (PARTITION BY ARRAY_TO_STRING(ARRAY[service1, service2, service3] ORDER BY 1, ',') ORDER BY (SELECT NULL)) AS rn
    FROM your_table_name
)
-- Delete duplicate records
DELETE FROM your_table_name
WHERE id IN (SELECT id FROM ranked_records WHERE rn > 1);

Notes for PostgreSQL: #

• The ARRAY[service1, service2, service3] ORDER BY 1 sorts the array elements alphabetically/numerically, ensuring the same trio always produces the same string.
• Adjust the ORDER BY (SELECT NULL) part if you want to keep a specific record (e.g., ORDER BY created_at DESC to keep the newest record).

3. SQL Server 实现 #

SQL Server requires unpivoting the service fields first to sort them, then re-aggregating:

-- First: Generate sorted service signatures for each record
WITH sorted_services AS (
    SELECT 
        id,
        STRING_AGG(service, ',') WITHIN GROUP (ORDER BY service) AS service_signature
    FROM (
        -- Unpivot the three service columns into rows
        SELECT id, service1 AS service FROM your_table_name
        UNION ALL
        SELECT id, service2 AS service FROM your_table_name
        UNION ALL
        SELECT id, service3 AS service FROM your_table_name
    ) AS unpivoted_data
    GROUP BY id
),
ranked_records AS (
    SELECT 
        t.*,
        s.service_signature,
        ROW_NUMBER() OVER (PARTITION BY s.service_signature ORDER BY (SELECT NULL)) AS rn
    FROM your_table_name t
    JOIN sorted_services s ON t.id = s.id
)
-- Delete duplicates
DELETE FROM your_table_name
WHERE id IN (SELECT id FROM ranked_records WHERE rn > 1);

General Tips: #

1. Always run the verification CTE (the SELECT * FROM ranked_records WHERE rn >1 part) first to confirm which records will be deleted—never skip this step!
2. If you don't have a unique identifier (like id), consider adding a temporary one (e.g., ROW_NUMBER() OVER () AS temp_id) before proceeding.

内容的提问来源于stack exchange，提问作者Bheem Singh