問(wèn)題描述

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

# 查詢(xún)進(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

這個(gè)SQL查出來(lái)的出貨情況是不對(duì)的，請(qǐng)問(wèn)該怎么寫(xiě)？

問(wèn)題解答

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：

第二個(gè)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 )

不一樣的話(huà)

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 )