php - 计算整个类别的销售额

Question

大家好。

下面的例子显然是一个极端的伪例子，但它很好地代表了我面临的问题。

我已经为此苦苦挣扎了一段时间，但我还没有找到一个好的解决方案。

我需要打印出所有类别的列表(父类别也是如此!加上它们的子类别总销售额)及其产品的总销售额 - 按总销售额排序。

我需要打印的内容

+-------------+---------------+-------+
| category_id |   category    | sales |
+-------------+---------------+-------+
|           1 | shirts        |  1100 |
|           2 | t-shirts      |  1000 |
|           3 | tank-tops     |   100 |
|           4 | hats          |   100 |
|           5 | baseball caps |    50 |
|           6 | beanies       |    50 |
+-------------+---------------+-------+

表格

Table: categories
+-------------+--------------+
| category_id |   category   |
+-------------+--------------+
|           1 | shirts       |
|           2 | t-shirts     |
|           3 | tank-tops    |
|           4 | hats         |
|           5 | baseball caps|
|           6 | beanie       |
+-------------+--------------+

Table: categories_to_categories
+-----------------------------+---------------+----------------------+
| category_to_category_id | category_id | parent_category_id |
+-----------------------------+---------------+----------------------+
|                           1 |             1 |                    0 |
|                           2 |             2 |                    1 |
|                           3 |             3 |                    1 |
|                           4 |             4 |                    0 |
|                           5 |             5 |                    4 |
|                           6 |             6 |                    4 |
+-----------------------------+---------------+----------------------+

Table: products
+------------+-------------+------------------+-------+
| product_id | category_id |       name       | sales |
+------------+-------------+------------------+-------+
|          1 |           2 | black t-shirt    |   600 |
|          2 |           2 | blue t-shirt     |   400 |
|          3 |           3 | white tank-top   |   100 |
|          4 |           5 | red baseball cap |    50 |
|          5 |           6 | yellow beanie    |    50 |
+------------+-------------+------------------+-------+

这有可能吗？我已经尝试在整个 PHP 中使用一些递归函数，但它非常慢而且肯定不是最佳方式。

Answer 1

使用聚合函数并加入

   select c.category_id, 
   c.category ,sum(p.sales) as sum_sales from 
   product p inner join
   categories_to_categories ctc 
   on p.category_id=ctc.category_id
   inner join 
   categories c on ctc.parent_category_id=c.category_id     
   group by c.category_id, c.category
   order by sum_sales desc