mysql - MySQL查询可通过第二个表中链接的数据在列中设置唯一编号

我将尽力解释这一点。

概述：

这是用于游戏服务器。在游戏中，角色拥有普通物品，然后还有带有属性的装备。我想为某些特殊设备赋予唯一性。即使玩家拥有两个相同的设备，每个特殊设备的计数也应具有不同的唯一性。有两个与项目相关的表，库存项目，然后是库存设备。普通项目将简单地显示在库存项目表中，因为它们没有统计信息，但是设备显示在库存项目中以及存储该项目统计信息的库存设备中。这两个表由称为清单项目中间的列链接。

我要做什么：

我想查询一个查询，该查询将在清单项目的uniqueid列中为具有某些统计信息的任何设备设置唯一编号。

这是一个残破的示例查询，显然不会起作用，但希望它将帮助您进一步了解我要完成的工作...

 UPDATE inventoryitems
 SET    inventoryitems.uniqueid = (
                 SELECT MAX(uniqueid)) + 1
                 WHERE EXISTS
                 (
                     SELECT inventoryequipment.inventoryitemid
                     FROM   inventoryequipment
                     WHERE  inventoryequipment.upgradeslots = 0
                        &&  inventoryequipment.level = 0
                        &&  inventoryequipment.str = 0
                        &&  inventoryequipment.dex = 0
                        &&  inventoryequipment.int = 0
                        &&  inventoryequipment.luk = 0
                        &&  inventoryequipment.hp = 0
                        &&  inventoryequipment.mp = 0
                        &&  inventoryequipment.watk = 0
                        &&  inventoryequipment.wdef = 0
                        &&  inventoryequipment.mdef = 0
                        &&  inventoryequipment.acc = 0
                        &&  inventoryequipment.hands = 0
                        &&  inventoryequipment.speed = 0
                        &&  inventoryequipment.jump = 0
                        &&  inventoryequipment.ringid = -1
                        &&  inventoryequipment.locked = 0
                        &&  inventoryequipment.isRing = 0
                   );

最佳答案

弄清楚了...

set @i=0;
UPDATE inventoryitems SET uniqueid = (@i:=@i+1) WHERE inventoryitemid IN (SELECT inventoryequipment.inventoryitemid
                 FROM   inventoryequipment
                 WHERE  inventoryequipment.upgradeslots = 0
                    &&  inventoryequipment.level = 0
                    &&  inventoryequipment.str = 0
                    &&  inventoryequipment.dex = 0
                    &&  inventoryequipment.int = 0
                    &&  inventoryequipment.luk = 0
                    &&  inventoryequipment.hp = 0
                    &&  inventoryequipment.mp = 0
                    &&  inventoryequipment.watk = 0
                    &&  inventoryequipment.wdef = 0
                    &&  inventoryequipment.mdef = 0
                    &&  inventoryequipment.acc = 0
                    &&  inventoryequipment.hands = 0
                    &&  inventoryequipment.speed = 0
                    &&  inventoryequipment.jump = 0
                    &&  inventoryequipment.ringid = -1
                    &&  inventoryequipment.locked = 0
                    &&  inventoryequipment.isRing = 0);