Sql数据检索问题

Question

我有以下几张表：

阶

Order_id     orde_number    Order_name  

1             12345          iphone

2             67891          samsung 

order_event

order_event_no      status

1                    D

1                    C

2                    C

我编写了下面的查询来像下面这样检索status not in ('D')，但是它给了我两个记录，但是查询不应该返回，因为order_no 1已经作为状态D返回，即使它有第二个记录C，它不应该包含。

select o.order_number,o.order_name 
  from order o 
  join order_event oe 
    on (o.order_id=oe.order_event_no) where oe.status not in ('D') 

你好，柴土

Answer 1

这将满足您对给定模式/数据的要求.

SELECT order_number, order_name
FROM order
WHERE order_id NOT IN (SELECT order_event_no FROM order_event WHERE status = 'D')

Answer 2

如果要排除任何具有“D”状态的订单，则需要一个子查询。

select o.order_number,o.order_name 
from order o 
where oe.order_event_no 
NOT IN
    (SELECT order_event_no FROM order_event_no WHERE status = 'D')

Answer 3

这是等价的。一些RDBMs会更快地执行它：

Select
  o.order_number,
  o.order_name
from
  order o
where
  not exists (
    select
      'x'
    from
      order_event oe
    where
      oe.order_event_no = o.order_id And
      oe.status = 'D'
  );