MySQL根据库存原材料的原材料单位计算体积

我正在试图计算找到所需的库存，并显示产量，如果它不能达到最大产量，在这种情况下，体积‘1000’。

我会尽力把它解释得尽可能清楚。(表格和栏目均为荷兰语)

示例：

为了生产1000件产品，我需要多种原材料。原料有不同的总数要放进‘烹饪锅’(为了达到1000，需要加水，但这只是个附注)。

1000是计算表中数字的基数Receitgrondstoffen

首先，我有一张table，上面有一个叫‘Naam’(名字)的食谱

表格：接收

CREATE TABLE `recepten` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `administratieid` int(11) NOT NULL DEFAULT 0,
  `omzetgroepid` int(11) NOT NULL DEFAULT 0,
  `artikelgroepid` int(11) NOT NULL DEFAULT 0,
  `artikelnummer` int(11) NOT NULL DEFAULT 0 COMMENT 'gevuld vanuit snelstart',
  `factornummer` varchar(20) NOT NULL,
  `eannummer` varchar(20) NOT NULL,
  `naam` varchar(255) NOT NULL,
  `notitie` mediumtext NOT NULL,
  `volume` decimal(10,5) NOT NULL DEFAULT 0.00000,
  `onderzoek` int(1) NOT NULL DEFAULT 0,
  `viscositeit` varchar(50) NOT NULL,
  `phwaarde` varchar(50) NOT NULL,
  `dichtheid` varchar(50) NOT NULL,
  `thtmaanden` int(11) NOT NULL DEFAULT 0,
  `voorraadcontrole` int(1) NOT NULL DEFAULT 0,
  `drempelwaarde` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;

INSERT INTO `recepten` (`id`,`administratieid`,`omzetgroepid`,`artikelgroepid`,`artikelnummer`,`factornummer`,`eannummer`,`naam`,`notitie`,`volume`,`onderzoek`,`viscositeit`,`phwaarde`,`dichtheid`,`thtmaanden`,`voorraadcontrole`,`drempelwaarde`) VALUES (1,0,0,702,300001,'122','','test','test',1000.00000,1,'1','2','3',36,0,1);
INSERT INTO `recepten` (`id`,`administratieid`,`omzetgroepid`,`artikelgroepid`,`artikelnummer`,`factornummer`,`eannummer`,`naam`,`notitie`,`volume`,`onderzoek`,`viscositeit`,`phwaarde`,`dichtheid`,`thtmaanden`,`voorraadcontrole`,`drempelwaarde`) VALUES (2,0,0,704,300002,'1234','','test1','test',1000.00000,1,'1','2','3',36,0,100);

第二张table是放入“烹饪锅”中的食谱。有两条原料线。两者都有100个数字，因此每1000个卷都需要100个单位。如果我把音量改为100，那么两个都需要10个单位。

表格：接收grondstoffen

CREATE TABLE `receptgrondstoffen` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `receptid` int(11) NOT NULL DEFAULT 0,
  `grondstofid` int(11) NOT NULL DEFAULT 0,
  `aantal` decimal(10,5) NOT NULL DEFAULT 0.00000,
  `percentage` decimal(10,5) NOT NULL DEFAULT 0.00000,
  UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

INSERT INTO `receptgrondstoffen` (`id`,`receptid`,`grondstofid`,`aantal`,`percentage`) VALUES (2,1,1,100.00000,10.00000);
INSERT INTO `receptgrondstoffen` (`id`,`receptid`,`grondstofid`,`aantal`,`percentage`) VALUES (3,1,2,100.00000,10.00000);

Grondstof Batch表是已购买的原材料数量

表：grondstof Batch

CREATE TABLE `grondstofbatch` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `grondstofid` int(11) NOT NULL DEFAULT 0,
  `leveranciersid` int(11) NOT NULL DEFAULT 0,
  `batchnummer` varchar(50) NOT NULL,
  `datum` int(10) NOT NULL DEFAULT 0,
  `thtdatum` int(10) NOT NULL DEFAULT 0,
  `voorraad` int(11) NOT NULL DEFAULT 0,
  UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

INSERT INTO `grondstofbatch` (`id`,`grondstofid`,`leveranciersid`,`batchnummer`,`datum`,`thtdatum`,`voorraad`) VALUES (1,1,4,'1224-4',1662626077,1665266400,100);
INSERT INTO `grondstofbatch` (`id`,`grondstofid`,`leveranciersid`,`batchnummer`,`datum`,`thtdatum`,`voorraad`) VALUES (2,1,3,'#34423',1662626904,1663970400,300);
INSERT INTO `grondstofbatch` (`id`,`grondstofid`,`leveranciersid`,`batchnummer`,`datum`,`thtdatum`,`voorraad`) VALUES (3,2,3,'#00931',1662626904,1663970400,200);

音量才是关键。

我想要的是，如果我使用体积，假设在这种情况下是3000，我得到的结果是，最大体积是X，因为并不是所有的原材料都存在。

基数是1000=100，因此3000的基数是300，并且只有1种原材料有所需的库存。所以这意味着它将是最大2000卷。如果没有可以生产的量，则为0。

结果：

CREATE TABLE `results` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `volume` int(11) NOT NULL,
  `quantity_needed` mediumtext NOT NULL,
  `stock` mediumtext NOT NULL,
  `result` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

INSERT INTO `results` (`id`,`volume`,`quantity_needed`,`stock`,`result`) VALUES (1,1000,'100,100','400,200',1000);
INSERT INTO `results` (`id`,`volume`,`quantity_needed`,`stock`,`result`) VALUES (2,400,'100,100','400,200',2000);
INSERT INTO `results` (`id`,`volume`,`quantity_needed`,`stock`,`result`) VALUES (3,3000,'100,100','400,200',2000);
INSERT INTO `results` (`id`,`volume`,`quantity_needed`,`stock`,`result`) VALUES (4,500,'100,100','400,200',500);
INSERT INTO `results` (`id`,`volume`,`quantity_needed`,`stock`,`result`) VALUES (5,500,'50,75','400,200',500);
INSERT INTO `results` (`id`,`volume`,`quantity_needed`,`stock`,`result`) VALUES (6,500,'50,75','25,75',250);
INSERT INTO `results` (`id`,`volume`,`quantity_needed`,`stock`,`result`) VALUES (7,500,'30,30','25,75',416);

希望有一个SQL向导可以帮我解决问题。

我想你要找的东西是

SELECT r.id, 
 r.volume,
 group_concat(rg.aantal ORDER BY rg.id SEPARATOR ',') quantity_needed,
 group_concat(g.voorraad ORDER BY rg.id SEPARATOR ',') stock, 
 min(floor(g.voorraad/rg.aantal)) * r.volume result
FROM recepten r INNER JOIN receptgrondstoffen rg
  on r.id = rg.receptid
 inner join grondstofbatch g
  on g.id = rg.grondstofid
GROUP BY r.id, r.volume

您可以使用示例数据in this fiddle查看它。

(我可能误解了您的问题，因为您的结果数据似乎与您的样本数据不匹配)。

这里的想法是，对于每个容器，计算min(floor(grondstofbatch.voorraad/receptgrondstoffen.aantal))。Floor因为我们需要一个整数(例如，如果配方需要100个单位，而实际上有250个单位，则Floor(250/100)=2个可能的批次)。Min，因为我们想要限制因素(例如，食谱中的一项产品是否足够20批，如果另一项仅够一批，则无关紧要)。

希望这与您正在寻找的东西大致相同。

**已编辑：**为了处理对可用数量求和的情况，我使用grondstofid表(而不仅仅是直接的grondstofid表)将别名g更改为子查询。我还意识到我可能错误地加入了上面的grondstofid表(g.id = rg.grondstofid而不是g.grondstofid = rg.grondstofid)。

SELECT r.id, 
 r.volume,
 group_concat(rg.aantal ORDER BY rg.id SEPARATOR ',') quantity_needed,
 group_concat(g.voorraad ORDER BY rg.id SEPARATOR ',') stock, 
  min(floor(g.voorraad/rg.aantal)) * r.volume result
FROM recepten r INNER JOIN receptgrondstoffen rg
  on r.id = rg.receptid
 inner join (SELECT grondstofid, sum(voorraad) voorraad FROM grondstofbatch GROUP BY grondstofid) g
  on g.grondstofid = rg.grondstofid
GROUP BY r.id, r.volume

请看一下fiddle of this version

MySQL根据库存原材料的原材料单位计算体积

1条答案

相关问题

热门标签

最新问答