SQL Server：如何筛选仅包含指定GroupDescription值的行？

Got it, let's fix this query. Your current statement returns rows that match either 'construction' or 'h&s', but it doesn't account for AgreementIDs that also have other GroupDescription values (like HR or Legal in your example). We need to ensure the AgreementID has only those two values (or just one of them) and no others.

Here are two reliable approaches:

Approach 1: Using GROUP BY and HAVING #

This method groups by AgreementID, then checks two key conditions:

1. No GroupDescription entries for the ID fall outside your target set
2. The number of distinct GroupDescriptions is either 1 or 2 (so we include IDs with just one of the two values, or both)

SELECT AgreementID
FROM tblAgreements
GROUP BY AgreementID
HAVING 
    -- Ensure no GroupDescription outside the target set exists
    SUM(CASE WHEN GroupDescription NOT IN ('construction', 'h&s') THEN 1 ELSE 0 END) = 0
    -- Allow either 1 or 2 distinct target values
    AND COUNT(DISTINCT GroupDescription) IN (1, 2);

Approach 2: Using NOT EXISTS #

This approach first selects AgreementIDs that have at least one of your target values, then excludes any IDs that have a GroupDescription outside the target set:

SELECT DISTINCT a.AgreementID
FROM tblAgreements a
WHERE a.GroupDescription IN ('construction', 'h&s')
AND NOT EXISTS (
    SELECT 1
    FROM tblAgreements a2
    WHERE a2.AgreementID = a.AgreementID
    AND a2.GroupDescription NOT IN ('construction', 'h&s')
);

Why these work for your example #

In your sample data, AgreementID 20549 has HR and Legal entries. Both queries will exclude it because:

• In Approach 1, the SUM would return 2 (for HR and Legal), which isn't 0
• In Approach 2, the NOT EXISTS condition would find the HR/Legal entries, so the ID gets excluded

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