针对多行匹配项的 SQLite 查询测试

Question

我在尝试查询这个时有点卡住了： 查找订购了与客户编号 250 相同的所有客户

数据库的架构：

Customers(
customerNumber INTEGER, PRIMARY KEY
);
Orders(
orderNumber INTEGER, PRIMARY KEY
customerNumber INTEGER NOT NULL,
FOREIGN KEY (customerNumber) REFERENCES Customers
);
OrderDetails (
orderNumber INTEGER NOT NULL,
productCode TEXT NOT NULL,
PRIMARY KEY (orderNumber, productCode),
FOREIGN KEY (productCode) REFERENCES Products
);

我想到了查询：

select DISTINCT customerNumber
FROM Orders Natural 
JOIN OrderDetails
WHERE productCode NOT IN (
select productCode
FROM Orders NATURAL JOIN OrderDetails
WHERE customerNumber = 219
)    
order by customerNumber;

是正确的，但它什么也没返回，所以显然我误解了什么。

Answer 1

由于您需要所有客户，因此订购了250订单或超过250但具有相同产品的所有产品，因此尝试使用条件聚合

select customerNumber
FROM Orders o
JOIN OrderDetails od 
on o.orderNumber =od.orderNumber 
WHERE od.productCode IN (
select productCode
FROM Orders o
JOIN OrderDetails od 
on o.orderNumber =od.orderNumber
WHERE customerNumber = 250
)    
group by customerNumber
having count(distinct productCode )>= 
(select count(distinct productCode )
FROM Orders o
JOIN OrderDetails od 
on o.orderNumber =od.orderNumber
WHERE customerNumber = 250)