我在加入多個表時遇到問題。當我將另一個聯接添加到正確的查詢數量時，就會搞砸。我加入了狀態表，因為我想從那里獲取狀態。（使用 SSMS）
這是正確的代碼和實際正確的數量：
SELECT
TIT.PART_ID,
TIT.TRACE_ID,
IT.WAREHOUSE_ID,
IT.LOCATION_ID,
P.PRIMARY_WHS_ID,
P.PRIMARY_LOC_ID,
P.BACKFLUSH_WHS_ID,
P.BACKFLUSH_LOC_ID,
P.AUTO_BACKFLUSH,
SUM(TIT.QTY) AS QTY
FROM
TRACE_INV_TRANS TIT --TIT = TRACE INV TRANS TABLE
INNER JOIN INVENTORY_TRANS IT ON TIT.PART_ID = IT.PART_ID AND TIT.TRANSACTION_ID = IT.TRANSACTION_ID
LEFT JOIN PART_SITE P ON P.PART_ID = TIT.PART_ID
WHERE
IT.LOCATION_ID = 'DISPATCH'
AND TIT.QTY IS NOT NULL
GROUP BY
TIT.TRACE_ID,
TIT.PART_ID,
IT.WAREHOUSE_ID,
IT.LOCATION_ID,
P.PRIMARY_WHS_ID,
P.PRIMARY_LOC_ID,
P.BACKFLUSH_WHS_ID,
P.BACKFLUSH_LOC_ID,
P.AUTO_BACKFLUSH
HAVING
SUM(TIT.QTY) > 0
以正確數量執行的正確代碼
現在，當我嘗試加入另一張桌子時，我仍然希望獲得與上面顯示的相同數量。
這是我嘗試過的，但數量似乎已乘以 9 或其他東西。也可能是我的連接錯誤或者我犯了一個愚蠢的錯誤。
這是新代碼（加入Part_Location表格），但我的數量不正確（數量應與圖 1 中所示的相同）
SELECT
TIT.PART_ID,
TIT.TRACE_ID,
IT.WAREHOUSE_ID,
IT.LOCATION_ID,
L.STATUS,
P.PRIMARY_WHS_ID,
P.PRIMARY_LOC_ID,
P.BACKFLUSH_WHS_ID,
P.BACKFLUSH_LOC_ID,
P.AUTO_BACKFLUSH,
SUM(TIT.QTY) AS QTY
FROM
TRACE_INV_TRANS TIT --TIT = TRACE INV TRANS TABLE
INNER JOIN INVENTORY_TRANS IT ON TIT.PART_ID = IT.PART_ID AND TIT.TRANSACTION_ID = IT.TRANSACTION_ID
LEFT JOIN PART_SITE P ON P.PART_ID = TIT.PART_ID
INNER JOIN PART_LOCATION L ON L.PART_ID = TIT.PART_ID
WHERE
IT.LOCATION_ID = 'DISPATCH'
AND TIT.QTY IS NOT NULL
AND L.STATUS = 'A'
GROUP BY
TIT.TRACE_ID,
TIT.PART_ID,
IT.WAREHOUSE_ID,
IT.LOCATION_ID,
P.PRIMARY_WHS_ID,
P.PRIMARY_LOC_ID,
P.BACKFLUSH_WHS_ID,
P.BACKFLUSH_LOC_ID,
P.AUTO_BACKFLUSH,
L.STATUS
HAVING
SUM(TIT.QTY) > 0
在添加第 6、17、21 和 32 行但現在數量不正確的地方進行了更改
uj5u.com熱心網友回復：
您可以使用子查詢而不是聯接。如果任何 L.PART_ID 有多個狀態，則查詢將顯示錯誤。您需要根據您的 dbms 更改子查詢。
對于 sql 服務器：
select top 1 STATUS from PART_LOCATION L where L.PART_ID = TIT.PART_ID and L.STATUS = 'A'
對于 MySQL：
select STATUS from PART_LOCATION L where L.PART_ID = TIT.PART_ID and L.STATUS = 'A' Limit 1
詢問：
SELECT
TIT.PART_ID,
TIT.TRACE_ID,
IT.WAREHOUSE_ID,
IT.LOCATION_ID,
(select STATUS from PART_LOCATION L where L.PART_ID = TIT.PART_ID and L.STATUS = 'A'),
P.PRIMARY_WHS_ID,
P.PRIMARY_LOC_ID,
P.BACKFLUSH_WHS_ID,
P.BACKFLUSH_LOC_ID,
P.AUTO_BACKFLUSH,
SUM(TIT.QTY) AS QTY
FROM
TRACE_INV_TRANS TIT --TIT = TRACE INV TRANS TABLE
INNER JOIN INVENTORY_TRANS IT ON TIT.PART_ID = IT.PART_ID AND TIT.TRANSACTION_ID = IT.TRANSACTION_ID
LEFT JOIN PART_SITE P ON P.PART_ID = TIT.PART_ID
WHERE
IT.LOCATION_ID = 'DISPATCH'
AND TIT.QTY IS NOT NULL
GROUP BY
TIT.TRACE_ID,
TIT.PART_ID,
IT.WAREHOUSE_ID,
IT.LOCATION_ID,
P.PRIMARY_WHS_ID,
P.PRIMARY_LOC_ID,
P.BACKFLUSH_WHS_ID,
P.BACKFLUSH_LOC_ID,
P.AUTO_BACKFLUSH
HAVING
SUM(TIT.QTY) > 0
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/491574.html