嗨,我不知道如何把这个放在一个简短的句子,但我有DB表像下面
用户表- user_id
- 用户名
- item_id
- item_name
Item_Equipped
- equipped_id head (FK to item_id)
- hand (FK to item_id)
- user_id (FK to user_id IN User Table)
我想生成一个查询,它将显示如下格式
user_id | head | head_item_name | hand | hand_item_name |…
到目前为止,我只能这样做:
SELECT user.user_id, user.username,
equipments.head, equipments.head_acc,
equipments.hand,
equipments.acc, equipments.body
FROM gw_member_equipped AS equipments
LEFT JOIN gw_member AS user ON user.memberid = equipments.member_id
这(我必须残酷地诚实)没有做什么。
我试图在item和item_equip之间执行INNER JOIN,但是我无法获得每个项目的单独名称(基于其项目ID)
您需要将ITEM表与item_equip表连接两次。
您可以使用下面的查询查询问题中显示的所需输出列。
SELECT USER.User_Id,
Item_Equipped.Head,
Item_Heads.Item_Id Head_Item_Id, -- if you want you can remove this column
Item_Heads.Item_Name Head_Item_Name,
Item_Equipped.Hand,
Item_Hands.Item_Id Hand_Item_Id, -- and this column also as these columns are same as their previous select columns
Item_Hands.Item_Name Hand_Item_Name
FROM USER, Item Item_Heads, Item Item_Hands, Item_Equipped
WHERE USER.User_Id = Item_Equipped.User_Id
AND Item_Heads.Item_Id = Item_Equipped.Head
AND Item_Hands.Item_Id = Item_Equipped.Hand