mysql查询查找给定天数的开始和结束日期

Question

我正在尝试查询下表，根据每件商品的库存时间为 30 天，给出开始日期和结束日期。 (库存不连续)

例如，如果过去 40 天中有 30 天该商品有库存，那么当我们达到 30 天时，我会得到该商品有库存的第一个日期以及该商品有库存的最后一天。结束日期应尽可能接近今天。我知道我可以在 php 代码中执行此操作，但表会变大并且难以存储在内存中。

这在 SQL 中可能吗？

 CREATE TABLE phppos_inventory_stock_log (
  id int(10) NOT NULL AUTO_INCREMENT,
  in_stock int(1) NOT NULL DEFAULT '0',
  date date NOT NULL,
  item_id int(10) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY in_stock (item_id,in_stock,date)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

mysql> select * FROM phppos_inventory_stock_log;
+----+----------+------------+---------+
| id | in_stock | date       | item_id |
+----+----------+------------+---------+
|  1 |        1 | 2018-03-10 |       2 |
|  2 |        1 | 2018-03-11 |       2 |
|  3 |        1 | 2018-03-12 |       2 |
|  4 |        1 | 2018-03-13 |       2 |
|  5 |        1 | 2018-03-10 |       1 |
|  6 |        0 | 2018-03-11 |       1 |
|  7 |        1 | 2018-03-12 |       1 |
|  8 |        1 | 2018-03-13 |       1 |

在上面的示例中，如果库存商品有 3 天，商品 ID 2 的开始日期将为 2018-03-11 至 2018-03-13，商品 ID 1 将为 2018-03-10 至 2018-03-13

Answer 1

如果我正确理解了这个问题，您需要一个该商品库存量为 30 倍的日期。

首先，我们需要找到该商品有货的最晚日期 max(date)，然后我们为每个商品选择有货的时间，并按以下顺序排序天，我们限制在我们感兴趣的范围(30 天)并选择最早的日期。

SELECT max(`date`) AS max_date, item_id,
  (SELECT MIN(`date`)
   FROM phppos_inventory_stock_log AS chsl
   WHERE chsl.item_id = psl.item_id
     AND in_stock = 1
   ORDER BY `date` DESC
   LIMIT 30) AS min_date
FROM phppos_inventory_stock_log AS psl
WHERE in_stock = 1
GROUP BY item_id

注意:如果你有大量数据，你会杀死数据库。限制返回结果的数量或检索特定 item_id 的列表。