SQL需求：仅提取分组内存在多唯一分类的产品数据

Alright, let's tackle this problem. You want to retain all records from product groups that have 2 or more distinct product categories—so in your example, group 'a' stays (since it has Apple and Orange as unique categories) and group 'b' gets excluded (only has Toys, even with duplicate rows). Here are two reliable approaches:

Method 1: Using Window Functions (Modern Databases) #

This works for MySQL 8+, PostgreSQL, SQL Server, and most other modern database systems. Window functions let us calculate the distinct category count directly for each row's group, then filter:

SELECT product_id, product_group_code, product_category
FROM (
    SELECT 
        *,
        COUNT(DISTINCT product_category) OVER (PARTITION BY product_group_code) AS distinct_category_count
    FROM your_table_name  -- Replace with your actual table name
) grouped_data
WHERE distinct_category_count >= 2;

How it works: #

• The inner query adds a column distinct_category_count that counts how many unique categories exist in each product group.
• The outer query filters out any rows where their group has fewer than 2 distinct categories, leaving only the full records from qualifying groups.

Method 2: Subquery + Join (Compatible with Older Databases) #

If you're using an older database that doesn't support window functions (like MySQL 5.x), this approach works by first identifying valid groups, then pulling all their records:

SELECT t.product_id, t.product_group_code, t.product_category
FROM your_table_name t
INNER JOIN (
    -- First get all groups with 2+ distinct categories
    SELECT product_group_code
    FROM your_table_name
    GROUP BY product_group_code
    HAVING COUNT(DISTINCT product_category) >= 2
) valid_groups ON t.product_group_code = valid_groups.product_group_code;

How it works: #

• The subquery uses GROUP BY and HAVING to find only product groups that meet the category count requirement.
• We then join this list of valid groups back to the original table to get all records from those groups.

Testing with Your Example Data #

For your sample input:

Product id | product group code | product category
1 | a | Apple
2 | a | Orange
3 | a | Apple
4 | b | Toys
5 | b | Toys

Both queries will return exactly the records for group 'a' (IDs 1, 2, 3), which matches your expected output.

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