需求:一个产品有不同的订单,交付日期不同,现把所有的订单按照交付日期的先后依次展示出来,并找出此产品的库存,依次进行匹配操作,将能够满足订单的库存数量的标记出来。
预期样式:
思路:红色方框标记的列是不存在的,是需要通过计算出来的,注意需要库存总数这列,是累加的。
1.创建表,插入原始数据
// 1.1创建产品库存表
CREATE TABLE product_inventory(
product_name varchar(20),
inventory int(11)
)
// 1.2插入产品及库存数据
INSERT INTO product_inventory(product_name, inventory)
VALUES('型号-007', 200);
// 2.1创建订单表
CREATE TABLE order(
need_product_name varchar(20),
need_date datetime(3),
need_inventory int(11)
)
// 2.2插入订单的信息
INSERT INTO order(need_product_name, need_date, need_inventory)
VALUES
('型号-007', '2021-06-04 00:00:00.0', 10),
('型号-007', '2021-06-10 00:00:00.0', 20),
('型号-007', '2021-06-30 00:00:00.0', 30),
('型号-007', '2021-07-07 00:00:00.0', 40),
('型号-007', '2021-08-08 00:00:00.0', 50),
('型号-007', '2021-09-09 00:00:00.0', 60);
2.创建临时表 production_plan,目的是为了整合两张表的数据,顺便对该数据进行时间排序,使用自增id,自增id会使用到字段“需要库存总数”的累加计算。
CREATE TABLE production_plan (
id int(11) AUTO_INCREMENT,
need_product_name varchar(20),
need_date datetime(3),
need_inventory int(11),
inventory int(11),
PRIMARY KEY (id)
)
注意!:下面的sql,通过时间排序来进行id自增(id越大,need_date越大)
INSERT INTO production_plan
SELECT
0 AS id,
o.need_product_name,
o.need_date,
o.need_inventory,
i.inventory
FROM order o
INNER JOIN product_inventory i ON o.need_product_name = i.product_name
ORDER BY
o.need_date ASC
3.创建临时表 production_plan_sort, 对字段“需要库存总数”进行累加计算。
__ 这里是主要的思想步骤 __
// 增加字段 sum_need_inventory
CREATE TABLE production_plan_sort(
id int(11),
need_product_name varchar(20),
need_date datetime(3),
need_inventory int(11),
inventory int(11),
sum_need_inventory int(11)
)
INSERT INTO production_plan_sort
SELECT
t.id,
t.need_product_name,
t.need_date,
t.need_inventory,
t.inventory,
(
SELECT
sum(p.need_inventory)
FROM
production_plan p
WHERE
p.need_product_name = t.need_product_name
AND p.id <= t.id
) AS sum_need_inventory // 字段:需要库存总数
FROM production_plan t
4.现在已获得需要库存总数,即对数据进行展示
SELECT
need_product_name AS "产品",
inventory AS "库存",
(CASE WHEN inventory <= sum_need_inventory THEN
sum_need_inventory
ELSE
0
END) AS "已消耗库存",
(CASE WHEN inventory <= sum_need_inventory THEN
'Y'
ELSE
'N'
END) AS "是否能满足",
id AS "订单",
need_date AS "交付日期",
need_inventory AS "需要库存",
sum_need_inventory AS "需要库存总数"
FROM
production_plan_sort
ORDER BY
need_product_name,
id
这样就完成了一个简单版的逻辑运算,当然,实际的报表不可能怎么简单,这只是其中一个场景的思路。