我想寻求与UPDATE命令的帮助,这取决于4个表的关系。 (或者甚至有可能吗?

这是我的桌子:

Characters
guid, ...., totalItemlevel

template_items
id, ... itemlevel, ...,

instances_item
itemInstanceID, itemID, Owner
(itemID is foreign key template_items.id, Owner is foreign key Character.guid)

inventory_character
guid, slot, iteminstance
(guid is foreign key character.guid, item is foreign key instances_item.itemInstanceID)


现在,我需要执行一个UPDATE脚本,如果角色在插槽> 20的库存中具有该项目的实例,它将设置totalItemLevel为template_items.itemLevel的总和

在伪陈述中:

UPDATE characters
SET    totalItemLevel = SUM(FOR EACH ITEM IN INVENTORY(is slot>20?(GET its template(get itemLevel))));


所以我尝试了这样的事情

SELECT  SUM(template_items.itemLevel)
FROM    template_items,
        inventory_character,
        instances_item,
        characters
WHERE   chararacter.guid = inventory_character.guid
AND     inventory_character.slot > 19
AND     inventory_character.iteminstance = instances_item.itemInstanceID
AND     instances_item.itemID = template_items.id);


这绝对是行不通的,太疯狂了,可能需要进行一些子查询,但是我真的不知道如何实现这一目标。

另外,也没有其他方法可以像在PHP中进行一些查询然后将其计数一样...我需要直接在数据库中进行此更新。

那么,请问有什么想法,如何实现此计算列?

提前致谢 :)

最佳答案

假设您需要> 20的插槽(在您的帖子中,一个位置> 20的插槽在另一位置> 19的插槽):

UPDATE Characters
SET Characters.totalItemLevel = s FROM
(
    SELECT SUM(template_items.itemlevel) AS s, c.[guid]
    FROM Characters c
    INNER JOIN inventory_character ON inventory_character.[guid] = c.[guid]
    INNER JOIN instances_item ON instances_item.itemInstanceID = inventory_character.iteminstance AND instances_item.[Owner] = c.[guid]
    INNER JOIN template_items ON template_items.id = instances_item.itemID
    WHERE c.[guid] = instances_item.[Owner] AND slot > 20
    GROUP BY c.[guid]
) A
WHERE Characters.[guid] = A.[guid]

关于mysql - MySQL SUM更新超过4个表,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/43784756/

10-11 02:49