SQL查询：如何获取同一SK下同时包含Code='A'和Code='B'的记录？

Let's break this down and fix your query! First, here's your table data formatted for clarity:

Why Your Initial Queries Failed #

• Your first attempt (WHERE Code='A' AND Code='B') can never return results—a single row can't have two different Code values at once.
• Using IN ('A','B') only pulls rows where Code is either A or B, but it doesn't filter out SKs that only have one of those codes (like SK 2 or 3).

Solution 1: Subquery to Find Qualifying SKs First #

This method first identifies all SKs that have both Code='A' and Code='B', then fetches all records for those SKs:

SELECT t.*
FROM your_table t
INNER JOIN (
    -- Get SKs that have both A and B codes
    SELECT SK
    FROM your_table
    WHERE Code IN ('A', 'B')
    GROUP BY SK
    HAVING COUNT(DISTINCT Code) = 2
) valid_sks ON t.SK = valid_sks.SK;

How it works:

• The subquery filters rows to only those with Code='A' or 'B', groups by SK, and uses COUNT(DISTINCT Code) = 2 to ensure the SK has both codes (the DISTINCT handles cases where an SK might have duplicate A/B entries).
• We join this list of valid SKs back to the original table to get all records for those SKs.

Solution 2: Double EXISTS Checks #

This approach directly verifies if the current SK has both an 'A' and a 'B' entry:

SELECT *
FROM your_table t
WHERE EXISTS (
    -- Check if this SK has at least one Code='A' entry
    SELECT 1
    FROM your_table
    WHERE SK = t.SK AND Code = 'A'
)
AND EXISTS (
    -- Check if this SK has at least one Code='B' entry
    SELECT 1
    FROM your_table
    WHERE SK = t.SK AND Code = 'B'
);

How it works:

• Each EXISTS clause confirms the existence of a specific code for the current SK. Only rows where both conditions are true (the SK has both A and B) are returned.

Both queries will return your desired output: all records for SK 1 and SK 4, since those are the only SKs with both Code='A' and Code='B'.

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