如何在SQL中关联monsters与items表生成怪物掉落物品关联信息？

我有monsters、items和joinedInfo三张表，想要将monsters与items表关联，让joinedInfo表展示每个怪物可掉落的物品。

示例表结构 #

Monsters表 #

Items表 #

预期JoinedInfo表 #

掉落规则 #

• 牛、鸡和哥布林均可掉落骨头
• 鸡和哥布林可掉落金币
• 只有牛可掉落牛皮
• 只有哥布林可掉落匕首
• 只有鸡可掉落鸡蛋

我尝试修改网上的示例SQL来关联表，但执行结果不符合预期：

SELECT items.ID, items.ItemName, monsters.MonsterName, monsters.ID FROM items, monsters;
LEFT OUTER JOIN joinedInfo ON MonsterName.ID = joinedInfo.ID AND MonsterName.ID = items.MonsterForeignKey
LEFT OUTER JOIN groups ON group_elements.GroupID = groups.ID

以下是SQLFiddle中的Schema和执行结果：

MySQL 5.6 Schema设置 #

CREATE TABLE monsters
    (`ID` int, `MonsterName` varchar(43))
;
    
INSERT INTO monsters
    (`ID`, `MonsterName`)
VALUES
    (1, 'Cow'),
    (2, 'Chicken'),
    (3, 'Goblin')
;

CREATE TABLE items
    (`ID` int, `ItemName` varchar(9), `MonsterForeignKey` int)
;
    
INSERT INTO items
    (`ID`, `ItemName`, `MonsterForeignKey`)
VALUES
    (1, 'Gold', 3), 
    (2, 'Bones', 1),
    (3, 'Feathers', 2),
    (4, 'Cow Hide', 1),
    (5, 'Dagger', 3),
    (6, 'Eggs', 2)
;

CREATE TABLE group_elements
    (`GroupID` int, `ElementID` int)
;
    
INSERT INTO group_elements
    (`GroupID`, `ElementID`)
VALUES
    (3, 1),
    (1, 2),
    (2, 2),
    (2, 3),
    (3, 3)
;

查询1 #

SELECT items.ID, items.ItemName, monsters.MonsterName FROM items, monsters

结果:

| ID | ItemName | MonsterName |
|----|----------|-------------|
|  1 |     Gold |         Cow |
|  1 |     Gold |     Chicken |
|  1 |     Gold |      Goblin |
|  2 |    Bones |         Cow |
|  2 |    Bones |     Chicken |
|  2 |    Bones |      Goblin |
|  3 | Feathers |         Cow |
|  3 | Feathers |     Chicken |
|  3 | Feathers |      Goblin |
|  4 | Cow Hide |         Cow |
|  4 | Cow Hide |     Chicken |
|  4 | Cow Hide |      Goblin |
|  5 |   Dagger |         Cow |
|  5 |   Dagger |     Chicken |
|  5 |   Dagger |      Goblin |
|  6 |     Eggs |         Cow |
|  6 |     Eggs |     Chicken |
|  6 |     Eggs |      Goblin |

查询2 #

LEFT OUTER JOIN joinedInfo ON monsterName.ID = joinedInfo.ID AND monsterName.ID = items.MonsterForeignKey
LEFT OUTER JOIN groups ON group_elements.GroupID = groups.ID

结果: 无有效结果

解决方案 #

核心问题说明 #

当前items表中MonsterForeignKey用逗号分隔多个怪物ID的设计违反数据库范式，会导致关联查询困难、性能低下，且不利于后续扩展。推荐使用关联表建立怪物与物品的多对多关系。

方案1：修改表结构（推荐，符合规范） #

1. 移除items表中的MonsterForeignKey字段：

ALTER TABLE items DROP COLUMN MonsterForeignKey;

2. 创建关联表monster_item，存储怪物与物品的对应关系：

CREATE TABLE monster_item (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    MonsterID INT,
    ItemID INT,
    FOREIGN KEY (MonsterID) REFERENCES monsters(ID),
    FOREIGN KEY (ItemID) REFERENCES items(ID)
);

3. 插入关联数据（匹配掉落规则）：

-- 牛的掉落物品
INSERT INTO monster_item (MonsterID, ItemID) VALUES (1, 2), (1, 4);
-- 鸡的掉落物品
INSERT INTO monster_item (MonsterID, ItemID) VALUES (2, 2), (2, 3), (2, 6);
-- 哥布林的掉落物品
INSERT INTO monster_item (MonsterID, ItemID) VALUES (3, 2), (3, 1), (3, 5);

4. 查询生成预期结果（或直接插入到joinedInfo表）：

-- 查询结果
SELECT 
    ROW_NUMBER() OVER (ORDER BY m.MonsterName DESC, i.ItemName) AS ID,
    m.MonsterName AS 怪物名称,
    i.ItemName AS 物品名称
FROM monsters m
JOIN monster_item mi ON m.ID = mi.MonsterID
JOIN items i ON mi.ItemID = i.ItemID
ORDER BY m.MonsterName DESC, i.ItemName;

-- 插入到joinedInfo表
INSERT INTO joinedInfo (怪物名称, 物品名称)
SELECT 
    m.MonsterName,
    i.ItemName
FROM monsters m
JOIN monster_item mi ON m.ID = mi.MonsterID
JOIN items i ON mi.ItemID = i.ItemID
ORDER BY m.MonsterName DESC, i.ItemName;

方案2：基于现有结构的临时查询（不推荐） #

如果暂时无法修改表结构，可使用MySQL的FIND_IN_SET函数匹配逗号分隔的ID：

SELECT 
    ROW_NUMBER() OVER (ORDER BY m.MonsterName DESC, i.ItemName) AS ID,
    m.MonsterName AS 怪物名称,
    i.ItemName AS 物品名称
FROM monsters m
JOIN items i ON FIND_IN_SET(m.ID, i.MonsterForeignKey) > 0
ORDER BY m.MonsterName DESC, i.ItemName;

注意：此方法性能差，数据量大时会明显变慢，且后续维护难度高，仅作为临时解决方案。

内容的提问来源于stack exchange，提问作者BadAtProgrammingLOL