mysql - 合并多个查询 - 同一表

Question

我有 1 个名为 itemmovement 的表:它有 Item Id 、 Quantity In 、 Quantity Out 、 Invoice Id 、 Date 。我需要在一个查询中显示已售出多少件，并且在已售出列旁边将显示当前的现有数量。

元素移动

Id   itemid      qtyin     qtyout    invid    purchasereturnid          date1     1            10                                                2019-01-042     2            8                                                 2019-01-063     2                      2         1                             2019-01-084     1                      3         2                             2019-01-125     2            1                                                 2019-02-046     3            4                                                 2019-03-047     1                      1         3                             2019-04-048     1                      1                      1                2019-04-149     3                      1                      2                2019-04-24

I need the query to show this result

Id   itemid      Sold Quantity   OnHandQty1     1               4             52     2               2             73     3               0             3

I'm Trying to use this query but not working

SELECT *
FROM
  (SELECT itmv.itemid,
          sum(itmv.qtyout)-sum(itmv.qtyin)
   FROM itemmovement itmv
   WHERE (itmv.systemdate BETWEEN '2019-01-01' AND '2019-06-01')
     AND invid>0
   GROUP BY itmv.itemid) AS result1,

  (SELECT sum(itmv2.qtyin)-sum(itmv2.qtyout)
   FROM itemmovement itmv2
   WHERE itmv.itemid=itmv2.itemid
   GROUP BY itmv2.itemid) AS result2
ORDER BY sum(itmv.qtyin)-sum(itmv.qtyout)

我得到:

Unknown column 'itmv.itemid' in 'where clause it for this syntax :

where itmv.itemid = itmv2.itemid

Answer 1

这是您的查询。

select itemid
    , sum(case when COALESCE(invid,0) > 0 then qtyout else 0 end) as Sold_Qantity
    , sum(qtyin)-sum(qtyout) as OnHandQty
from itemmovement
group by itemid