如何在多个已联接表值满足选择条件的行中进行选择?

Given the following sample table schema

给出以下示例表模式

Customer Table

客户表

CustID
1
2
3

Invoice Table

发票表

CustID InvoiceID

1       10
1       20
1       30
2       10
2       20
3       10
3       30

The objective is to select all customers who have an InvoiceID value of 10 and 20 (not OR). So, in this example customers w/ CustID=1 and 2 would be returned.

目标是选择所有InvoiceID值为10和20(不是或)的客户。因此，在这个示例中，将返回w/ CustID=1和2的客户。

How would you construct the SELECT statement?

如何构造SELECT语句?

3 个解决方案

#1

Use:

使用:

  SELECT c.custid
    FROM CUSTOMER c
    JOIN INVOICE i ON i.custid = c.custid
   WHERE i.invoiceid IN (10, 20)
GROUP BY c.custid
  HAVING COUNT(DISTINCT i.invoiceid) = 2

The key thing is that the counting of i.invoiceid needs to equal the number of arguments in the IN clause.

关键是我的计数。invoiceid需要等于在in子句中的参数数。

The use of COUNT(DISTINCT i.invoiceid) is in case there isn't a unique constraint on the combination of custid and invoiceid -- if there's no chance of duplicates you can omit the DISTINCT from the query:

如果custid和invoiceid的组合没有唯一的限制，那么COUNT(不同的i.invoiceid)的使用是有可能的——如果没有重复的机会，你可以忽略查询中的不同之处:

  SELECT c.custid
    FROM CUSTOMER c
    JOIN INVOICE i ON i.custid = c.custid
   WHERE i.invoiceid IN (10, 20)
GROUP BY c.custid
  HAVING COUNT(i.invoiceid) = 2

#2

The Group By answers will work unless it is possible for there to be multiples of CustID/InvoiceId in the Invoice table. Then you might get some unexpected results. I like the answer below a little better because it mirrors more closely the logic as you are describing it.

“答案组”将有效，除非发票表中可能有多个CustID/InvoiceId。然后你可能会得到一些意想不到的结果。我更喜欢下面的答案，因为它更接近你描述的逻辑。

Select CustID
From Customer
Where
  Exists (Select 1 from Invoice Where CustID=Customer.CustID and InvoiceID=10)
and
  Exists (Select 1 from Invoice Where CustID=Customer.CustID and InvoiceID=20)

#3

select CustID
    from InvoiceTable
    where InvoiceID in (10,20)
    group by CustID
    having COUNT(distinct InvoiceID) = 2

#1