通过子查询移除仅出现1-2次的Fruit数据并保留其他列

Let's break down what's wrong with your original query and fix it properly.

What's Wrong With the Original Query? #

Your subquery doesn't group by Fruit—it's calculating the total count of all fruits in the entire table, not the count per fruit. When you put this single aggregate value in the WHERE clause, the database can't match it to individual rows in your main query, which causes that data type mismatch error.

Solution 1: Use a Window Function (Recommended) #

Window functions let you calculate aggregate values per group without collapsing rows, which is perfect for this scenario. Here's how to do it:

SELECT "Fruit", "Year", "Units"
FROM (
    SELECT 
        "Fruit", 
        "Year", 
        "Units",
        -- Calculate total occurrences of each Fruit for every row
        COUNT(*) OVER (PARTITION BY "Fruit") AS fruit_count
    FROM example_table
) filtered_fruits
WHERE fruit_count >= 3;

This works by:

• Adding a fruit_count column to every row that shows how many times that fruit appears in the table
• Filtering out any rows where the fruit only appears 1 or 2 times
• Keeping exactly the columns you need: Fruit, Year, and Units

Solution 2: Use a Subquery + Join #

If window functions aren't available in your database (unlikely these days, but just in case), you can precompute the valid fruits first and then join back to the main table:

SELECT et."Fruit", et."Year", et."Units"
FROM example_table et
INNER JOIN (
    -- Get all fruits that appear 3+ times
    SELECT "Fruit"
    FROM example_table
    GROUP BY "Fruit"
    HAVING COUNT(*) >= 3
) valid_fruits ON et."Fruit" = valid_fruits."Fruit";

This method:

1. Creates a list of fruits that meet your occurrence threshold
2. Joins this list back to the original table to keep only rows for those fruits
3. Preserves your desired columns without extra aggregate values

Quick Notes #

• Make sure your quoted column names match exactly how they're stored in the database (some systems like Oracle or PostgreSQL are case-sensitive with quoted identifiers)
• The window function approach is generally more efficient for large datasets since it only scans the table once, whereas the join method scans it twice

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