SQL server 2008条件选择查询

Question

请帮我形成这个查询。

我有一张这样的桌子：

Row Product   Role            
-------------------
1   ABC-1     Self
2   ABC-2     Self
3   ABC-2     Joint
4   ABC-3     Self
5   ABC-3     Joint
6   ABC-4     Self

我只想获取角色为Self only的那些产品行。换句话说，从给定表中，SQL查询应该只输出第1& 6行。

Row Product   Role            
-------------------
1   ABC-1     Self
6   ABC-4     Self

谢谢

Answer 1

您可以使用NOT EXISTS

SELECT *
FROM dbo.YourTable t
WHERE NOT EXISTS(SELECT 1 FROM dbo.YourTable
                 WHERE Product = t.Product
                 AND Role <> 'Self');

Answer 2

您可以使用WHERE NOT EXISTS来执行以下操作：

Select  *
From    YourTable   T
Where Not Exists
(
    Select  *
    From    YourTable   T1
    Where   T.Product = T1.Product
    And     T1.[Role] <> 'Self'
)
And    T.[Role] = 'Self'