SQL如何筛选两列唯一组合数超阈值的行并保留其他列

Got it, let's break this down. You've already nailed the first step of counting unique years per brand, but the tricky part is retaining all original columns like msrp while filtering out niche brands. Here are two straightforward, practical approaches to solve this:

Method 1: Subquery + INNER JOIN #

This approach first identifies valid brands (those with >10 unique years) using a subquery, then joins back to the original table to pull in all relevant rows:

SELECT c.*
FROM cars c
INNER JOIN (
    -- First, get all brands that have more than 10 distinct years
    SELECT make
    FROM cars
    GROUP BY make
    HAVING COUNT(DISTINCT year) > 10
) AS valid_brands ON c.make = valid_brands.make;

How it works: #

• The subquery valid_brands returns only the brand names that meet your "more than 10 unique years" criteria.
• Joining this with the original cars table ensures you only keep rows from those valid brands, preserving every column (including msrp, year, etc.) from the original dataset.

Method 2: Window Function (Modern SQL) #

If your database supports window functions (most modern systems like PostgreSQL, MySQL 8+, SQL Server do), this is a cleaner, more concise approach that avoids explicit joins:

SELECT make, year, msrp -- Replace with SELECT * to get all original columns
FROM (
    SELECT 
        *,
        -- Attach the count of unique years for the brand to every row
        COUNT(DISTINCT year) OVER (PARTITION BY make) AS brand_year_count
    FROM cars
) AS car_with_metrics
WHERE brand_year_count > 10;

How it works: #

• The window function COUNT(DISTINCT year) OVER (PARTITION BY make) calculates the number of unique years for each brand and assigns that value to every row belonging to that brand.
• The outer query then filters out any rows where this count is 10 or less, leaving you with all columns for the valid brands you want to keep.

Quick Notes: #

• If you need every column from the original cars table, swap SELECT make, year, msrp with SELECT * in the outer query.
• Window functions are often more efficient, but if you're working with an older database that doesn't support COUNT(DISTINCT) in window functions, stick with the first method—it’s universally compatible.

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