SQL查询如何排除同时含Visma和1850标识的客户？

Got it, let's tackle this problem. The core issue here is we need to keep customers who have a 'Visma' entry in CustomFields, but completely exclude anyone who also has an entry starting with '1850' (regardless of how many 'Visma' entries they have). Here are two reliable approaches:

Approach 1: Using NOT EXISTS (Most Intuitive) #

This method first gets all customers with 'Visma', then filters out those who have any '1850...' entries in CustomFields.

SELECT 
    c.CompanyName AS Kunde, 
    c.IsActiveClient AS "Aktiv i CV", 
    c.ssn AS CVR, 
    cf.CustomFieldDefinitionFk, 
    cf.Value 
FROM Person AS c 
INNER JOIN CustomFields AS cf ON c.Id = cf.PersonFk 
WHERE 
    cf.Value = 'Visma'
    AND NOT EXISTS (
        SELECT 1 
        FROM CustomFields cf_exclude 
        WHERE cf_exclude.PersonFk = c.Id 
        AND cf_exclude.Value LIKE '1850%'
    )
ORDER BY Kunde;

How it works: #

• The main query grabs your original 'Visma' records.
• The NOT EXISTS subquery checks if the same customer has any CustomFields entry starting with '1850'. If they do, that customer is excluded entirely from the results.

Approach 2: Using GROUP BY + HAVING (Good for Aggregate Checks) #

If you want to validate the condition at the customer level first, you can group by customer ID, then filter groups that have 'Visma' but no '1850...' entries.

SELECT 
    c.CompanyName AS Kunde, 
    c.IsActiveClient AS "Aktiv i CV", 
    c.ssn AS CVR, 
    cf.CustomFieldDefinitionFk, 
    cf.Value 
FROM Person AS c 
INNER JOIN CustomFields AS cf ON c.Id = cf.PersonFk 
WHERE 
    c.Id IN (
        SELECT PersonFk
        FROM CustomFields
        GROUP BY PersonFk
        HAVING 
            MAX(CASE WHEN Value = 'Visma' THEN 1 ELSE 0 END) = 1
            AND MAX(CASE WHEN Value LIKE '1850%' THEN 1 ELSE 0 END) = 0
    )
    AND cf.Value = 'Visma'
ORDER BY Kunde;

How it works: #

• The subquery groups CustomFields entries by customer (PersonFk).
• The HAVING clause checks two things:
  1. The customer has at least one 'Visma' entry (MAX(CASE...) = 1).
  2. The customer has no entries starting with '1850' (MAX(CASE...) = 0).
• We then join back to get the specific 'Visma' records for these filtered customers.

Both methods should work reliably. The first one is usually more performant for large datasets since it avoids grouping, but either will get the job done.

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