Suppose I have these two tables:
假设我有这两张桌子:
|Customers | |Purchases |
---------------- -----------------
|CusID |----- |PurchaseID |
|CusName | |---<|CusID |
|CusAge | |ItemName |
|CusDateAdded | |DatePurchased |
I am needing to filter my result set by multiple ItemNames. Say I want to return all Customers who are between the ages of 18 and 24 who purchased a 'camera' AND a 'phone'. I can run the following query to obtain all records for customers between the age range:
我需要用多个项目名来过滤我的结果集。比如说，我想把所有18岁到24岁购买了“相机”和“手机”的顾客都还给他们。我可以运行以下查询，获取年龄范围内客户的所有记录:
SELECT CusID
FROM Customers AS C
JOIN Purchases AS P
ON C.CusID = P.CusID
WHERE C.CusAge BETWEEN 18 AND 24
However when it comes time to filter on the ItemName column in Purchases table how does one filter on multiple rows? Supposing it is possible without multiple queries?
但是，当需要对购货表中的ItemName列进行筛选时，如何对多行进行筛选呢?假设没有多个查询是可能的?
2 个解决方案
#1
2
SELECT C.CusID
FROM Customers AS C
JOIN Purchases AS P ON C.CusID = P.CusID
WHERE C.CusAge BETWEEN 18 AND 24
AND ItemName IN ('camera','phone')
GROUP BY C.CusID
HAVING count(distinct ItemName) = 2
Group by the customer and return only those having both items.
按客户分组，只返回两个项目都有的。
#2
0
I believe this will answer your question: SQL FIDDLE
我相信这将回答你的问题:SQL FIDDLE
SELECT C.CustID,C.CustName,P.ItemName
FROM Customers AS C
JOIN Purchases AS P ON C.CustID = P.CustID
GROUP BY C.CustID,C.CustName,P.ItemName
HAVING P.ItemName IN ('camera','phone')
#1
2
SELECT C.CusID
FROM Customers AS C
JOIN Purchases AS P ON C.CusID = P.CusID
WHERE C.CusAge BETWEEN 18 AND 24
AND ItemName IN ('camera','phone')
GROUP BY C.CusID
HAVING count(distinct ItemName) = 2
Group by the customer and return only those having both items.
按客户分组，只返回两个项目都有的。
#2
0
I believe this will answer your question: SQL FIDDLE
我相信这将回答你的问题:SQL FIDDLE
SELECT C.CustID,C.CustName,P.ItemName
FROM Customers AS C
JOIN Purchases AS P ON C.CustID = P.CustID
GROUP BY C.CustID,C.CustName,P.ItemName
HAVING P.ItemName IN ('camera','phone')