TSQL - 选择具有相同列 A 但具有不同列 B 的行

Question

我正在尝试查找没有ID = 1的行（名称）。例如，如果我的表看起来像这样：

Name     ID        
--------------
A      1
A      0
B      1
B      0
C      0 
D      2
D      0

此查询的答案是：

Name
-----
C
D

你有什么想法吗？

Answer 1

SELECT Name
FROM myTable
GROUP BY Name
HAVING SUM(CASE WHEN ID = 1 THEN 1 ELSE 0 END) = 0

Answer 2

这里有一种方法可以做到这一点：

SELECT DISTINCT Name
FROM Table t0
WHERE NOT EXISTS
(
    SELECT 1
    FROM Table t1
    WHERE t0.Name = t1.Name
    AND t1.Id = 1
)

Answer 3

尝试以下查询：

SELECT DISTINCT(name)
FROM tbl t1 
WHERE 
NOT EXISTS (SELECT name FROM tbl t2 WHERE ID=1 AND t1.name=t2.name)

Answer 4

Select Distinct name 
From myTable
Where name not in (Select name From myTable Where id= 1)