MySQL技术问询：查询TAPS表中id和sequence重复但card_tap、time不同的行

Hey there! Let's work through this problem. You need to pull all rows from your TAPS table where multiple rows share the same id and sequence values, but have differing card_tap or time values. Below are two reliable approaches depending on your MySQL version:

Option 1: Window Functions (MySQL 8.0+) #

This is the cleanest and most efficient method if you're running MySQL 8.0 or newer. We'll use window functions to count distinct values of card_tap and time within each (id, sequence) group, then filter groups where at least one of these counts is greater than 1 (meaning there are differing values).

WITH grouped_taps AS (
    SELECT
        id,
        sequence,
        card_tap,
        time,
        -- Include any other columns from your TAPS table here
        other_column_1,
        other_column_2,
        -- Count distinct card_tap values (handle NULLs with COALESCE)
        COUNT(DISTINCT COALESCE(card_tap, '###NULL_MARKER###')) 
            OVER (PARTITION BY id, sequence) AS distinct_card_count,
        -- Count distinct time values (handle NULLs similarly)
        COUNT(DISTINCT COALESCE(time, '1970-01-01 00:00:00')) 
            OVER (PARTITION BY id, sequence) AS distinct_time_count
    FROM TAPS
)
SELECT
    id,
    sequence,
    card_tap,
    time,
    other_column_1,
    other_column_2
FROM grouped_taps
WHERE distinct_card_count > 1 OR distinct_time_count > 1;

Notes for Option 1: #

• Replace other_column_1, other_column_2 with actual column names from your table (or use * if you want all columns, but explicit column names are better practice).
• The COALESCE calls ensure NULL values are treated as distinct non-NULL values, since COUNT(DISTINCT) normally ignores NULLs. Adjust the marker values if they might conflict with your actual data.

Option 2: Self-Join (Compatible with MySQL 5.x) #

If you're stuck on an older MySQL version that doesn't support window functions, a self-join will get the job done. We'll join the table to itself on matching id and sequence, then filter for rows where card_tap or time don't match.

SELECT DISTINCT t1.*
FROM TAPS t1
INNER JOIN TAPS t2
    ON t1.id = t2.id
    AND t1.sequence = t2.sequence
    -- Exclude exact matches (same card_tap AND same time)
    AND NOT (t1.card_tap <=> t2.card_tap AND t1.time <=> t2.time);

Notes for Option 2: #

• We use <=> (the NULL-safe equality operator) instead of = to handle NULL values correctly. a <=> b returns true if both are NULL or both are non-NULL and equal.
• The DISTINCT keyword ensures we don't get duplicate rows from the join operation.

Performance Tips #

• If your table is large, add an index on (id, sequence) to speed up both queries dramatically.
• The window function approach will generally outperform the self-join on large datasets, as it avoids the Cartesian product created by joining the table to itself.

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