如何编写SQL查询计算制作指定装备所需的材料及数量
2026-6-28
需求与SQL实现
现有三张数据表，结构及数据如下：
materials表（记录所有可用材料信息）
| material | max |
|---|---|
| animalfat | 30 |
| arrow1_broadhead | 40 |
| arrow2_smallgame | 40 |
| arrow3_poison | 8 |
| arrow4_fire | 8 |
| arrow5_dynamite | 8 |
| arrow6_tracking | 8 |
| cartridge_pistol1_fmj | 400 |
| cartridge_reapeter1_fmj | 200 |
| cartridge_revolver1_fmj | 400 |
| cartridge_revolver5_explosive | 10 |
| cartridge_rifle1_fmj | 200 |
| cartridge_rifle5_explosive | 10 |
| moonshine | 40 |
| shotshell1_pellets | 120 |
requirements表（记录可制作的产品、所需材料及对应数量）
| requirementID | product | materialx | material |
|---|---|---|---|
| 1 | cartridge_revolver5_explosive | 4 | animalfat |
| 2 | cartridge_revolver5_explosive | 1 | cartridge_revolver1_fmj |
| 3 | cartridge_rifle5_explosive | 2 | animalfat |
| 4 | cartridge_rifle5_explosive | 2 | cartridge_rifle1_fmj |
equipment表（记录需要制作的产品及对应数量）
| equipment | equipmentx |
|---|---|
| cartridge_revolver5_explosive | 8 |
| cartridge_rifle5_explosive | 4 |
请编写SQL查询，列出制作equipment表中指定数量的产品所需的所有材料及其总需求量，预期输出如下：
预期输出
| material | materialx |
|---|---|
| animalfat | 40 |
| cartridge_revolver1_fmj | 8 |
| cartridge_rifle1_fmj | 8 |
实现SQL
SELECT r.material, SUM(r.materialx * e.equipmentx) AS materialx FROM equipment e INNER JOIN requirements r ON e.equipment = r.product GROUP BY r.material ORDER BY r.material;
内容的提问来源于stack exchange，提问作者ACIXCIX0001
相关产品推荐
相关产品推荐
