另一个涉及组合的复杂 MySQL 查询

Question

假设我们有一个如下所示的表。

connection_requirements
+-----------------------------------+ 
| item_id | connector_id | quantity | 
+---------+--------------+----------+ 
| 1       | 4            | 1        | 
| 1       | 5            | 1        | 
| 1       | 2            | 2        | 
+---------+--------------+----------+ 

该表列出了电子设备运行所需的连接器，以及每种类型的连接器需要多少个。 （想想主板上的连接需要来自电源的某些类型的连接器）

现在我们还有这个表...

connections_compatability 
+-------------------------+ 
| connector_id | works_as | 
+--------------+----------+ 
| 6            | 4        | 
| 6            | 5        | 
+--------------+----------+ 

其中第一列是连接器，也可以充当第二列的连接器 ID。 （例如，电源具有“6+2 针”等连接器，可以用作“8 针”或“6 针”）

现在，我们终于知道此表中有多少个可用的连接器

connector_quantities 
+-------------------------+ 
| connector_id | quantity | 
+--------------+----------+ 
| 1            | 1        | 
| 2            | 3        | 
| 3            | 2        | 
| 4            | 1        | 
| 5            | 0        | 
| 6            | 4        | 
| 7            | 0        | 
| 8            | 5        | 
+--------------+----------+ 

根据这些表，如下所示您可以推断，我们确实有足够的连接器供第 1 项正常运行。尽管我们没有足够的连接器 #5，但我们有 4 个连接器 #6，它们可以用作连接器 #4 和 #5。

connection_requirements 表连接到 items 表，我们如何过滤需要比我们可用连接更多的连接的项目？我们已经准备好代码来过滤需要不可用连接器的项目。

这个问题有更多的复杂性，所以我们试图简化问题。

非常感谢所有的帮助！

Answer 1

一种方法是确定物品的“真实”库存，包括其替代品。例如，第 4 部分的实际库存实际上是 5：1-第 4 部分 + 4-第 6 部分。因此，使用它：

Select ...
From connection_requirements As CR
Where Not Exists    (
                    Select 1
                    From connector_quantities As Q1
                        Left Join   (
                                    Select C2.connector_id, C2.works_as, Q2.quantity
                                    From connections_compatibility As C2
                                        Join connector_quantities As Q2
                                            On Q2.connector_id = C2.connector_id
                                    ) As Subs1
                            On Subs1.works_as = Q1.connector_id
                    Where Q1.connector_id = CR.connector_id
                        And ( Coalesce(Subs1.quantity, 0) + Q1.quantity ) > CR.quantity
                    )

这种方法当然有一个问题。假设您有一个产品的组成为：4 #4 连接器和 2 #6 连接器。从技术上讲，您确实有 4 个 #4 连接器（1 个 #4 和 3 个 #6 替代品），但结合 2 个 #6 连接器的要求，您没有足够的零件。要解决此问题，您可能必须使用循环或多个查询来确定用完所有主要零件后的现有库存。