w3schools如何在SQL查询中使用不存在? [英] How to use not exists in a sql query with w3schools?

问题描述

我在 w3schools 的not exists sql查询中遇到问题

I have an issue with not exists sql query at w3schools

我想select所有使用shipperid = 1但不是shipperid = 3的客户. 我尝试了以下方法:

I want to select all customers that work with shipperid = 1 BUT not shipperid = 3. I tried the following:

select o1.customerid, o1.shipperid
from orders o1
where o1.shipperid=1 and not exists
(select o2.customerid from orders o2
where o1.orderid=o2.orderid
and o2.shipperid=3)
order by customerid
;

上面的查询提供了使用shipperid = 1的所有客户，并且不排除使用shipperid = 3的客户.什么是不正确的查询. (我需要专门使用not exists)

The above query gives all customers that work with shipperid = 1 and does NOT exclude customers who work with shipperid = 3. What is not correct with the query. (I need to speifically use not exists)

PS:我知道in解决方案:

PS: I know the in solution:

select customerid, shipperid
from orders
where shipperid=1 and customerid not in (
select customerid
from orders
where shipperid=3
)
order by customerid;

为什么not exists解决方案不起作用?

Why does not the not exists solution work?

推荐答案

我相当确定问题出在您加入关联子查询的方式上，即orderid = orderid.我对这个数据集不熟悉，但是令人惊讶的是，同一订单将有不同的托运人，并且增加了在正确"答案中找不到的条件.这应该起作用:

I'm fairly certain that the problem lies in the way you're joining the correlated subquery, on orderid = orderid. I'm not familiar with this dataset, but it seems surprising that the same order would have different shippers, and it adds a condition not found in your 'correct' answer. This should work:

select o1.customerid
      ,o1.shipperid
from orders as o1
where o1.shipperid = 1 
and not exists (
    select o2.orderid 
    from orders as o2
    where o1.customerid = o2.customerid
    and o2.shipperid = 3)
order by customerid
;

这篇关于w3schools如何在SQL查询中使用不存在?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！