MYSQL 多表拼接link

Question

场景：mysql中以合同表为主表，查询发票表，收款单表，报销单表，实现合同执行信息的查询。使用JOIN的话发现出现了很对交叉数据，请问怎么才能实现合同只有一条，后面分别显示发票，收款，报销情况
合同表
合同号 合同类型 合同签订日期
HT001 物业合同 2024/1/1
HT002 安保合同 2024/1/1
HT003 服务合同 2024/1/1
HT004 服务合同 2024/1/1
HT005 服务合同 2024/1/1

发票表
发票号 合同号 发票金额
FP001 HT001 100
FP002 HT001 100
FP003 HT002 100
FP004 HT002 100
FP005 HT003 100
FP006 HT004 100

收款单表
收款单号 合同号 收款金额
SK001 HT001 200
SK002 HT001 200
SK003 HT003 200
SK004 HT003 200
SK005 HT003 200
SK006 HT005 200

报销单表
报销单号 合同号 报销金额
BX001 HT001 300
BX002 HT001 300
BX003 HT002 300
BX004 HT002 300
BX005 HT003 300
BX006 HT004 300
BX007 HT005 300

想要的结果表
合同号 合同类型 合同签订日期 发票号 发票金额 收款单号 收款金额 报销单号 报销金额
HT001 物业合同 2024/1/1 FP001 100 SK001 200 BX001 300
HT001 物业合同 2024/1/1 FP002 100 SK002 200 BX002 300
HT002 安保合同 2024/1/1 FP003 100 BX003 300
HT002 安保合同 2024/1/1 FP004 100 BX004 300
HT003 服务合同 2024/1/1 FP005 100 SK003 200 BX005 300
HT003 服务合同 2024/1/1 FP005 100 SK004 200 300
HT003 服务合同 2024/1/1 FP005 100 SK005 200 300
HT004 服务合同 2024/1/1 FP006 100 BX006 300
HT005 服务合同 2024/1/1 SK006 200 BX007 300

各位不要为了回答而回答，上来就说left join的可不可以仔细看看题啊…left join会出现笛卡尔啊！

Answer 1

🍀🍀🍀本答案参考ChatGPT，并由Seal^_^整理回复。

为了解决这个问题，你需要使用一种方法来避免JOIN时产生的笛卡尔积，并确保每个合同号只与对应的发票、收款单和报销单匹配一次（或多次，但每次只对应一个具体的单据）。这通常需要使用某种形式的行号匹配或分组聚合来确保每行输出都是唯一的且逻辑上正确。

然而，直接在SQL中处理这种多对多关系并避免笛卡尔积，特别是在需要显示所有相关行的情况下，可能比较复杂。一个常见的方法是使用窗口函数（如ROW_NUMBER()）来为每个合同下的发票、收款单和报销单分配一个唯一的行号，然后基于这些行号进行JOIN。但这种方法在MySQL中可能会比较复杂，因为MySQL的窗口函数是在较新版本中引入的。

下面是一个简化的思路，使用子查询和GROUP_CONCAT（或其他聚合函数）来先聚合发票、收款单和报销单的信息，然后在主查询中通过合同号来匹配这些信息。但请注意，这种方法不会完全按照你的期望格式输出（因为它会将多个发票/收款单/报销单的信息聚合到一个字段中），但我们可以稍微调整它以更接近你的目标。

首先，我们可以尝试一个简化的版本，它使用GROUP BY和GROUP_CONCAT来合并相同合同下的多个单据信息，但这可能不是你想要的精确格式：

SELECT
    h.合同号,
    h.合同类型,
    h.合同签订日期,
    GROUP_CONCAT(DISTINCT CONCAT(f.发票号, ' ', f.发票金额) ORDER BY f.发票号 SEPARATOR ', ') AS 发票信息,
    GROUP_CONCAT(DISTINCT CONCAT(s.收款单号, ' ', s.收款金额) ORDER BY s.收款单号 SEPARATOR ', ') AS 收款信息,
    GROUP_CONCAT(DISTINCT CONCAT(b.报销单号, ' ', b.报销金额) ORDER BY b.报销单号 SEPARATOR ', ') AS 报销信息
FROM
    合同表 h
LEFT JOIN 发票表 f ON h.合同号 = f.合同号
LEFT JOIN 收款单表 s ON h.合同号 = s.合同号
LEFT JOIN 报销单表 b ON h.合同号 = b.合同号
GROUP BY
    h.合同号,
    h.合同类型,
    h.合同签订日期;

但如果你确实需要每个合同号下的每个单据都单独显示，并且不想使用复杂的窗口函数或存储过程，你可能需要考虑在应用层（如Python、Java等）进行更复杂的逻辑处理，或者考虑使用数据库视图和临时表来逐步构建所需的结果集。

然而，如果你坚持要在SQL中实现，并且MySQL版本支持窗口函数，你可以尝试使用ROW_NUMBER()为每个单据分配一个行号，并在JOIN时匹配这些行号。但这种方法将大大增加SQL查询的复杂性，并且可能不如在应用层处理来得直观和高效。

最后，请注意，由于你的需求中每个合同号下可能有多个发票、收款单和报销单，而你又希望它们在同一行中显示（但又不完全是笛卡尔积），这实际上是一个挑战，因为SQL的结果集本质上是二维的，很难直接表示出这种复杂的多对多关系而不产生重复或遗漏。