MYSQL基于多级产品的产品总销售额

Question

希望确定特定产品的总销售额。该产品可能是也可能不是父产品的一部分。例如，一个产品可能包含在一组父产品中，而该父产品又可以包含在父产品中，而父产品又......等等......

实际上只有2个表需要引用:

订单_产品

orders_products_id
products_id
products_quantity

分层产品

parent_id
subproduct_id
subproduct_qty

我们使用的表格内容如下:

订单_产品

orders_products_id | products_id | products_qty 
-------------------+-------------+--------------
      8922         |     7232    |      1       
      8711         |     6823    |      2       
      8658         |     6823    |      1      
      8633         |     6823    |      2       
      8702         |     7538    |      1       
      8690         |     7538    |      1      
      8622         |     7538    |      2       

分层产品

parent_id + subproduct_id + subproduct_qty +
----------+---------------+----------------+
  6823    +      7232     +        1       +
  7538    +      6823     +        4       +

此过程可以通过使用单独的查询来完成。

SELECT IFNULL(SUM(products_qty),0) AS total_qty 
FROM orders_products 
WHERE products_id = '7232';

查询结果=total_qty=1

SELECT lp.parent_id, lp.subproduct_qty, 
       IFNULL(SUM(op.products_qty) * lp.subproduct_qty,0) AS total_qty 
FROM layered_products lp 
LEFT JOIN orders_products op ON op.products_id = lp.parent_id 
WHERE lp.subproduct_id = '7232';

查询结果=parent_id=6823;子产品数量 = 1；总数量 = 5

SELECT lp.parent_id, lp.subproduct_qty, 
       IFNULL(SUM(op.products_qty) * lp.subproduct_qty * [prior select subproduct_qty],0) AS total_qty 
FROM layered_products lp 
LEFT JOIN orders_products op ON op.products_id = lp.parent_id 
WHERE lp.subproduct_id = [prior select parent_id];

查询结果=parent_id=7538；子产品数量 = 4；总数量 = 16

所有查询的总和 =total_qty = 22

但是，我正在尝试将所有内容都放入一个 SELECT 中。 SQL 专家中有人知道如何实现这一点吗？已经工作了一个多星期了，但只能完成一个级别的工作。第二个父级根本不起作用。尝试了子查询、派生查询以及我能找到的任何其他内容。另外，如果可能的话，希望能够处理额外的级别。提前致谢...

Answer 1

MySQL 不支持递归查询，因此没有干净的方法来执行此操作。

您可以使用 stored proc ，但可能会很慢。

编辑:我想到的最方便的方法...就像数据库如何执行“WITH RECURSIVE”查询...

开始:我们有一个列表“L”，其中包含我们想要列出子元素的元素。

SELECT child_id WHERE parent_id IN (L)

...结果进入列表 L2...

SELECT child_id WHERE parent_id IN (L2)

...等等...继续下去，直到查询没有返回任何结果。

虽然麻烦又烦人，但这是唯一的方法。在应用程序中的存储过程中执行此操作...考虑到 MySQL 存储过程的速度较慢，并且存储过程必须使用临时表，我不确定存储过程实际上是否具有任何性能优势.

但是还有另一种方法。我用过，不记得在哪里了...

为每个元素提供一条由其父元素的 id 组成的路径。例如:

relation table:
parent_id    child_id
12           34
34           567

objects table (products here, but could be anything)
obj_id   path
12       00000012
34       00000012/00000034
567      00000012/00000034/00000567

现在，如果路径是由触发器生成的，则可以使路径保持最新，尽管将具有大量子元素的一个元素移动到不同的父元素将需要更新其所有子路径，因此可能会很慢。

现在，获取元素的子元素是通过以下方式完成的:

path LIKE '00000012/%'

...如果路径上有 btree，那么这是可索引的。速度 super 快。

但是，您需要对您的情况进行一些后期处理，因此会有点头疼。而且，它非规范化，而且有点丑陋。你的选择!