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

您需要在join中聚合股票表。

替换:

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