mysql - 显示未订购/未出现在发票上的产品的 sql 查询

Question

我有一个作业问题我遇到了一些麻烦。我被问及我们数据库中从未售出的产品总数。

它应该是这样的:

+--------------------------------------------+
| number of products that have not been sold |
+--------------------------------------------+
|                                        228 |
+--------------------------------------------+

我不断得到这个:

+--------------------------------------------+
| number of products that have not been sold |
+--------------------------------------------+
|                                          0 |
+--------------------------------------------+

我的查询是:

SELECT count(*) AS 'number of products that have not been sold'  
FROM orderdetail  
JOIN invoice on invoice.invoiceid=orderdetail.invoiceid  
WHERE productid is null;

Answer 1

• 由于您要考虑所有产品，product 表必须是您在Join 中的起始表。
• 执行左连接而不是内连接。否则，Inner Join 将消除没有可用匹配订单行的所有产品。
• 使用 ProductID 加入 orderDetail 表
• 要计算所有没有订单的产品，请使用 COUNT()功能

尝试以下操作:

SELECT count(p.ProductID) AS 'number of products that have not been sold'  
FROM product AS p   
LEFT JOIN orderDetail AS od ON od.ProductID = p.ProductID  
WHERE od.productid IS NULL