mysql - iReport 中的 SQL Join 语句

Question

我正在处理一个应该显示“PART”的报告，它是我们的“WO”(工作订单表)和“SO”(销售订单表)中的项目数量。我相信我已经完成了格式设置和其他所有事情，但是出于某种原因，我似乎无法通过我的连接语句使我的逻辑正确。

我的逻辑基于 - 选择 PART_NUMBER 会在 WO 和 SO 中显示该零件的数量。然后它添加 SO 和 WO 的列总数，然后生成每个列的总计。

我是 SQL 的新手，处理这份报告已经 4 天了，但似乎无法让它发挥作用。我假设我的 JOINS 已关闭或其他。我们将不胜感激!

---

SELECT
 COALESCE(woitem.qtytarget,0) AS woallocated,
 COALESCE(soitem.qtyfulfilled,0) AS soallocated,
 PART."NUM" AS PART_NUM,
 WO."NUM" AS WO_NUM,
 PART."ID" AS PART_ID,
 WO."ID" AS WO_ID,
 PART."DESCRIPTION" AS PART_DESCRIPTION,
 SO."ID" AS SO_ID,
 SO."NUM" AS SO_NUM,
 WOITEM."ID" AS WOITEM_ID,
 WOITEM."QTYTARGET" AS WOITEM_QTYTARGET,
 SOITEM."ID" AS SOITEM_ID,
 SOITEM."QTYFULFILLED" AS SOITEM_QTYFULFILLED,
 WOITEM."WOID" AS WOITEM_WOID,
 WOITEM."PARTID" AS WOITEM_PARTID,
 COMPANY."ID" AS COMPANY_ID,
 COMPANY."NAME" AS COMPANY_NAME
FROM
 "PART" PART
 INNER JOIN "WO" WO ON PART."ID" = WO."ID"
 INNER JOIN "SO" SO ON PART."ID" = SO."ID"
 INNER JOIN "WOITEM" WOITEM ON PART."ID" = WOITEM."PARTID"
 AND WO."ID" = WOITEM."WOID"
 INNER JOIN "SOITEM" SOITEM ON SO."ID" = SOITEM."SOID",
 "COMPANY" COMPANY
WHERE
 PART."NUM"=$P{partNum}

我是 SQL 的新手，非常感谢对我逻辑的任何帮助!

Answer 1

尝试使用这个。您正在尝试内部连接并不真正相关的表，所以您会得到重复项。创建 2 个查询并将它们与 UNION ALL 合并

SELECT
 COALESCE(woitem.qtytarget,0) AS woallocated,
 0 AS soallocated,
 PART."NUM" AS PART_NUM,
 WO."NUM" AS WO_NUM,
 PART."ID" AS PART_ID,
 WO."ID" AS WO_ID,
 PART."DESCRIPTION" AS PART_DESCRIPTION,
 NULL AS SO_ID,
 NULL AS SO_NUM,
 WOITEM."ID" AS WOITEM_ID,
 WOITEM."QTYTARGET" AS WOITEM_QTYTARGET,
 NULL AS SOITEM_ID,
 0 AS SOITEM_QTYFULFILLED,
 WOITEM."WOID" AS WOITEM_WOID,
 WOITEM."PARTID" AS WOITEM_PARTID,
 COMPANY."ID" AS COMPANY_ID,
 COMPANY."NAME" AS COMPANY_NAME
FROM
 "PART" PART
 INNER JOIN "WOITEM" WOITEM ON PART."ID" = WOITEM."PARTID"
 INNER JOIN "WO" WO ON WOITEM."WOID" = WO."ID",
 "COMPANY" COMPANY
WHERE
 PART."NUM"=$P{partNum}

UNION ALL

SELECT
 0 AS woallocated,
 COALESCE(soitem.qtyfulfilled,0) AS soallocated,
 PART."NUM" AS PART_NUM,
 NULL AS WO_NUM,
 PART."ID" AS PART_ID,
 NULL AS WO_ID,
 PART."DESCRIPTION" AS PART_DESCRIPTION,
 SO."ID" AS SO_ID,
 SO."NUM" AS SO_NUM,
 NULL AS WOITEM_ID,
 0 AS WOITEM_QTYTARGET,
 SOITEM."ID" AS SOITEM_ID,
 SOITEM."QTYFULFILLED" AS SOITEM_QTYFULFILLED,
 NULL AS WOITEM_WOID,
 NULL AS WOITEM_PARTID,
 COMPANY."ID" AS COMPANY_ID,
 COMPANY."NAME" AS COMPANY_NAME
FROM
 "PART" PART
 INNER JOIN "PRODUCT" P ON PART."ID" = P."PARTID"
 INNER JOIN "SOITEM" SOITEM ON P."ID" = SOITEM."PRODUCTID"
 INNER JOIN "SO" SO ON SOITEM."SOID" = SO."ID",
 "COMPANY" COMPANY
WHERE
 PART."NUM"=$P{partNum} 

在第一个查询中仅选择 WO 项目，然后将其连接到 SO 项目。对于 WO 项目，SO 字段将为空，反之亦然