我一直在尋找解決問題的方法，并嘗試了多種方法。
查詢試圖做的是匯總表“qo”和表“qoh”中的值并按“SKU”分組。然后它會從另一個中減去一個，給我一個我“矮”的值。
我的問題：
如果表“qoh”中沒有值，則 SKU 不存在，甚至不存在空值！我希望表“qo”中的總值保持不變，因為沒有任何東西可以扣除，實際發生的是它完全從表“qo”中洗掉行并且不回傳任何內容。
所以基本上，如果它在 qoh 中不存在，那么即使知道 qo 中有一個值，也不會回傳任何內容。
誰能看到我哪里出錯了？
謝謝
SELECT qo.sku_id
, SUM(qo.QTY_ORDERED_II) AS "Total ordered"
, SUM(qoh.total_inventory) AS "Total Inventory"
, ABS(SUM(QTY_ORDERED_II - TOTAL_INVENTORY)) AS "Short"
FROM
(SELECT SKU_ID, SUM(qty_ordered) AS QTY_ORDERED_II
FROM order_line
GROUP BY order_line.sku_id)
qo
JOIN
(SELECT sku_id, SUM(qty_on_hand) AS TOTAL_INVENTORY
FROM INVENTORY
GROUP BY sku_id)
qoh
ON qo.sku_id = qoh.sku_id
GROUP BY qo.sku_id
HAVING SUM(QTY_ORDERED_II - TOTAL_INVENTORY) > 0
ORDER BY SKU_ID;
uj5u.com熱心網友回復：
這是 SQL，以防萬一注釋不夠。
還要注意HAVING clause變化。
SELECT qo.sku_id
, SUM(qo.QTY_ORDERED_II) AS "Total ordered"
, SUM(qoh.total_inventory) AS "Total Inventory"
, ABS(SUM(QTY_ORDERED_II - COALESCE(TOTAL_INVENTORY, 0))) AS "Short"
FROM
(SELECT SKU_ID, SUM(qty_ordered) AS QTY_ORDERED_II
FROM order_line
GROUP BY order_line.sku_id)
qo
LEFT JOIN
(SELECT sku_id, SUM(qty_on_hand) AS TOTAL_INVENTORY
FROM INVENTORY
GROUP BY sku_id)
qoh
ON qo.sku_id = qoh.sku_id
GROUP BY qo.sku_id
HAVING SUM(QTY_ORDERED_II - COALESCE(TOTAL_INVENTORY, 0)) > 0
ORDER BY SKU_ID;
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/385705.html
上一篇：用于傳播幀值的SQL視窗函式