mysql子查询不返回正确的结果

Question

我有一个名为stock的mySQL表，查询返回item_id = 271的总库存。

select sum(qty) from stock where item_id = 271;

+----------+
| sum(qty) |
+----------+
|   127.00 |
+----------+
1 row in set (0.001 sec)

但是在我的原始查询中，我无法获得该item_id的正确库存值。

这是我的查询：

SELECT p.purchase_id
    , p.reference_number
    , pi.item_id
    , pi.received_qty
    , pi.cost_price_before_disc
    , pi.item_discount
    , round(pi.received_qty * pi.cost_price_after_disc, 2) as inline_total
    , sum(current_stock) as stock
FROM purchase_draft p 
  JOIN purchase_item_draft pi USING(purchase_id)
  JOIN item i ON pi.item_id = i.item_id
  LEFT JOIN (
              SELECT p.purchase_id
                   , pi.item_id
                   , sum(ifnull(s.qty, 0)) as current_stock
              FROM purchase_draft p 
                LEFT JOIN purchase_item_draft pi USING(purchase_id)
                LEFT JOIN stock s ON s.item_id = pi.item_id ANd qty_type = 'a'
              GROUP BY pi.item_id, p.purchase_id 
            ) s ON s.item_id = pi.item_id 
WHERE p.purchase_id = 4
GROUP BY purchase_id, pi.item_id

+-------------+------------------+---------+--------------+------------------------+---------------+--------------+--------+
| purchase_id | reference_number | item_id | received_qty | cost_price_before_disc | item_discount | inline_total | stock  |
+-------------+------------------+---------+--------------+------------------------+---------------+--------------+--------+
|           4 | PO2022/0004      |      92 |           60 |                1285.00 |          0.00 |     77100.00 |   0.00 |
|           4 | PO2022/0004      |     271 |           40 |                3396.00 |          0.05 |    135838.00 | 254.00 |
|           4 | PO2022/0004      |     407 |           10 |                3100.00 |        500.00 |     26000.00 |   0.00 |
|           4 | PO2022/0004      |     582 |           30 |                2898.00 |          0.21 |     86933.70 |   0.00 |
|           4 | PO2022/0004      |     583 |           20 |                1552.50 |          1.33 |     31023.40 |   0.00 |
|           4 | PO2022/0004      |     640 |           15 |                3285.00 |          2.00 |     49245.00 |   0.00 |
+-------------+------------------+---------+--------------+------------------------+---------------+--------------+--------+
6 rows in set (0.002 sec)

我能知道我在查询中做错了什么吗？

Answer 1

您需要在加入中汇总库存表。

替换

LEFT JOIN stock s ON s.item_id = pi.item_id ANd qty_type = 'a'

为：：

LEFT JOIN (
    SELECT item_id, sum(qty) AS qty
    FROM stock
    WHERE qty_type = 'a'
    GROUP BY item_id
) s ON s.item_id = pi.item_id