如何选择多个联接表值符合选择条件的行? [英] How to select rows where multiple joined table values meet selection criteria?

问题描述

给定以下示例表架构

客户表

CustID
1
2
3

发票表

CustID InvoiceID

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

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

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.

您将如何构造 SELECT 语句?

How would you construct the SELECT statement?

推荐答案

使用:

  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

关键是i.invoiceid的计数需要等于IN子句中的参数个数.

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

COUNT(DISTINCT i.invoiceid) 的使用是为了防止 custid 和 invoiceid 的组合没有唯一约束——如果没有重复的机会，你可以省略 DISTINCT来自查询:

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:

  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

这篇关于如何选择多个联接表值符合选择条件的行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！