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

a64a0gku 于 2021-06-17 发布在 Mysql

关注(0)|答案(2)|浏览(252)

在这个数据库中，我想计算只包含某一类别产品的订单数量。
我知道如何计算包含特定类别（即类别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类项目。我尝试选择所有orderid并按orderid和categoryid对它们进行分组：

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。我的方法对吗？或者有没有更好的方法（我肯定有）

sql mysql Join Database

来源：https://stackoverflow.com/questions/53430760/sql-get-all-orders-which-contain-exclusively-one-kind-of-item

2条答案

按热度按时间

q5lcpyga1#

您可以使用 HAVING 条款。我们基本上计算订单明细行，其中一个订单的category为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(*)

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

赞(0）回复(0）举报 2021-06-18

axr492tv2#

你可以用 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;

赞(0）回复(0）举报 2021-06-18

我来回答

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

2条答案

相关问题

热门标签

最新问答