如何简化筛选同一Number对应唯一Value的SQL查询语句？

Hey there! Let's make your SQL query cleaner while getting the exact result you need. Your goal is to fetch all rows where every entry for a given Number has the same Value—so Numbers like 2,3,4 where there's no mix of different Values.

Your current nested query works, but we can simplify it in a few ways. Here are some concise alternatives:

1. Window Function (One Step to All Rows) #

This approach uses a window function to calculate distinct values per Number directly, then filters for groups with only one distinct value:

SELECT Number, Value
FROM (
    SELECT 
        Number, 
        Value,
        COUNT(DISTINCT Value) OVER (PARTITION BY Number) AS distinct_value_count
    FROM testtable
) subquery
WHERE distinct_value_count = 1;

Why this works: The PARTITION BY Number splits the data into groups per Number, and COUNT(DISTINCT Value) counts how many unique Values exist in each group. We only keep rows where this count is 1 (meaning all Values for that Number are identical).

2. GROUP BY + JOIN (Filter Valid Numbers First) #

If you prefer a more traditional approach, first identify valid Numbers with uniform Values, then join back to the original table to get the full rows:

SELECT t.Number, t.Value
FROM testtable t
JOIN (
    SELECT Number
    FROM testtable
    GROUP BY Number
    HAVING COUNT(DISTINCT Value) = 1
) valid_numbers ON t.Number = valid_numbers.Number;

Why this works: The inner query groups by Number and uses COUNT(DISTINCT Value) = 1 to filter for Numbers with no mixed Values. We then join this list of valid Numbers back to the original table to retrieve all corresponding rows.

3. EXISTS Subquery (Logical, No Grouping) #

This option uses a logical check to exclude any Number that has conflicting Values:

SELECT Number, Value
FROM testtable t
WHERE NOT EXISTS (
    SELECT 1
    FROM testtable t2
    WHERE t2.Number = t.Number
      AND t2.Value != t.Value
);

Why this works: For each row, we check if there's any other row with the same Number but a different Value. If no such row exists, all Values for that Number are identical, so we keep the row.

All three solutions will return your expected result:

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