如何查询满足TYPE字段对应可变值集合条件的所有ID的完整数据行

Let's break down how to solve this problem: we need to find all complete rows for IDs that simultaneously have at least one row matching TYPE='a' with VALUE in a specified set, and at least one row matching TYPE='b' with VALUE in another specified set. The value sets for each type are dynamic (can be 1, 2, or more values).

Core Logic First #

The key here isn't filtering individual rows directly—instead, we first identify which IDs satisfy both cross-type conditions, then pull all rows for those IDs. Using your example filter (a IN (10,30) and b IN (20,50)), IDs 1 and 3 meet both criteria, so we return every row associated with those IDs.

Solution 1: Using EXISTS Subqueries (Intuitive & Readable) #

This approach checks for the existence of qualifying rows for each type directly, making the logic easy to follow and adjust:

SELECT t.*
FROM your_table t
WHERE 
    -- Verify the ID has at least one valid 'a' row
    EXISTS (
        SELECT 1
        FROM your_table t_a
        WHERE t_a.ID = t.ID
          AND t_a.TYPE = 'a'
          AND t_a.VALUE IN (10, 30) -- Swap with your dynamic 'a' value set
    )
    AND
    -- Verify the ID has at least one valid 'b' row
    EXISTS (
        SELECT 1
        FROM your_table t_b
        WHERE t_b.ID = t.ID
          AND t_b.TYPE = 'b'
          AND t_b.VALUE IN (20, 50) -- Swap with your dynamic 'b' value set
    );

Solution 2: Using GROUP BY + HAVING (Efficient for Large Datasets) #

If you're working with a large table, this method can be more efficient since it groups IDs once instead of running subqueries per row:

SELECT t.*
FROM your_table t
JOIN (
    SELECT ID
    FROM your_table
    GROUP BY ID
    HAVING 
        -- Count valid 'a' rows; >0 means at least one exists
        SUM(CASE WHEN TYPE = 'a' AND VALUE IN (10, 30) THEN 1 ELSE 0 END) > 0
        AND
        -- Count valid 'b' rows; >0 means at least one exists
        SUM(CASE WHEN TYPE = 'b' AND VALUE IN (20, 50) THEN 1 ELSE 0 END) > 0
) qualifying_ids ON t.ID = qualifying_ids.ID;

Handling Dynamic Value Sets #

Both solutions work seamlessly with dynamic configurations:

• For a single value in the 'a' set: change IN (10,30) to IN (10)
• For 4 values in the 'b' set: change IN (20,50) to IN (20,50,60,70)

Testing with Your Sample Data #

Running either query against your provided dataset will return exactly the expected results: all rows for ID 1 and ID 3, including non-qualifying individual rows (like ID 1's a=5 or a=40) since we're pulling full data for the qualifying IDs.

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