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 进行累加库存，累加时一旦发现某一天的库存变为负数，就可以将其标记为库存不足。
这个查询需要以下步骤：
- 按照
material_code和invoice_date将数据按时间升序排列。 - 对于每个物料，逐步累加
quantity，同时判断是否有任何时间点的库存是负数。 - 使用
变量来累加数量并检查是否为负数，而不使用窗口函数。
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;
解释：
- @current_stock：一个变量，记录每个物料的当前累积库存。
- @prev_material：记录前一个物料的
material_code，以确保如果物料切换，库存会重新计算。 - 累加逻辑：如果当前物料与上一个相同，累加库存。如果物料变了，重置库存累加。
- 库存检查：如果某个时间点的库存小于 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;
解释：
-
内部子查询
aggregated_table：- 通过
GROUP BY material_code, invoice_date对相同物料、相同日期的数据进行聚合，使用SUM(quantity)来计算每天的总数量。
- 通过
-
外部查询：
- 使用变量
@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是经过日期聚合后的数量。- 之后的逻辑和之前一样，累加库存并检查是否为负。
这样，查询会先对同一天的数量进行汇总，然后再按照日期累加库存，确保你可以监控到任何可能出现负库存的情况。