SQL查询：找出包含其他集合所有元素的集合ID

To solve this problem, we need to identify every collection ID that includes all elements from at least one other distinct collection ID. Below are two efficient SQL approaches to achieve this, using your sample data as a reference.

Assumptions #

• We'll assume your table is named collection with columns id (collection ID) and elem (element in the collection).
• Duplicate elements in a collection (like ID4 having two Fs) are treated as part of the same set—so we only care about unique elements when checking inclusion.

Approach 1: Using CTEs for Readability #

This method breaks down the problem into manageable steps using Common Table Expressions (CTEs), making the logic easy to follow:

WITH collection_element_counts AS (
    -- Get the count of unique elements for each collection
    SELECT 
        id AS collection_id, 
        COUNT(DISTINCT elem) AS unique_element_count
    FROM collection
    GROUP BY id
),
matching_elements AS (
    -- Count how many unique elements of Y are present in X (for X != Y)
    SELECT 
        c1.id AS x_id, 
        c2.id AS y_id, 
        COUNT(DISTINCT c2.elem) AS matched_elements
    FROM collection c1
    INNER JOIN collection c2 
        ON c1.elem = c2.elem 
        AND c1.id != c2.id
    GROUP BY c1.id, c2.id
)
-- Select all X IDs where all elements of Y are present in X
SELECT DISTINCT x_id AS result_id
FROM matching_elements
JOIN collection_element_counts 
    ON matching_elements.y_id = collection_element_counts.collection_id
WHERE matching_elements.matched_elements = collection_element_counts.unique_element_count;

How It Works:

1. collection_element_counts: Calculates the number of unique elements for each collection. For your sample data, this gives:
   • ID1: 2 elements (A, C)
   • ID2: 4 elements (B, D, A, C)
   • ID3: 3 elements (A, E, F)
   • ID4: 1 element (F)
2. matching_elements: Counts how many unique elements from collection Y are present in collection X (for all pairs where X ≠ Y).
3. The final query joins these two CTEs and filters for cases where the number of matched elements equals the total unique elements of Y—meaning X contains every element of Y.

Approach 2: Using NOT EXISTS for Set-Based Logic #

This approach leverages nested NOT EXISTS clauses to directly check if all elements of Y are present in X:

SELECT DISTINCT c1.id AS result_id
FROM collection c1
WHERE EXISTS (
    -- There exists some other collection Y
    SELECT 1
    FROM collection c2
    WHERE c2.id != c1.id
    AND NOT EXISTS (
        -- No element in Y is missing from X
        SELECT 1
        FROM collection c3
        WHERE c3.id = c2.id
        AND NOT EXISTS (
            -- This element from Y is present in X
            SELECT 1
            FROM collection c4
            WHERE c4.id = c1.id
            AND c4.elem = c3.elem
        )
    )
);

How It Works:

• For each collection X (c1.id), we check if there's another collection Y (c2.id) where no element of Y is missing from X.
• The innermost NOT EXISTS checks if a specific element from Y is not present in X. If this is never true for any element in Y, then X contains all elements of Y.

Result for Your Sample Data #

Both queries will return the following result:

result_id
2
3

Which matches your expected outcome: ID2 contains all elements of ID1, and ID3 contains all elements of ID4.

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