php - SQL 查询按列中的 null 对分组连接表进行计数

Question

我有 3 个表 products 和 stock 以及 purchases。

Puchases 有一个 primary key 为 purchaseID。

我的 products 表有一个 primary key productID，然后是包含产品详细信息的列。

我的 stock 表有一个 primary key 为 framenumber 一个 foreign key 为 productID 和 purchaseID 的另一个 foriegn key。

并非所有产品都有库存。

我想通过 productID join prodcuts 和 stock 表，并在最后一行计算purchaseID 列中的空值，表示我有多少库存。

我已经尝试了很多查询，但似乎总是只能得出stock 表中的条目总数或子查询 而不是左连接正确。

我尝试过的一些东西

SELECT * , COUNT( * ) AS Count
FROM Products
LEFT JOIN Stock ON Products.productID = Stock.productID
WHERE Stock.OrderID IS NULL AND Stock.framenumber IS NOT NULL
GROUP BY Products.productID

只是给我按产品 ID 分组的库存中所有元素的数量，并没有加入所有产品

架构

CREATE TABLE BikeStock (

FrameNumber VARCHAR(10) PRIMARY KEY,
BikeCode VARCHAR(40),
OrderID INT,
FOREIGN KEY (BikeCode) REFERENCES Bike(BikeCode),
FOREIGN KEY (OrderID) REFERENCES Purchase(OrderID)
);

CREATE TABLE Bike (

BikeCode VARCHAR (40) PRIMARY KEY,
Manufacturer VARCHAR (30),
Model VARCHAR (30),
SubType VARCHAR (30),
Year SMALLINT,
FrameMaterial VARCHAR (20),
Description TEXT,
Gender VARCHAR (6),
Type VARCHAR (20),
Price DECIMAL (11,2)
);


CREATE TABLE Purchase (

DateOfPurchase DATETIME,
OrderID INT NOT NULL AUTO_INCREMENT, 
PRIMARY KEY(OrderID),
CustomerID INT,
FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)

);

Answer 1

SELECT Products.productID , SUM( 
case 
   when  (Stock.OrderID IS NULL AND Stock.framenumber IS NOT NULL) 
      then 1 
      else 0 
end case
) AS Count
FROM Products
LEFT JOIN Stock ON Products.productID = Stock.productID
GROUP BY Products.productID