mysql如何不使用窗口函数，去统计出入库情况

mysql如何不使用窗口函数，去统计出入库情况

你把这个表看做 进出库表，每个物料把时间正序后 依次累加数量 ，看这个物料的时间线上 是否会出现负数，1号进货5个 2号出库3个  3号你不能出库3个 最多俩个 不然就是负库存，说白 就是按时间线累加每天的数据 每个时间线不能出现 负数。需要用mysql 写出来   aa date num A 2024-01-03   2 B 2024-01-05   -2  。要求不能使用窗口函数。    CREATE TABLE your_table (material_code VARCHAR(10),invoice_date DATE,quantity INT
);INSERT INTO your_table (material_code, invoice_date, quantity)
VALUES('A', '2024-01-03', 2),('A', '2024-01-04', 4),('A', '2024-01-05', -2),('A', '2024-01-05', -5),('A', '2024-01-05', 5),('A', '2024-01-06', -9),('A', '2024-01-07', 5),('B', '2024-01-03', 2),('B', '2024-01-04', 4),('B', '2024-01-05', -2),('B', '2024-01-05', -5),('B', '2024-01-05', 5),('B', '2024-01-06', -9),('B', '2024-01-07', 5);

你要通过 MySQL 查询每个物料的库存变化，并检查在时间线上的库存是否会出现负数。在这个场景下，我们需要按照 invoice_date 进行累加库存，累加时一旦发现某一天的库存变为负数，就可以将其标记为库存不足。

这个查询需要以下步骤：

1. 按照 material_code 和 invoice_date 将数据按时间升序排列。
2. 对于每个物料，逐步累加 quantity，同时判断是否有任何时间点的库存是负数。
3. 使用 变量 来累加数量并检查是否为负数，而不使用窗口函数。

MySQL 查询：

-- 定义变量
SET @current_stock = 0;
SET @prev_material = '';-- 查询每个物料的时间线库存变化，查看是否出现负数
SELECT material_code, invoice_date, quantity, @current_stock := CASE WHEN @prev_material = material_code THEN @current_stock + quantityELSE quantityEND AS stock_after_transaction,@prev_material := material_code AS material_code_check,CASE WHEN @current_stock < 0 THEN '库存不足'ELSE '库存充足'END AS stock_status
FROM your_table
ORDER BY material_code, invoice_date;

解释：

1. @current_stock：一个变量，记录每个物料的当前累积库存。
2. @prev_material：记录前一个物料的 material_code，以确保如果物料切换，库存会重新计算。
3. 累加逻辑：如果当前物料与上一个相同，累加库存。如果物料变了，重置库存累加。
4. 库存检查：如果某个时间点的库存小于 0，会标记为 库存不足。

执行结果示例：

material_code	invoice_date	quantity	stock_after_transaction	stock_status
A	2024-01-03	2	2	库存充足
A	2024-01-04	4	6	库存充足
A	2024-01-05	-2	4	库存充足
A	2024-01-05	-5	-1	库存不足
A	2024-01-05	5	4	库存充足
A	2024-01-06	-9	-5	库存不足
A	2024-01-07	5	0	库存充足
B	2024-01-03	2	2	库存充足
B	2024-01-04	4	6	库存充足
B	2024-01-05	-2	4	库存充足
B	2024-01-05	-5	-1	库存不足
B	2024-01-05	5	4	库存充足
B	2024-01-06	-9	-5	库存不足
B	2024-01-07	5	0	库存充足

说明：

• stock_after_transaction 是每个时间点的库存。
• stock_status 表明库存是否充足，当库存低于 0 时显示 “库存不足”。

通过这个查询，你可以监控物料的库存变化，并及时发现负库存的情况。

============================================

=============================================================

【第二版：日期聚合】

好的，理解你的意思了。你希望在相同日期的 quantity 先进行聚合，然后再按日期累加库存变化。

我们可以在查询中加入一个聚合步骤，先对相同 material_code 和 invoice_date 的 quantity 进行汇总，之后再按照时间顺序进行库存的累加和检查。

更新的 MySQL 查询：

-- 定义变量
SET @current_stock = 0;
SET @prev_material = '';-- 先对相同日期的数量进行聚合，再按日期累加库存并检查是否为负
SELECT material_code, invoice_date, total_quantity, @current_stock := CASE WHEN @prev_material = material_code THEN @current_stock + total_quantityELSE total_quantityEND AS stock_after_transaction,@prev_material := material_code AS material_code_check,CASE WHEN @current_stock < 0 THEN '库存不足'ELSE '库存充足'END AS stock_status
FROM (-- 聚合相同日期的数量SELECT material_code, invoice_date, SUM(quantity) AS total_quantityFROM your_tableGROUP BY material_code, invoice_date
) AS aggregated_table
ORDER BY material_code, invoice_date;

解释：

1. 内部子查询 aggregated_table：

   • 通过 GROUP BY material_code, invoice_date 对相同物料、相同日期的数据进行聚合，使用 SUM(quantity) 来计算每天的总数量。

2. 外部查询：

   • 使用变量 @current_stock 来进行库存累加，逻辑与之前相同。如果物料切换，库存重新计算。
   • 继续检查库存是否为负数，并根据结果显示 “库存不足” 或 “库存充足”。

执行结果示例：

material_code	invoice_date	total_quantity	stock_after_transaction	stock_status
A	2024-01-03	2	2	库存充足
A	2024-01-04	4	6	库存充足
A	2024-01-05	-2	4	库存充足
A	2024-01-06	-9	-5	库存不足
A	2024-01-07	5	0	库存充足
B	2024-01-03	2	2	库存充足
B	2024-01-04	4	6	库存充足
B	2024-01-05	-2	4	库存充足
B	2024-01-06	-9	-5	库存不足
B	2024-01-07	5	0	库存充足

说明：

• total_quantity 是经过日期聚合后的数量。
• 之后的逻辑和之前一样，累加库存并检查是否为负。

这样，查询会先对同一天的数量进行汇总，然后再按照日期累加库存，确保你可以监控到任何可能出现负库存的情况。