基于优先级筛选MySQL行：同身份标识下按Status优先级获取数据

Alright, let's break down how to solve this MySQL problem step by step. We need to target records where FirstName, LastName, and Number are identical but Status differs, then pull the highest-priority record from each of these duplicate groups (with 'King' as the top priority).

Step 1: Identify Duplicate Groups with Mismatched Status #

First, we need to find all the (FirstName, LastName, Number) combinations that have multiple distinct Status values. This helps us focus only on the record groups we care about:

SELECT FirstName, LastName, Number
FROM your_table_name
GROUP BY FirstName, LastName, Number
HAVING COUNT(DISTINCT Status) > 1;

This query returns every combination that has conflicting Status values across its records.

Step 2: Rank Records by Priority & Select Top Candidates #

Next, we'll use window functions to rank records within each duplicate group, then pick the highest-priority entry. We'll use ROW_NUMBER() to assign a rank based on your priority rules (starting with 'King' as the top choice):

WITH duplicate_groups AS (
    -- Get all duplicate groups with conflicting Status values
    SELECT FirstName, LastName, Number
    FROM your_table_name
    GROUP BY FirstName, LastName, Number
    HAVING COUNT(DISTINCT Status) > 1
),
ranked_records AS (
    -- Assign priority ranks to records in each duplicate group
    SELECT 
        t.*,
        ROW_NUMBER() OVER (
            PARTITION BY t.FirstName, t.LastName, t.Number
            ORDER BY 
                -- Prioritize 'King' first
                CASE WHEN t.Status = 'King' THEN 1 ELSE 2 END,
                -- Add additional priority rules here (e.g., next highest status)
                -- Example: CASE WHEN t.Status = 'Queen' THEN 1 ELSE 2 END,
                t.Status -- Fallback default sort if no other rules exist
        ) AS priority_rank
    FROM your_table_name t
    JOIN duplicate_groups dg 
        ON t.FirstName = dg.FirstName 
        AND t.LastName = dg.LastName 
        AND t.Number = dg.Number
)
-- Select the highest-priority record from each duplicate group
SELECT * -- Or list specific columns: FirstName, LastName, Number, Status, ...
FROM ranked_records
WHERE priority_rank = 1;

How This Works #

• The duplicate_groups CTE narrows down our scope to only the record groups we need to process.
• The ranked_records CTE assigns a priority_rank to each record in the group: records with Status = 'King' get rank 1, others get a higher rank. You can extend the ORDER BY clause with additional CASE statements to add more priority rules (like 'Queen' as the next highest, etc.).
• Finally, we filter for priority_rank = 1 to get the top-priority record from each duplicate group.

Bonus: View All Duplicate Records #

If you want to see all records in these duplicate groups (not just the top-priority one), simply remove the WHERE priority_rank = 1 clause. This will show you every record along with its priority rank, which is great for debugging.

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