mysql - 从一个值中减去多个值

Question

我必须维护库存的账龄，我必须向库存部门表明一种产品的数量从多少天开始待定。

它将按照先到先得的原则减去...

Department  |Product  | Date               | Quantity  |Indicator
------------+---------+--------------------+-----------+---------
D001        |P001     | 1Jul2017           | 1000      |ADD
D001        |P001     | 10Jul2017          | 2000      |ADD
D001        |P001     | 15Jul2017          | 3000      |ADD
D001        |P001     | 18Jul2017          | 5500      |SUBTRACT
D001        |P002     | 12Jul2017          | 3000      |ADD
D001        |P002     | 14Jul2017          | 2000      |ADD
D001        |P002     | 15Jul2017          | 4000      |SUBTRACT

最终的输出应该是这样的。如果今天的日期是 2017 年 7 月 31 日

Department  |Product  | Date               | Quantity  |AgeinginDays
------------+---------+--------------------+-----------+---------
D001        |P001     | 15Jul2017          | 500       |16
D001        |P002     | 14Jul2017          | 1000      |17

Answer 1

可以使用聚合函数和 datediff

select Department
  , Product
  , max(Date)
  , sum ( case when indicator =  'ADD' then quantity else -quantity end)
  , datediff(curdate(), max(Date))
from my_table
group by  Department, Product

或者如果您需要添加日期，则按照 chillysapiens 的建议

select Department
  , Product
  , t.max_add_date
  , sum ( case when indicator =  'ADD' then quantity else -quantity end)
  , datediff(curdate(), max(t.max_add_date))
from my_table
inner join  (
    select Department
      , Product
      , max(Date) max_add_date
    from my_table 
    where indicator = 'ADD'
    group by  Department, Product
 ) t 
group by  Department, Product