Oracle SQL：自动化删除唯一组条目的实现方案问询

Got it, let's solve this problem exactly how you need it—automatically filtering out Group values that only appear once for type = 'rec', no manual exclusion lists required (critical for large datasets!).

Core Idea #

First, we need to count how many times each Group appears for the target type, then only keep the groups where that count is greater than 1. There are two straightforward, efficient ways to do this in SQL.

Approach 1: Subquery with GROUP BY + HAVING #

This is a classic method that works in almost all SQL databases (MySQL, PostgreSQL, SQL Server, etc.):

SELECT `Group`
FROM your_table
WHERE type = 'rec'
AND `Group` IN (
    -- Subquery to get groups that appear more than once for type 'rec'
    SELECT `Group`
    FROM your_table
    WHERE type = 'rec'
    GROUP BY `Group`
    HAVING COUNT(*) > 1
);

Notes for this approach: #

• Group is a reserved SQL keyword, so we wrap it in backticks ` (MySQL) or square brackets [] (SQL Server) to avoid syntax errors. Adjust the quoting to match your database.
• The subquery first calculates the occurrence count for each group, then the outer query fetches all rows for those groups.

Approach 2: Window Function (More Efficient for Large Datasets) #

If your database supports window functions (MySQL 8.0+, PostgreSQL, SQL Server, Oracle), this method is better because it scans the table only once instead of twice:

WITH group_occurrences AS (
    SELECT 
        `Group`,
        -- Count total occurrences of each group within the 'rec' type
        COUNT(*) OVER (PARTITION BY `Group`) AS total_count
    FROM your_table
    WHERE type = 'rec'
)
SELECT `Group`
FROM group_occurrences
WHERE total_count > 1;

Why this is better: #

• The window function COUNT(*) OVER (PARTITION BY Group) adds a column to each row showing how many times that group appears. We then filter out any rows where this count is 1.
• For large datasets, this reduces the number of table scans, making the query faster.

Key Benefits of Both Methods #

• Fully automated: No need to manually list groups like C or E—both queries dynamically filter out any group that only appears once for type = 'rec'.
• Scalable: Works just as well for 10 rows as it does for 10 million rows.

内容的提问来源于stack exchange，提问作者M. Rogers