查询对不同表中父类别下的项目进行计数 - Query for counting items under parent category from a different table

Query for counting items under parent category from a different table

本文关键字：项目父类查询 | 更新日期: 2023-09-27

我有股票卡和股票类别表。

我想显示类别

并显示在此类别下打开了多少张库存卡。

SELECT a.stockCatID, a.stockCatName, f.nProductsInside 
FROM stockcategories a 
LEFT JOIN 
        (
            SELECT COUNT(b.stockID) AS nProductsInside 
            FROM stockcards b 
        ) 
        f ON a.stockCatID = f.stockCatID

好吧，它返回#1054 - Unknown column 'f.stockCatID' in 'on clause'显然我犯了一个错误。

SELECT a.stockCatID, a.stockCatName, COUNT(b.stockID) as nProductsInside 
FROM stockcategories a 
LEFT JOIN stockcards b ON a.stockCatID = b.stockCatID 
group by a.stockCatID

您可能也可以这样做（未经验证）ni 顺序以坚持您的查询

SELECT a.stockCatID, a.stockCatName, f.nProductsInside 
FROM stockcategories a 
LEFT JOIN 
(
    SELECT COUNT(b.stockID) AS nProductsInside, stockCatID
    FROM stockcards b 
) 
f ON a.stockCatID = f.stockCatID

尝试如下内容：

SELECT a.stockCatID, a.stockCatName, COUNT(b.stockID) AS nProductsInside
FROM stockcategories a 
LEFT JOIN stockcards b ON a.stockCatID = b.stockCatID
GROUP BY a.id

其中a.id是stockcategories表中的主键

f 是 SELECT COUNT（b.stockID）...所以 f 没有列