设计为多个记录按一个总量来分配
/*
Navicat Premium Data Transfer
Source Server : overseawmsomsdev
Source Server Type : MySQL
Source Server Version : 80026
Source Host : localhost:3306
Source Schema : mytest
Target Server Type : MySQL
Target Server Version : 80026
File Encoding : 65001
Date: 26/11/2021 20:53:30
*/
SET NAMES utf8mb4;
-- ----------------------------
-- Table structure for ta
-- ----------------------------
DROP TABLE IF EXISTS `ta`;
CREATE TABLE `ta` (
`id` int(0) NOT NULL,
`gp` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`need_qty` int(0) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of ta
-- ----------------------------
INSERT INTO `ta` VALUES (1, 'A', 50);
INSERT INTO `ta` VALUES (2, 'A', 20);
INSERT INTO `ta` VALUES (3, 'A', 30);
INSERT INTO `ta` VALUES (4, 'B', 100);
INSERT INTO `ta` VALUES (5, 'B', 20);
INSERT INTO `ta` VALUES (6, 'B', 30);
INSERT INTO `ta` VALUES (7, 'C', 50);
INSERT INTO `ta` VALUES (8, 'C', 60);
INSERT INTO `ta` VALUES (9, 'A', 50);
INSERT INTO `ta` VALUES (10, 'C', 60);
INSERT INTO `ta` VALUES (11, 'D', 20);
INSERT INTO `ta` VALUES (12, 'D', 50);
INSERT INTO `ta` VALUES (13, 'E', 20);
INSERT INTO `ta` VALUES (14, 'E', 10);
-- ----------------------------
-- Table structure for tg
-- ----------------------------
DROP TABLE IF EXISTS `tg`;
CREATE TABLE `tg` (
`gp` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`ass_qty` int(0) NULL DEFAULT NULL,
PRIMARY KEY (`gp`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of tg
-- ----------------------------
INSERT INTO `tg` VALUES ('A', 80);
INSERT INTO `tg` VALUES ('B', 130);
INSERT INTO `tg` VALUES ('C', 50);
INSERT INTO `tg` VALUES ('D', 70);
INSERT INTO `tg` VALUES ('E', 50);
insert into ta
select 1,'A',50 union all
select 2,'A',20 union all
select 3,'A',30 union all
select 4,'B',100 union all
select 5,'B',20 union all
select 6,'B',30 union all
select 7,'C',50 union all
select 8,'C',60 union all
select 10,'C',60 union all
select 9,'A',50 union all
select 11,'D',20 union all
select 12,'D',50 union all
select 13,'E',20 union all
select 14,'E',10
insert into tg
select 'A',80 union all
select 'B',130 union all
select 'C',50 union all
select 'D',70 union all
select 'E',50
-- Ass_Qty大于0的即为能分配到数量的记录，小于等于0的未分配到数量，
select a.id,a.gp,a.need_qty,b.ass_qty,b.ass_qty- ifnull((select sum(need_qty) from ta where a.gp = gp and id <= a.id group by gp),0) as tt,
case when b.ass_qty-ifnull((select sum(need_qty) from ta where a.gp = gp and id <= a.id group by gp),0)>= 0
then a.need_qty else
a.need_qty+(b.ass_qty- ifnull((select sum(need_qty) from ta where a.gp = gp and id <= a.id group by gp),0))
end
as Ass_Qty
from ta as a inner join tg as b on a.gp =b.gp
select a.id,a.gp,a.need_qty,b.ass_qty,b.ass_qty- ifnull((select sum(need_qty) from ta where a.gp = gp and id <= a.id group by gp),0) as tt,
case when b.ass_qty-ifnull((select sum(need_qty) from ta where a.gp = gp and id <= a.id group by gp),0)> 0
then a.need_qty else
if(b.ass_qty - ifnull((select sum(need_qty) from ta where a.gp = gp and id <a.id group by gp),0)>0,b.ass_qty - ifnull((select sum(need_qty) from ta where a.gp = gp and id <a.id group by gp),0),0)
end
as Ass_Qty
from ta as a inner join tg as b on a.gp =b.gp
select a.id,a.gp,a.need_qty,b.ass_qty,b.ass_qty-(select sum(need_qty) from ta where a.gp = gp and id <= a.id group by gp) as tt,
b.ass_qty-ifnull((select sum(need_qty) from ta where a.gp = gp and id < a.id group by gp),0) as tt1,
case when b.ass_qty- (select sum(need_qty) from ta where a.gp = gp and id <= a.id group by gp)> 0
then a.need_qty else
b.ass_qty - ifnull((select sum(need_qty) from ta where a.gp = gp and id < a.id group by gp),0)
end
as Ass_Qty
from ta as a inner join tg as b on a.gp =b.gp
