問題描述

stock(進(jìn)貨單表)stock_id -- 進(jìn)貨單ID主鍵stock_product(進(jìn)貨單和產(chǎn)品關(guān)系表)stock_id -- 進(jìn)貨單IDproduct_sn -- 產(chǎn)品編號(廠家提供的編號，每個產(chǎn)品一個)product_no -- 產(chǎn)品自編號(針對該進(jìn)貨單的每個產(chǎn)品的自編號，同一進(jìn)貨單內(nèi)自編號和產(chǎn)品編號是一一對應(yīng)，但不同進(jìn)貨單里可能有相同的自編號對應(yīng)不同的廠家產(chǎn)品編號)quantity -- 進(jìn)貨數(shù)量shipment(出貨單表)shipment_id -- 出貨單IDstock_id -- 進(jìn)貨單IDshipment_product(出貨單和產(chǎn)品關(guān)系表)shipment_id -- 出貨單IDproduct_no -- 產(chǎn)品自編號quantity -- 出貨數(shù)量

# 查詢進(jìn)貨單ID為1的產(chǎn)品出貨數(shù)量明細(xì)SELECT sh.stock_id, st_p.product_sn, sh_p.quantityFROM shipment_product sh_p LEFT JOIN shipment sh ON sh.shipment_id=sh_p.shipment_id LEFT JOIN stock_product st_p ON st_p.product_no=sh_p.product_no WHERE sh.stock_id=1

這個SQL查出來的出貨情況是不對的，請問該怎么寫？

問題解答

SELECT *FROM shipment_product shipment_productLEFT JOIN shipment shipment ON shipment.shipment_id = shipment_product.shipment_idWHERE shipment_product.product_no IN (SELECT stock_product.product_noFROM stock_product stock_productLEFT JOIN stock stock ON stock.stock_id = stock_product.stock_idWHERE stock_product.stock_id = 1 )回答2：

第二個left join stock表試試吧

如果 stock_product表中的字段product_no等于shipment_product表

SELECT *FROM shipment_product AS sh_pWHERE product_no = ( SELECT stock_id FROM stock_product AS st_p WHERE st_p.stock_id = 1 )

不一樣的話

SELECT *FROM shipment_product AS sh_pWHERE sh_p.shipment_id = ( SELECT shipment_id FROM shipment AS st_p WHERE st_p.stock_id = 1 )