时间戳

首页 > 解决方案 > MySQL - 我什么时候不应该加入表？价值的组合爆炸

mysql - MySQL - 我什么时候不应该加入表？价值的组合爆炸

问题描述

我正在开发一个名为 的数据库classicmodels，我在以下位置找到了该数据库：https ://www.mysqltutorial.org/mysql-sample-database.aspx/

我意识到，当我Inner Join在“付款”和“订单”表之间执行时，发生了“笛卡尔爆炸”。我知道这两个表不是要连接的。但是，我想知道是否可以仅通过查看关系模式来识别这一点，或者我是否应该一一检查表。

例如，我通过使用以下代码找到的“订单表”中customer number '141'出现的时间：26

SELECT
    customerNumber,
    COUNT(customerNumber) 
FROM
    orders
WHERE customerNumber=141
GROUP BY customerNumber;

并且相同的客户编号 (141)13在支付表中出现次数：

SELECT
    customerNumber,
    COUNT(customerNumber)
FROM
    payments
WHERE customerNumber=141
GROUP BY customerNumber;

最后，我在 'payments' 和 'orders' 表之间执行了一个内部联接，并且只选择了客户编号为 '141' 的行。MySQL 返回338 rows，这是26*13. 所以，我的查询是将这个“客户 n°”出现在“订单”表中的次数乘以它出现在“付款”中的次数。

SELECT
    o.customernumber,
    py.amount
FROM
    customers c
        JOIN
    orders o ON c.customerNumber=o.customerNumber
        JOIN
    payments py ON c.customerNumber=py.customerNumber
WHERE o.customernumber=141;

我的问题如下：

1）有没有办法查看关系模式并确定是否可以执行连接（不产生组合爆炸）？还是我应该逐表检查以了解它们之间的关系？

这是“MySQL 教程”网站提供的数据库关系模式：

在此处输入图像描述

感谢您的关注！

标签： mysqlsqljoincross-join

解决方案

这被称为“组合爆炸”，当一个表中的行各自连接到其他表中的多行时，就会发生这种情况。

（这不是“高估”或任何形式的估计。它会多次计算数据项，而它应该只计算一次。）

在一对多关系中汇总数据是一个臭名昭著的陷阱。在您的示例中，每个客户可能没有订单、一个订单或多个订单。独立地，他们可能没有付款，一笔或多笔。

诀窍是：使用子查询，这样您的顶级查询与 GROUP BY 避免连续加入一对多关系。在您向我们展示的查询中，这种情况正在发生。

您可以使用此子查询来获取每个客户只有一行的结果集。（尝试一下。）

                    SELECT customernumber, 
                           SUM(amount) amount
                      FROM payments 
                  GROUP BY customernumber

同样，您可以通过此获取每个客户的所有订单的价值

                    SELECT c.customernumber, 
                           SUM(od.qytOrdered * od.priceEach) amount
                      FROM orders o
                      JOIN orderdetails od ON o.orderNumber = od.orderNumber
                     GROUP BY c.customernumber

这个 JOIN 不会在你面前爆炸，因为客户可以有多个订单，每个订单可以有多个详细信息。所以这是一个严格的分层汇总。

现在，我们可以在主查询中使用这些子查询。

SELECT c.customernumber, p.payments, o.orders 
  FROM customers c
  LEFT JOIN (
                    SELECT c.customernumber, 
                           SUM(od.qytOrdered * od.priceEach) orders
                      FROM orders o
                      JOIN orderdetails od ON o.orderNumber = od.orderNumber
                     GROUP BY c.customernumber
            ) o ON c.customernumber = o.customernumber
  LEFT JOIN (
                    SELECT customernumber, 
                           SUM() payment
                      FROM payments 
                  GROUP BY customernumber
            ) p on c.customernumber = p.customernumber

带回家的技巧：

  1. 子查询是一个表（虚拟表），可以在您可能提到表或视图的任何地方使用。
  2. 此查询中的 GROUP BY 内容分别在两个子查询中发生，因此没有组合爆炸。
  3. 顶级 JOIN 中的所有三个参与者每个customernumber.
  4. LEFT JOIN 在那里，所以我们仍然可以看到没有订单或没有付款的客户（对于企业来说很重要）。使用普通的内部 JOIN，行必须匹配 ON 条件的两侧，否则它们会从结果集中被忽略。

专业提示狂热地仔细格式化你的 SQL 查询：它们真的很冗长。格蕾丝·霍珀上将会感到自豪。这意味着它们变得相当长且嵌套，将结构化查询语言放入结构化查询语言中。如果您或任何人将来要对它们进行推理，我们必须能够轻松掌握结构。

专业提示 2设计这个数据库的数据工程师在思考和记录它方面做得非常好。渴望达到这种质量水平。（在现实世界中很少达到。）

推荐阅读