问题描述
给定以下示例表架构
客户表
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
这篇关于如何选择多个联接表值符合选择条件的行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！