一个mysql联表查询的问题

Question

stock(进货单表)
stock_id -- 进货单ID主键

stock_product(进货单和产品关系表)
stock_id -- 进货单ID
product_sn -- 产品编号(厂家提供的编号，每个产品一个)
product_no -- 产品自编号(针对该进货单的每个产品的自编号，同一进货单内自编号和产品编号是一一对应，但不同进货单里可能有相同的自编号对应不同的厂家产品编号)
quantity -- 进货数量

shipment(出货单表)
shipment_id -- 出货单ID
stock_id -- 进货单ID

shipment_product(出货单和产品关系表)
shipment_id -- 出货单ID
product_no -- 产品自编号
quantity -- 出货数量

# 查询进货单ID为1的产品出货数量明细
SELECT
  sh.stock_id,
  st_p.product_sn,
  sh_p.quantity
FROM 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查出来的出货情况是不对的，请问该怎么写？

Answer 1

SELECT
    *
FROM
    shipment_product shipment_product
LEFT JOIN shipment shipment ON shipment.shipment_id = shipment_product.shipment_id
WHERE
    shipment_product.product_no IN (
        SELECT
            stock_product.product_no
        FROM
            stock_product stock_product
        LEFT JOIN stock stock ON stock.stock_id = stock_product.stock_id
        WHERE
            stock_product.stock_id = 1
    )

Answer 2

第二个left join stock表试试吧

Answer 3

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

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

不一样的话

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