php - MySql查询从库存表中获取未售出的产品

Question

我的项目中有 2 个表。一个是“产品”，另一个是“库存变动”。

“产品”表只是一个主表，其中加载了我们所有的产品。它只有 2 个字段 id - 产品 ID 的自动递增字段 产品名称 - VarChar 255 产品名称的唯一列

“库存变动” 表如下所示

id - Auto increment field

movement_type - Enum field (IN, OUT)

product - id from product table

quantity - No of items moving

movement_time - timestamp of action

运动表的示例数据...

id     movement_type   product   Quantity  movement_time

1      IN              1001      10        2018-02-01 12:35:33

2      IN              1002      15        2018-02-01 13:33:33

3      OUT             1001      5         2018-03-01 11:00:33

现在我必须获取一份报告，其中列出了所有产品及其最后“OUT”和最后“IN”类型记录之间的时间差。

对于单个产品，我编写了这样的查询...

1) 获取最后一条“OUT”记录；

 select movement_time from inventory_movement where product = '1001' and movement_type='OUT' order by movement_time desc limit 1

2) 获取最后一条“IN”记录；

 select movement_time from inventory_movement where product = '1001' and movement_type='IN' order by movement_time desc limit 1

3) 查找两个时间戳之间的差异。

这就是我如何知道产品及其 ID 的未售出持续时间。

我知道这是无效的方法...有什么方法可以在单个查询中做到这一点？

我是否可以获得具有此时间戳差异的产品列表？

即...以下是预期输出...

Product     ----     Time Difference between last Out and last In


1001      ----        40 days

1002      ----        45 days

1004      ----        12 days

是否可以使用单个查询？

Answer 1

似乎条件 MIN 和 MAX 之间的 DATEDIFF 应该可以。

SELECT product,
DATEDIFF(MAX(case when movement_type='OUT' then movement_time end), MIN(case when movement_type='IN' then movement_time end)) AS InOutDayDiff
from inventory_movement 
GROUP BY product
ORDER BY product

你可以测试一下here