如何在SQL中按条件筛选分组内的行？附事务ID场景需求

Hey there! Let's tackle this SQL grouping and filtering problem you've got. The goal is straightforward: for each transactionID, pick the row where Sgroup = Rgroup if it exists; if not, grab a random row from the group. Here are a few solid solutions that work across different SQL databases, plus some dialect-specific tweaks:

Generic SQL Solution (Works for Most Modern Databases) #

This approach uses window functions to rank rows within each transaction group. We prioritize rows where Sgroup matches Rgroup first, then randomize the rest. Finally, we select the top-ranked row per group.

WITH ranked_transactions AS (
    SELECT 
        transactionID,
        Sgroup,
        Rgroup,
        ROW_NUMBER() OVER (
            PARTITION BY transactionID 
            ORDER BY CASE WHEN Sgroup = Rgroup THEN 0 ELSE 1 END, RAND()
        ) AS rn
    FROM your_table_name
)
SELECT transactionID, Sgroup, Rgroup
FROM ranked_transactions
WHERE rn = 1;

How it works: #

• PARTITION BY transactionID splits the data into groups based on each transaction ID.
• The ORDER BY clause first sorts rows so that any row with Sgroup = Rgroup gets priority (marked as 0, which comes before 1).
• RAND() adds random ordering for rows that don't match the condition, ensuring we pick a random one from the group.
• We filter to only keep rows where rn = 1 (the top-ranked row per group).

Dialect-Specific Adjustments #

MySQL #

• MySQL 8.0+: The generic solution works as-is.
• Pre-MySQL 8.0 (no window functions): Use this correlated subquery approach instead:

SELECT t.*
FROM your_table_name t
WHERE (
    -- Select matching row if it exists
    Sgroup = Rgroup 
    AND NOT EXISTS (
        SELECT 1 FROM your_table_name 
        WHERE transactionID = t.transactionID AND Sgroup = Rgroup AND transactionID <> t.transactionID
    )
)
OR (
    -- Or select a random row if no match exists
    NOT EXISTS (
        SELECT 1 FROM your_table_name 
        WHERE transactionID = t.transactionID AND Sgroup = Rgroup
    ) 
    AND t.transactionID = (
        SELECT transactionID FROM your_table_name 
        WHERE transactionID = t.transactionID 
        ORDER BY RAND() LIMIT 1
    )
);

PostgreSQL #

PostgreSQL uses RANDOM() instead of RAND() for random number generation. Swap out the function in the generic solution:

WITH ranked_transactions AS (
    SELECT 
        transactionID,
        Sgroup,
        Rgroup,
        ROW_NUMBER() OVER (
            PARTITION BY transactionID 
            ORDER BY CASE WHEN Sgroup = Rgroup THEN 0 ELSE 1 END, RANDOM()
        ) AS rn
    FROM your_table_name
)
SELECT transactionID, Sgroup, Rgroup
FROM ranked_transactions
WHERE rn = 1;

SQL Server #

RAND() generates the same value for all rows in a partition in SQL Server. Use NEWID() instead to get true random ordering for non-matching rows:

WITH ranked_transactions AS (
    SELECT 
        transactionID,
        Sgroup,
        Rgroup,
        ROW_NUMBER() OVER (
            PARTITION BY transactionID 
            ORDER BY CASE WHEN Sgroup = Rgroup THEN 0 ELSE 1 END, NEWID()
        ) AS rn
    FROM your_table_name
)
SELECT transactionID, Sgroup, Rgroup
FROM ranked_transactions
WHERE rn = 1;

Testing with Your Sample Data #

• For transactionID = 2, the row (2, B, B) will always be selected since it matches Sgroup = Rgroup.
• For transactionID = 1, either (1, A, I) or (1, A, J) will be picked randomly each time you run the query.

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