weixin_41652071 2024-09-16 22:15 采纳率: 0%
浏览 15
已结题

MYSQL 多表拼接link

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

img

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

img

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

img

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

img

想要的结果表
合同号 合同类型 合同签订日期 发票号 发票金额 收款单号 收款金额 报销单号 报销金额
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

img

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

  • 写回答

25条回答 默认 最新

  • Seal^_^ 云原生领域优质创作者 2024-09-17 16:23
    关注

    🍀🍀🍀本答案参考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的结果集本质上是二维的,很难直接表示出这种复杂的多对多关系而不产生重复或遗漏。

    评论

报告相同问题?

问题事件

  • 已结题 (查看结题原因) 9月19日
  • 修改了问题 9月16日
  • 赞助了问题酬金15元 9月16日
  • 创建了问题 9月16日

悬赏问题

  • ¥15 unity从3D升级到urp管线,打包ab包后,材质全部变紫色
  • ¥50 comsol温度场仿真无法模拟微米级激光光斑
  • ¥15 上传图片时提交的存储类型
  • ¥15 Ubuntu开机显示器只显示kernel,是没操作系统(相关搜索:显卡驱动)
  • ¥15 VB.NET如何绘制倾斜的椭圆
  • ¥15 arbotix没有/cmd_vel话题
  • ¥15 odoo17的分包重新供应路线如何设置?可从销售订单中实时直接触发采购订单或相关单据
  • ¥15 用C语言怎么判断字符串的输入是否符合设定?
  • ¥15 通信专业本科生论文选这两个哪个方向好研究呀
  • ¥50 我在一个购物网站的排队系统排队,这个排队到号后重新定向到目标网站进行购物,但是有技术牛通过技术方法直接跳过排队系统进入目标网址购物,有没有什么软件或者脚本可以用