SQL Server中排除含'ZX'的Column B对应的所有同Column A数据

Ah, I see the issue with your original query—it only filters out the individual rows where Column B has "ZX", but doesn't account for other rows sharing the same Column A value. To fix this, we need to first identify all Column A values that have at least one row with "ZX" in Column B, then exclude every row associated with those Column A values entirely.

Here are two reliable ways to do this:

Method 1: Using a Subquery with NOT IN #

This approach first gets all Column A values that have a matching "ZX" row, then excludes any rows with those A values:

SELECT ColumnA, ColumnB
FROM Table1
WHERE ColumnA NOT IN (
    SELECT ColumnA
    FROM Table1
    WHERE ColumnB LIKE '%ZX%'
);

Note: This works well if ColumnA doesn't contain NULL values. If NULLs are possible, the NOT IN clause might behave unexpectedly (since NULL comparisons return unknown), so Method 2 is safer.

Method 2: Using NOT EXISTS (Recommended for Most Cases) #

The EXISTS clause checks for the existence of any row in the same Column A group with "ZX" in Column B. If no such row exists, we keep the original row:

SELECT t1.ColumnA, t1.ColumnB
FROM Table1 t1
WHERE NOT EXISTS (
    SELECT 1
    FROM Table1 t2
    WHERE t2.ColumnA = t1.ColumnA
      AND t2.ColumnB LIKE '%ZX%'
);

This method is generally more efficient (especially in large datasets) and handles NULL values in ColumnA correctly, making it the preferred approach for most databases.

Why Your Original Query Failed #

Your original query SELECT ColumnA, ColumnB From Table1 Where ColumnB not like '%ZX%' only removes rows where Column B directly contains "ZX". It doesn't check if other rows with the same Column A have "ZX", so those remaining rows stay in the result set—exactly the problem you're seeing.

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