SQL获取只包含一种项目的所有订单

Question

在这个数据库中，我想统计只包含特定类别产品的订单数量。

我知道如何计算包含特定类别项目的所有订单，即类别1:

SELECT Count(DISTINCT orderdetails.orderid) AS "AllCat1" 
FROM   orderdetails 
INNER JOIN orders 
ON orderdetails.orderid = orders.orderid 
AND orderdetails.productid IN (SELECT DISTINCT productid 
FROM   products 
WHERE  categoryid = 1) 
WHERE  orderdate BETWEEN "1996-12-01" AND "1996-12-31"; 

我很难找到一种优雅的方式来获得所有只包含第1类商品的订单。我尝试选择所有的订单ID，并按订单ID和类别进行分组ID:

SELECT * 
FROM   orderdetails 
INNER JOIN orders 
ON orderdetails.orderid = orders.orderid 
AND orderdate BETWEEN "1996-12-01" AND "1996-12-31" 
INNER JOIN products 
ON orderdetails.productid = products.productid 
GROUP  BY orderdetails.orderid, 
categoryid;

但我不知道如何计算所有只包含类别1项目的OrderID。我的方法正确吗？或者有更好的方法吗(我相信有(

Answer 1

您可以使用group by和having。但你需要两个层次。通过以下操作获得一个(或一组类别(中的所有订单：

SELECT o.orderId 
FROM orders o JOIN
orderdetails od
ON od.orderid = o.orderid JOIN
products p
ON p.productid = od.productid
WHERE o.orderdate BETWEEN '1996-12-01' AND '1996-12-31'
GROUP BY o.orderId
HAVING SUM(CASE WHEN p.categoryid IN (1) THEN 1 ELSE 0 END) = COUNT(*);

计数需要一个子查询：

SELECT COUNT(*)
FROM (SELECT o.orderId 
FROM orders o JOIN
orderdetails od
ON od.orderid = o.orderid JOIN
products p
ON p.productid = od.productid
WHERE o.orderdate BETWEEN '1996-12-01' AND '1996-12-31'
GROUP BY o.orderId
HAVING SUM(CASE WHEN p.categoryid IN (1) THEN 1 ELSE 0 END) = COUNT(*)
) o;

Answer 2

您可以使用HAVING子句进行筛选。我们基本上计算订单详细信息行，其中订单的类别为1。它应该等于该顺序的总行数。这将确保一个顺序中的所有类别仅为1。

SELECT od.orderid 
FROM   orderdetails AS od 
INNER JOIN orders AS o
ON od.orderid = o.orderid 
AND o.orderdate BETWEEN "1996-12-01" AND "1996-12-31" 
INNER JOIN products AS p
ON od.productid = p.productid 
GROUP  BY od.orderid 
HAVING COUNT(CASE WHEN p.categoryid = 1 THEN 1 END) = COUNT(*)

建议在多表查询的情况下使用别名，以提高代码的清晰度和可读性