来自表的多个左连接的 Mysql 查询是 "from"表的子集未按预期工作

Question

为入站调用创建一个数据库，其中包含每种调用类型的数据。我定义了一个包含“记录”表、“销售”表和“accounts_receivable”表的数据库。 “sales”和“accounts_receivable”表本质上是“records”表的子集。意思是，对于“sales”和“accounts_receivable”表(独占)的每一行，“records”表中都有对应的行。

我的“记录”表有以下值:

id (BIGINT)
timestamp (TIMESTAMP)
rep_id (INT)
notes (VARCHAR)

我的“销售”表有以下值:

local_record_id (BIGINT)
record_id (BIGINT)
amount (VARCHAR)
bottles_sold (VARCHAR)
record_type (VARCHAR) default 'sales'

我的“accounts_receivable”表有以下值:

local_record_id (BIGINT)
record_id (BIGINT)
amount (VARCHAR)
bottles_sold (VARCHAR)
record_type (VARCHAR) default 'ar'

我正在尝试提取整个数据库中的所有记录以及每条记录的适用数据。为此，我认为从“记录”表开始的 LEFT JOIN 会工作得很好，但由于某种原因它不是。这是我的查询:

SELECT *
FROM $table_name_records
LEFT JOIN $table_name_sales ON ($table_name_records.id = $table_name_sales.record_id)
LEFT JOIN $table_name_ar ON ($table_name_records.id = $table_name_ar.record_id)

这将返回一组结果，其中具有相似列名的子表中的数据被清空。

输出示例(对应于我数据库“销售”表中的行/条目):

Array (
    [id] => 1
    [timestamp] => 2014-12-17 13:11:07
    [rep_id] => 37
    [notes] => Some notes for you.
    [local_record_id] =>
    [record_id] =>
    [amount] =>
    [bottles_sold] =>
    [record_type] =>
)

我已经验证我的数据库的每个子表的每个列中都有数据，所以我不明白为什么这些值返回为空白。如果我只使用一个左连接进行查询:

SELECT *
FROM $table_name_records
LEFT JOIN $table_name_sales ON ($table_name_records.id = $table_name_sales.record_id)

我得到了我期望看到的:

Array (
    [id] => 1
    [timestamp] => 2014-12-17 13:11:07
    [rep_id] => 37
    [notes] => Some notes for you.
    [local_record_id] => 14
    [record_id] => 1
    [amount] => 45.45
    [bottles_sold] => Multiple
    [record_type] => sales
)

如果我只加入“accounts-receivable”表，工作方式与我预期的相同:

SELECT *
FROM $table_name_records
LEFT JOIN $table_name_ar ON ($table_name_records.id = $table_name_ar.record_id)

Array (
    [id] => 1
    [timestamp] => 2014-12-17 13:12:16
    [rep_id] => 37
    [notes] => Some notes.
    [local_record_id] => 6
    [record_id] => 2
    [amount] => 50.89
    [bottles_sold] => Single
    [record_type] => ar
)

我查看了很棒的图形化 SQL 连接文档 MySQL Joins ，那里的 LEFT JOIN 看起来正是我想要的:表 A(记录)中的所有内容都包含表 B(销售)中的数据，然后还有第二个表 B(accounts_receivable)中的数据附加到适当的行(根据 ON声明)。

我做错了什么，它没有像我期望的那样响应？

我只需要“销售”记录的查询通常是按另一个方向构建的。因此:

SELECT *
FROM $table_name_sales
LEFT JOIN $table_name_records ON ($table_name_records.id = $table_name_sales.record_id)

所以，我尝试从那个方向构建我的查询，然后通过以下方式加入他们:

SELECT *
FROM $table_name_sales
LEFT JOIN $table_name_records ON ($table_name_records.id = $table_name_sales.record_id)
UNION
SELECT *
FROM $table_name_ar
LEFT JOIN $table_name_records ON ($table_name_records.id = $table_name_ar.record_id)

这个查询返回的正是我所期望的:

Array (
    [id] => 1
    [timestamp] => 2014-12-17 13:11:07
    [rep_id] => 37
    [notes] => Some notes for you.
    [local_record_id] => 14
    [record_id] => 1
    [amount] => 45.45
    [bottles_sold] => Multiple
    [record_type] => sales
)

但在我看来，当我有很多记录和更多表(对应于不同类型的入站调用——例如:信息请求、订单问题等)时，以这种方式构建查询在未来可能会变得昂贵).不过，我对 MySQL 没有太多经验。

Answer 1

看起来您的 sales 和 accounts_receivable 表具有并行结构，并且每个表中的行都与您的 records 表相关但不是彼此。

@MichaelBerkowski 指出了尝试从 SELECT * 解释结果集的危险。

在我看来您需要以下类型的查询。

SELECT r.id AS record_id, r.timestamp, r.rep_id, r.notes AS record_notes,
       d.record_type, d.amount, d.bottles_sold
  FROM records AS r
  LEFT JOIN (
         SELECT record_id, record_type, amount, bottles_sold FROM sales
          UNION ALL
         SELECT record_id, record_type, amount, bottles_sold FROM accounts_receivable
       ) AS d ON d.record_id = r.id
   ORDER BY r.rep_id, r.id, r.timestamp, d.record_type DESC

这将按顺序显示每个代表的事件，交错“销售”和“销售”记录。

如果您分别加入 sales 和 accounts_receivable 表，您将得到不想要的组合爆炸。执行 UNION ALL 可以防止这种情况。

请注意，您可以为销售和 ar 数据使用一个物理表。