Question

页: 1

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

我只想收回第121号命令的行文,因为它有1号命令,但没有2号命令。

Answer 1

既然你没有提及<代码>StatusDate,或者说什么样的话,我就将这样做。

我只想把第121号命令带回来,因为它拥有1部BUTITHAS NEVER HAD AN OrderStatus 2号命令。

意见

我只想恢复第121号命令,因为第121号命令和第1号命令有一行,但第121号命令和第2号命令没有增长。

With SQL 2005 and later, EXCEPT makes this extremely concise:

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

EXCEPT returns distinct values so there is no need for any further DISTINCT.

Answer 2

You can use a self-join, searching for OrderStatus = 1 from the left and missed join with OrderStatus = 2 from the right:

declare @t table(OrderID int, OrderStatus int)
insert into @t values (120, 1)
insert into @t values (120, 2)
insert into @t values (121, 1)
insert into @t values (123, 1)
insert into @t values (123, 2)

select t1.* 
from @t 
    t1 left join 
    (select * from @t where OrderStatus = 2) as t2 on t2.OrderID = t1.OrderID 
where 
    t1.OrderStatus = 1 and
    t2.OrderID is null

Answer 3

If your order statuses are only 1 and 2, and the orders must be a status of 1 at some point before becoming a status of 2, you can search for the orders with a maximum order status value of 1:

  select distinct orderid from orders
group by orderid
  having max(orderstatus) = 1;

Demo: http://www.sqlize.com/2k3C2SqMH2

或者,如果这并不简单,那么我们就能够更明确地表明不允许使用<条码>出现2号命令的情况。条款:

select distinct orderid
  from orders o
 where not exists (
     select * from orders o2
     where o2.orderid = o.orderid
       and o2.orderstatus = 2
   );

Demo:

友情链接