结构
我有3个表:
第一个是[设备]表:
+----------+----------+-----------+-------------+----------+---------+
| equip_id | chara_id | weapon_id | headgear_id | armor_id | ring_id |
+----------+----------+-----------+-------------+----------+---------+
| 3 | 1 | 3 | 3 | 5 | 6 |
| 4 | 2 | 1 | 2 | 3 | 4 |
+----------+----------+-----------+-------------+----------+---------+
第二个是[item]表:
+---------+--------------+-----------+----------+----------+----------+-------------------------------+-----------+------------+
| item_id | item_name | item_type | item_atk | item_def | item_atr | item_img | item_desc | item_price |
+---------+--------------+-----------+----------+----------+----------+-------------------------------+-----------+------------+
| 0 | Halberd | 1 | 220 | 20 | 0 | pics/weapons/halberd.png | | 400 |
| 1 | Axe | 1 | 220 | -10 | 0 | pics/weapons/axe.png | | 200 |
| 2 | Wooden Sword | 1 | 70 | 0 | 0 | pics/weapons/wooden-sword.png | | 225 |
| 3 | Dagger | 1 | 60 | 5 | 0 | pics/weapons/dagger.png | | 55 |
| 4 | Bow | 1 | 120 | 1 | 0 | pics/weapons/bow.png | | 0 |
| 5 | Helmet | 4 | 0 | 20 | 0 | pics/headgear/helmet.png | | 155 |
| 6 | Tunic | 2 | 0 | 10 | 0 | pics/armors/tunic.png | | 50 |
| 7 | Armour | 2 | 0 | 45 | 0 | pics/armors/armour.png | | 0 |
| 8 | Necklace | 3 | 15 | 5 | 0 | pics/accessories/necklace.png | | 199 |
+---------+--------------+-----------+----------+----------+----------+-------------------------------+-----------+------------+
最后一个是[chara]表:
+----------+------------+----------------+------------+
| chara_id | chara_name | chara_class_id | chara_gold |
+----------+------------+----------------+------------+
| 1 | Lawrence | 1 | 0 |
| 2 | Testo | 0 | 0 |
| 3 | Viscocent | 2 | 0 |
| 4 | Piatos | 1 | 0 |
| 5 | Hello | 4 | 0 |
+----------+------------+----------------+------------+
我的障碍:
我想从选定的字符中显示每个weapon_id, armor_id, headgear_id和ring_id(全部来自设备表)的等效item_name(来自项目表)。例句:
weapon_id: 1 => item_name from items table
Armor_id: 3 => item_name from items table等
预期结果:当我查询一个角色的装备时，它返回的是武器名称而不是武器id。
---------------
chara_id: 1
武器:匕首
Headgear: Dagger(字符数据是样本并不重要，只要它返回item_name)
甲:头盔
环:束腰外衣
这可以简单地解决4个查询，但我不想使用4个查询，如果有一个更好的解决方案。
Try
SELECT e.chara_id,
e.weapon_id, i1.item_name weapon_name,
e.headgear_id, i2.item_name headgear_name,
e.armor_id, i3.item_name armor_name,
e.ring_id, i4.item_name ring_name
FROM equipment e LEFT JOIN
item i1 ON e.weapon_id = i1.item_id LEFT JOIN
item i2 ON e.headgear_id = i2.item_id LEFT JOIN
item i3 ON e.armor_id = i3.item_id LEFT JOIN
item i4 ON e.ring_id = i4.item_id
WHERE e.chara_id = 1
| CHARA_ID | WEAPON_ID | WEAPON_NAME | HEADGEAR_ID | HEADGEAR_NAME | ARMOR_ID | ARMOR_NAME | RING_ID | RING_NAME |
------------------------------------------------------------------------------------------------------------------
| 1 | 3 | Dagger | 3 | Dagger | 5 | Helmet | 6 | Tunic |
SQLFiddle
如果你不需要id在你的结果集中，只要从query
可以使用join
SELECT c.chara_name as name, i.item_name
FROM chara c
LEFT JOIN equipment AS e ON c.chara_id = e.chara_id
LEFT JOIN item AS i ON i.id = e.item_id
WHERE c.chara_id = 1
你可能会得到多行处理…但它将是一个查询
我想你在找这个:
SELECT e.equip_id, w.item_name AS weapon, a.item_name AS armor,
h.item_name AS headgear, r.item_name AS ring
FROM equipment e
INNER JOIN item AS w ON (w.item_id = e.weapon_id)
INNER JOIN item AS a ON (w.item_id = e.armor_id)
INNER JOIN item AS h ON (w.item_id = e.headgear_id)
INNER JOIN item AS r ON (w.item_id = e.ring_id)
; -)