所有子类别的MySQL Count产品 [英] MySQL Count products from all subcategories

问题描述

我有两个桌子.类别和产品.对于每个类别，我想计算所有子类别中有多少种产品.我已经计算出每个类别中有多少个.表格示例为:

I have two tables; categories and products. For each category i would like to count how many products there are in all of its subcategories. I already have counted how many are in each category. Example tables are:

类别:

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

产品:

ProductID  CategoryID
123        2
124        2
125        3

所以我希望我的功能能够做到:

So i would like my function to make:

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

只需将其作为选择查询，而无需更新数据库.

It simply needs to be as a select query, no need to update the database.

有什么想法吗?

SQL文件: http://sqlfiddle.com/#!2/1941a /4/0

推荐答案

如果是我，我将创建一个存储过程.另一个选择是通过第一个查询与PHP循环，然后为每个ID运行另一个查询-但是这种逻辑会大大降低您的页面速度.

If it were me I'd create a STORED PROCEDURE. The other option is to loop with PHP through the first query, then for each ID run another query - but this kind of logic can slow down your page drastically.

关于存储过程，这是一个很好的教程: http://net .tutsplus.com/tutorials/an-introduction-to-stored-procedures/

Here's a nice tutorial on stored procedures: http://net.tutsplus.com/tutorials/an-introduction-to-stored-procedures/

基本上，您运行与我上面提到的相同的循环(对于PHP)(但运行速度要快得多).该过程存储在数据库中，可以像函数一样调用.结果与查询相同.

Basically you run the same loops I mentioned above you would with PHP (but it runs much faster). The procedure is stored in the database and can be called like a function. The result is the same as a query.

根据要求，在我的实例中这是一个示例过程(或者，它使用了两个)，"ags_orgs"的行为类似于您的具有parentOrgID的类别. "getChildOrgs"的行为也像是一个冗余函数，因为我不知道我必须向下走几级(这是针对MSSQL编写的，可能与mySQL有所不同).不幸的是，这不计算行，而是获取数据.我强烈建议您遵循一两个教程，以更好地掌握其工作原理:

As requested, here's a sample procedure (or rather, it uses two) in my instance, "ags_orgs" acts in a similar way to your categories where there is a parentOrgID. "getChildOrgs" also acts kind of like a redundant function since I had no idea how many levels down I had to go (this was written for MSSQL - there are probably differences with mySQL) Unfortunately this doesn't count rows, rather it gets data. I highly recommend following a tutorial or two to get a better grip on how it works:

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

此proc调用哪个:

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

这篇关于所有子类别的MySQL Count产品的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！