如果是我，我会创建一个存储过程。另一种选择是使用PHP循环第一个查询，然后对每个ID运行另一个查询-但这种逻辑会大大降低页面速度。
下面是一个关于存储过程的很好的教程：http://net.tutsplus.com/tutorials/an-introduction-to-stored-procedures/
基本上，您可以运行与前面提到的PHP相同的循环（但它运行得更快）。该过程存储在数据库中，可以像函数一样调用。结果与查询相同。
根据请求，这里有一个示例过程（或者更确切地说，它使用了两个）。在我的示例中，“ags_orgs”的行为与您的类别类似，其中有parentOrgID。“getChildOrgs”的作用也有点像一个冗余函数，因为我不知道我必须降多少级（这是为MSSQL编写的，可能与mySQL有区别）不幸的是，它不计算行数，而是获取数据。我强烈建议遵循一两个教程，更好地掌握它的工作原理：

USE [dbname]
GO

/******Object:  StoredProcedure [dbo].[getChildOrgs]    Script Date: 09/26/2012 15:30:06******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[getChildOrgs]

@myParentID int,
@isActive tinyint = NULL

AS
BEGIN

    SET NOCOUNT ON
    DECLARE @orgID int, @orgName varchar(255), @level int

        DECLARE cur CURSOR LOCAL FOR SELECT orgID FROM dbo.ags_orgs WHERE parentOrgID = @myParentID AND isActive = ISNULL(@isActive, isActive) ORDER BY orderNum, orgName

    OPEN cur
        fetch next from cur into @orgID
    WHILE @@fetch_status = 0
    BEGIN
        INSERT INTO #temp_childOrgs SELECT orgID,orgName,description,parentOrgID,adminID,isActive,@@NESTLEVEL-1 AS level  FROM dbo.ags_orgs WHERE orgID = @orgID

        EXEC getChildOrgs @orgID, @isActive
        -- get next result
        fetch next from cur into @orgID
    END
    CLOSE cur
    DEALLOCATE cur

END

GO

此进程调用：

USE [dbname]
GO

/******Object:  StoredProcedure [dbo].[execGetChildOrgs]    Script Date: 09/26/2012 15:29:34******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[execGetChildOrgs]

@parentID int,
@isActive tinyint = NULL,
@showParent tinyint = NULL

AS

BEGIN

CREATE TABLE #temp_childOrgs
(
   orgID int,
   orgName varchar(255),
   description text,
   parentOrgID int,
   adminID int,
   isActive tinyint,
   level int
)
-- if this isn't AGS top level (0), make the first record reflect the requested organization
IF @parentID != 0 AND @showParent = 1
BEGIN
    INSERT INTO #temp_childOrgs SELECT orgID,orgName,description,parentOrgID,adminID,isActive,0 AS level  FROM dbo.ags_orgs WHERE orgID = @parentID
END

exec getChildOrgs @parentID, @isActive

SELECT * FROM #temp_childOrgs
DROP TABLE #temp_childOrgs
END

GO

以下是我计算所有子类别产品的程序

DELIMITER $$
CREATE PROCEDURE CountItemsInCategories(IN tmpTable INT, IN parentId INT, IN updateId INT)
BEGIN

    DECLARE itemId INT DEFAULT NULL;
    DECLARE countItems INT DEFAULT NULL;
    DECLARE done INT DEFAULT FALSE;
    DECLARE recCount INT DEFAULT NULL;

    DECLARE 
        bufItemCategory CURSOR FOR
    SELECT
        itemCategory.id AS id,
        COUNT(CASE WHEN item.isVisible = 1 then 1 ELSE NULL END) items
    FROM
        itemCategory
    LEFT JOIN item ON
        item.categoryId = itemCategory.id 
    WHERE
        itemCategory.isVisible = 1 AND itemCategory.categoryParentId = parentId 
    GROUP BY
        itemCategory.id
    ORDER BY
        itemCategory.name;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    SET max_sp_recursion_depth = 10000;

    IF tmpTable = 1 THEN
        DROP TEMPORARY TABLE IF EXISTS tblResults;
        CREATE TEMPORARY TABLE IF NOT EXISTS tblResults(
            id INT NOT NULL PRIMARY KEY,
            items INT
        );
    END IF;

    OPEN bufItemCategory;

        Reading_bufItemCategory: LOOP

            FETCH FROM bufItemCategory INTO itemId, countItems;

            IF done THEN
                LEAVE Reading_bufItemCategory;
            END IF;

            IF tmpTable = 1 THEN
                INSERT INTO tblResults VALUES(itemId, countItems);
            ELSE
                UPDATE tblResults SET items = items + countItems WHERE id = updateId;
            END IF;

            SET recCount = (SELECT count(*) FROM itemCategory WHERE itemCategory.categoryParentId = itemId AND itemCategory.isVisible = 1);

            IF recCount > 0 THEN
                CALL CountItemsInCategories(0, itemId, CASE WHEN updateId = 0 then itemId ELSE updateId END);
            END IF;

        END LOOP Reading_bufItemCategory;

    CLOSE bufItemCategory;

    IF tmpTable = 1 THEN
        SELECT * FROM tblResults WHERE items > 0;
        DROP TEMPORARY TABLE IF EXISTS tblResults;
    END IF;

END $$
DELIMITER;

要调用过程，只需运行：

CountItemsInCategories(firstLoop,parentId,updateId);

其中参数为：
firstLoop-第一个循环始终为“1”
parentId-子类别的父级
updateId-要更新的行id，第一个循环始终为“0”
例如：

CountItemsInCategories(1,1,0);

我希望这个例子对某人有用。

这假设您有名为prods的Product表

prod_id|categ_id

和名为categ的类别表

categ_id|parent_categ_id

由于您似乎正在使用Adjacency List结构，其中外键parent_categ_id列引用同一表中的prod_id列，因此以下查询应该可以工作

select c1.categ_id,c1.parent_categ_id,count(prods.prod_id)
as product_count from categ c1
join prods on prods.categ_id=c1.categ_id or prods.categ_id
    in( with recursive tree(id,parent_id)as 
    (select categ_id,parent_categ_id from categ 
    where categ_id=c1.categ_id  
    union all
    select cat.categ_id,cat.parent_categ_id from categ cat
    join tree on tree.id=cat.parent_categ_id) select id from tree)
group by c1.categ_id,c1.parent_categ_id 
order by product_count

如果对层次结构的深度有限制，则可以在一个语句中完成此操作。你说你总共只有4级。

SELECT SUM(ProductCount)
FROM (
    SELECT c0.ID, c0.ProductCount
    FROM Categories AS c0
    WHERE c0.ID = 1
    UNION ALL
    SELECT c1.ID, c1.ProductCount
    FROM Categories AS c0
    JOIN Categories AS c1 ON c0.ID = c1.ParentID
    WHERE c0.ID = 1
    UNION ALL
    SELECT c2.ID, c2.ProductCount
    FROM Categories AS c0
    JOIN Categories AS c1 ON c0.ID = c1.ParentID
    JOIN Categories AS c2 ON c1.ID = c2.ParentID
    WHERE c0.ID = 1
    UNION ALL
    SELECT c3.ID, c3.ProductCount
    FROM Categories AS c0
    JOIN Categories AS c1 ON c0.ID = c1.ParentID
    JOIN Categories AS c2 ON c1.ID = c2.ParentID
    JOIN Categories AS c3 ON c2.ID = c3.ParentID
    WHERE c0.ID = 1
) AS _hier;

如果您以当前的方式存储层次结构，这将适用于此查询，即所谓的“邻接列表”。基本上，ParentID是每个节点记录其在层次结构中的位置的方式。
还有其他几种存储层次结构的方法，可以更容易地查询整个树或子树。最佳的数据组织取决于要运行的查询。
以下是更多的资源：

• Models for Hierarchical Data with SQL and PHP（用户@RaymondNijland在评论中链接到它）
• 我以webinar的形式进行了演示（可以免费观看录制，但需要注册）。
• 我的书，SQL Antipatterns Volume 1: Avoiding the Pitfalls of Database Programming。
• 将平面表解析为树的最有效/最优雅的方法是什么？