我正在用Node.js，Express，React和MySQL为Minecraft编写物料清单/配方应用程序。
我认为对于具有表的数据库来说，这是一个很好的结构，但是我遇到了我需要运行的查询的问题。这是我尝试使用示例输出进行操作的示例（我知道该配方不是正确的MC播放器）。
成分表：
RECIPE || INGREDIENT || QTY || TYPE
Piston || Redstone || 1 || RM
Piston || Iron Ingot || 1 || RM
Piston || Wood Planks || 3 || RM
Piston || Stone || 4 || RM
Sticky Piston || Piston || 2 || CO
Sticky Piston || Slimeball || 1 || RM
我想要的是，当我查询粘性活塞时，有一张表格返回所有必需成分加总（上），而所有必需原料加总（下）。
表格或JSON格式的示例必需输出：
INGREDIENT || QTY || TYPE
Piston || 2 || CO
Redstone || 2 || RM
Iron Ingot || 2 || RM
Wood Planks || 6 || RM
Stone || 8 || RM
Slimeball || 1 || RM
[
{
"Ingredient": "Piston",
"Qty": 2,
"Type": CO
},
{
"Ingredient": "Redstone",
"Qty": 2,
"Type": RM
},
{
"Ingredient": "Iron Ingot",
"Qty": 2,
"Type": RM
},
{
"Ingredient": "Wood Planks",
"Qty": 6,
"Type": RM
},
{
"Ingredient": "Stone",
"Qty": 8,
"Type": RM
},
{
"Ingredient": "Slimeball",
"Qty": 2,
"Type": RM
}
]
我认为这是一个JOIN，可以在MYSQL语法中完成所有其他花哨的工作，但是尽管经过两天尝试不同的组合，我还是无法弄清楚。
另外，如果可以使用我可以在Node.js服务器上运行的JS而不是使用MYSQL语法，也可以。
这是根据以下说明进行的模式修改后的版本。这就是我对关系的理解，但是当配方由需要其他材料的其他组件组成时，我仍然无法弄清楚如何返回所有必需的资源。
CREATE TABLE Ingredients
(
MaterialID INT unsigned NOT NULL AUTO_INCREMENT,
Material VARCHAR(250) NOT NULL,
MaterialImage VARCHAR(250),
PRIMARY KEY (MaterialID)
);
CREATE TABLE Recipes
(
RecipeID INT unsigned NOT NULL AUTO_INCREMENT,
Recipe VARCHAR(250) NOT NULL,
PRIMARY KEY (ComponentID)
);
CREATE TABLE Recipes_Ingredients
(
RecipeID INT unsigned NOT NULL,
MaterialID INT unsigned NOT NULL,
Quantity INT unsigned NOT NULL
)
谢谢！
最佳答案
更新/编辑：
更新以更正数量的计算
我已经更改了表格排列方式，以更好地匹配您要表示的数据。由于“配方”也可以是“物料”，因此我取消了配方表，并将该数据存储在物料表中。
我还创建了一个SQL Fiddle，以便您可以处理查询并微调结果。
下面的第一个查询返回所有“配方”及其组成部分。要仅获取一个特定配方，只需将条件添加到选择特定配方的where子句中即可。
如果您希望将组件列出在单个字段中，并且每个配方只包含一行，则可以使用GROUP_CONCAT函数并更改group by子句。
下面的第二个查询说明了GROUP_CONCAT函数。它还显示了如何将缺失的子成分从NULL更改为“无”
SQL Fiddle
MySQL 5.6模式设置：
CREATE TABLE `Ingredients`
(
`MaterialID` INT unsigned NOT NULL AUTO_INCREMENT,
`Material` VARCHAR(250) NOT NULL,
`MaterialImage` VARCHAR(250),
`IsRecipe` TINYINT(1) DEFAULT 0 NULL,
PRIMARY KEY (`MaterialID`)
);
CREATE TABLE `Recipes_Ingredients`
(
`id` INT unsigned NOT NULL AUTO_INCREMENT,
`RecipeID` INT unsigned NOT NULL,
`MaterialID` INT unsigned NOT NULL,
`Quantity` INT unsigned NOT NULL,
PRIMARY KEY (`id`)
);
INSERT INTO `Ingredients`
(`MaterialID`,`Material`,`MaterialImage`,`IsRecipe`)
VALUES
(1,'Redstone','redstone.jpg',0),
(2,'Iron Ingot','ironingot.jpg',0),
(3,'Wood Planks','woodplanks.jpg',0),
(4,'Stone','stone.jpg',0),
(5,'Slimeball','slimeball.jpg',0),
(6,'Piston','piston.jpg',1),
(7,'Sticky Piston','stickypiston.jpg',1),
(8,'Sticky Piston 2','stickypiston2.jpg',1);
INSERT INTO `Recipes_Ingredients`
(`RecipeID`,`MaterialID`,`Quantity`)
VALUES
(6,1,1),
(6,2,1),
(6,3,3),
(6,4,4),
(7,6,1),
(7,5,1),
(8,6,2),
(8,5,1);
查询1：
SELECT
a.`MaterialID`,
c.`MaterialID`,
a.`Material` as `Recipe`,
a.`MaterialImage` as `RecipeImage`,
c.`Material` as `Ingredient`,
b.`Quantity` as `FirstIngredientQuantity`,
c.`MaterialImage` as `IngredientImage`,
IF(d.`Quantity` IS NULL,SUM(b.`Quantity`),COALESCE(d.`Quantity`,0)*b.`Quantity`) as `Quantity`,
e.`Material` as `Ingredient`,
e.`MaterialImage` as `MaterialImage`
FROM `Ingredients` a
LEFT JOIN `Recipes_Ingredients` b
ON b.`RecipeID` = a.`MaterialID`
LEFT JOIN `Ingredients` c
ON c.`MaterialID` = b.`MaterialID`
LEFT JOIN `Recipes_Ingredients` d
ON d.`RecipeID` = c.`MaterialID`
LEFT JOIN `Ingredients` e
ON e.`MaterialID` = d.`MaterialID` AND c.`IsRecipe` = 1
WHERE a.`IsRecipe` = 1 AND a.`MaterialID` in (7,8)
GROUP BY a.`MaterialID`,c.`MaterialID`,e.`MaterialID`
Results：
| MaterialID | MaterialID | Recipe | RecipeImage | Ingredient | FirstIngredientQuantity | IngredientImage | Quantity | Ingredient | MaterialImage |
|------------|------------|-----------------|-------------------|------------|-------------------------|-----------------|----------|-------------|----------------|
| 7 | 5 | Sticky Piston | stickypiston.jpg | Slimeball | 1 | slimeball.jpg | 1 | (null) | (null) |
| 7 | 6 | Sticky Piston | stickypiston.jpg | Piston | 1 | piston.jpg | 1 | Redstone | redstone.jpg |
| 7 | 6 | Sticky Piston | stickypiston.jpg | Piston | 1 | piston.jpg | 1 | Iron Ingot | ironingot.jpg |
| 7 | 6 | Sticky Piston | stickypiston.jpg | Piston | 1 | piston.jpg | 3 | Wood Planks | woodplanks.jpg |
| 7 | 6 | Sticky Piston | stickypiston.jpg | Piston | 1 | piston.jpg | 4 | Stone | stone.jpg |
| 8 | 5 | Sticky Piston 2 | stickypiston2.jpg | Slimeball | 1 | slimeball.jpg | 1 | (null) | (null) |
| 8 | 6 | Sticky Piston 2 | stickypiston2.jpg | Piston | 2 | piston.jpg | 2 | Redstone | redstone.jpg |
| 8 | 6 | Sticky Piston 2 | stickypiston2.jpg | Piston | 2 | piston.jpg | 2 | Iron Ingot | ironingot.jpg |
| 8 | 6 | Sticky Piston 2 | stickypiston2.jpg | Piston | 2 | piston.jpg | 6 | Wood Planks | woodplanks.jpg |
| 8 | 6 | Sticky Piston 2 | stickypiston2.jpg | Piston | 2 | piston.jpg | 8 | Stone | stone.jpg |
查询2：
SELECT
a.`MaterialID`,
c.`MaterialID`,
a.`Material` as `Recipe`,
a.`MaterialImage` as `RecipeImage`,
c.`Material` as `Ingredient`,
c.`MaterialImage` as `MaterialImage`,
SUM(b.`Quantity` + COALESCE(d.`Quantity`,0)) as `Quantity`,
COALESCE(GROUP_CONCAT(CONCAT(e.`Material`,' (',b.`Quantity` * d.`Quantity`,') [',e.`MaterialImage`,']')),'None') as `Ingredients`
FROM `Ingredients` a
LEFT JOIN `Recipes_Ingredients` b
ON b.`RecipeID` = a.`MaterialID`
LEFT JOIN `Ingredients` c
ON c.`MaterialID` = b.`MaterialID`
LEFT JOIN `Recipes_Ingredients` d
ON d.`RecipeID` = c.`MaterialID`
LEFT JOIN `Ingredients` e
ON e.`MaterialID` = d.`MaterialID` AND c.`IsRecipe` = 1
WHERE a.`IsRecipe` = 1
GROUP BY a.`MaterialID`,c.`MaterialID`
Results：
| MaterialID | MaterialID | Recipe | RecipeImage | Ingredient | MaterialImage | Quantity | Ingredients |
|------------|------------|-----------------|-------------------|-------------|----------------|----------|-------------------------------------------------------------------------------------------------------------------|
| 6 | 1 | Piston | piston.jpg | Redstone | redstone.jpg | 1 | None |
| 6 | 2 | Piston | piston.jpg | Iron Ingot | ironingot.jpg | 1 | None |
| 6 | 3 | Piston | piston.jpg | Wood Planks | woodplanks.jpg | 3 | None |
| 6 | 4 | Piston | piston.jpg | Stone | stone.jpg | 4 | None |
| 7 | 5 | Sticky Piston | stickypiston.jpg | Slimeball | slimeball.jpg | 1 | None |
| 7 | 6 | Sticky Piston | stickypiston.jpg | Piston | piston.jpg | 13 | Redstone (1) [redstone.jpg],Iron Ingot (1) [ironingot.jpg],Wood Planks (3) [woodplanks.jpg],Stone (4) [stone.jpg] |
| 8 | 5 | Sticky Piston 2 | stickypiston2.jpg | Slimeball | slimeball.jpg | 1 | None |
| 8 | 6 | Sticky Piston 2 | stickypiston2.jpg | Piston | piston.jpg | 17 | Redstone (2) [redstone.jpg],Iron Ingot (2) [ironingot.jpg],Wood Planks (6) [woodplanks.jpg],Stone (8) [stone.jpg] |