|
/*
?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);
?? ??? ??? ?
?? ??? ??? ?-- 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?? ?
?
|