MySQL计数所有子类别的产品

Question

我有两个表格；类别和产品。对于每个类别，我想计算其所有子类别中有多少产品。我已经计算了每个类别中有多少。示例表是：

类别：

ID  ParentID  ProductCount  SubCategoryProducts
1   NULL      0
2   1         2
3   2         1

产品：

ProductID  CategoryID
123        2
124        2
125        3

所以我想让我的函数：

ID  ParentID  ProductCount  SubCategoryProducts
1   NULL      0             3
2   1         2             1
3   2         1             0

它只需要作为选择查询，无需更新数据库。

有什么想法吗？

编辑:SQL小提琴:http://sqlfiddle.com/#!2/1941a/4/0

Answer 1

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

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”

一、子类别的父类

updateId-要更新的行的id，第一个循环始终为“0”

例如:

CountItemsInCategories(1,1,0);

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

Answer 2

如果您对层次结构的深度有限制，您可以在一条语句中执行此操作。您说您总共只有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是每个节点记录其在层次结构中位置的方式。

还有其他几种存储层次结构的方法，可以更轻松地查询整棵树或子树。最佳的数据组织取决于要运行的查询。

以下是更多资源:

< li >使用SQL和PHP的分层数据模型(user @ RaymondNijland在评论中链接到它) < li >我以网络研讨会的形式进行了演示(免费观看录像，但需要注册)。 我的书《SQL反模式:避免数据库编程的陷阱》。 < li >将平面表解析为树的最有效/优雅的方法是什么？

Answer 3

如果是我，我会创建一个存储过程。另一种选择是在第一个查询中使用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