Oracle SQL：多列分组后查询pay_type仅含'CR'的行

Let's break this down. You've got a table with ord_num, loc, void, and pay_type columns, and you need to pull out only those ord_num/loc combinations where every single entry for that pair has a pay_type of 'CR'—regardless of the void status.

Sample Input Data #

Here's your source data formatted as a table:

Desired Output #

You want to get these unique ord_num/loc pairs:

Solution 1: Using GROUP BY + HAVING #

This approach groups the data by ord_num and loc, then filters groups where there are no entries with a pay_type other than 'CR':

SELECT ord_num, loc
FROM your_table_name
GROUP BY ord_num, loc
HAVING COUNT(CASE WHEN pay_type != 'CR' THEN 1 END) = 0;

How this works:

• The CASE statement marks any row where pay_type isn't 'CR' with a 1; rows that are 'CR' get NULL.
• COUNT() ignores NULL values, so if the count is 0, that means every row in the group has pay_type = 'CR'.

Solution 2: Using NOT EXISTS #

If you prefer a row-based approach (often efficient with proper indexing), you can use NOT EXISTS to exclude any ord_num/loc pairs that have at least one non-'CR' pay_type:

SELECT DISTINCT t1.ord_num, t1.loc
FROM your_table_name t1
WHERE NOT EXISTS (
    SELECT 1
    FROM your_table_name t2
    WHERE t2.ord_num = t1.ord_num
      AND t2.loc = t1.loc
      AND t2.pay_type != 'CR'
);

How this works:

• For each row in t1, we check if there's any matching row in t2 (same ord_num/loc) with a non-'CR' pay_type.
• If no such row exists, we keep the ord_num/loc pair. DISTINCT ensures we only get each pair once, even if there are multiple rows for it.

Either of these queries will give you the exact result you're looking for. Just replace your_table_name with the actual name of your Oracle table.

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