tsql - 获取具有一个特定值但没有另一个特定值的 ID

Question

我有两列

OrderId  OrderStatus
120      1
120      2
121      1
123      1
123      2

我只想检索 OrderId 为 121 的行，因为它的 OrderStatus 为 1，但没有 OrderStatus 为 2。

Answer 1

因为你没有提到有一个 ' StatusDate '或类似的东西，我要

I want to only bring back the row with OrderId 121 because it has had an OrderStatus of 1 BUT IT HAS NEVER HAD AN OrderStatus of 2.

意思是

I want to only bring back OrderId 121, because there is a row with OrderId 121 and OrderStatus 1, but there is no row with OrderId 121 and OrderStatus 2

对于 SQL 2005 及更高版本， EXCEPT 使这非常简洁:

SELECT OrderId FROM [Order] WHERE OrderStatus = 1
EXCEPT
SELECT OrderId FROM [Order] WHERE OrderStatus = 2

EXCEPT returns distinct values所以不需要任何进一步的 DISTINCT .