SQL显示所有客户和他们在没有大量联接的情况下购买的商品

Question

我想写一个查询，打印一个结果集，该结果集的行是客户，列是每个字段中带有"Y"或"N"的项目，具体取决于客户是否购买了该项目。

简而言之，我有以下表格：

客户ID1Joe BloggsJane Doe约翰·史密斯

Answer 1

您想要条件聚合：

SELECT
c.CustomerName AS Customer,
CASE WHEN COUNT(CASE WHEN i.ItemName = 'knife' THEN 1 END) > 0
THEN 'Y' ELSE 'N' END AS Knife,
CASE WHEN COUNT(CASE WHEN i.ItemName = 'fork'  THEN 1 END) > 0
THEN 'Y' ELSE 'N' END AS Fork,
CASE WHEN COUNT(CASE WHEN i.ItemName = 'spoon' THEN 1 END) > 0
THEN 'Y' ELSE 'N' END AS Spoon
FROM Customers c
LEFT JOIN CustomerItem ci
ON ci.CustomerID = c.CustomerID
LEFT JOIN Items i
ON i.ItemID = ci.ItemID
GROUP BY
c.CustomerName;