我想寻求与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/