组合不同的sql请求，但它们的位置都相同

我有很多要求，但总是一样的 WHERE ，我通过从一个表中键入一个字符的名字来请求复制一个字符，所以它会从其他表中复制数据（有时需要删除和复制，有时只需要更新），但是如果我要复制某人的字符，我总共要键入7次名称，我希望我能键入一次，然后它就完全改变了
以下是不同的要求：
请求删除“samymewtu”中的所有行，并通过复制其他人插入所有行，这里是“myths”

DELETE i 
FROM items i
JOIN characters c ON c.characterId = i.characterId
WHERE c.name = 'samymewtu' --Delete the inventory where you are going to paste

INSERT INTO ITEMS (CHARACTERID, ITEMID, AMOUNT, SORT, EQUIPSLOT, MAXUPGRADE, CURRENTUPGRADE, STATMOD, ISTRADABLE, UNIQUEID, ATTACHED)
    SELECT  
        C2.CHARACTERID, I.ITEMID, I.AMOUNT, I.SORT, I.EQUIPSLOT,
        I.MAXUPGRADE, I.CURRENTUPGRADE, I.STATMOD, I.ISTRADABLE, I.UNIQUEID, I.ATTACHED
    FROM 
        ITEMS I 
    INNER JOIN 
        CHARACTERS C ON I.CHARACTERID = C.CHARACTERID 
                     AND C.NAME = 'Myths' --The character you want to copy his inventory
    INNER JOIN 
        CHARACTERS C2 ON C2.NAME = 'samymewtu' --The character copying the inventory of the character above

这是同样的，不过是给银行的

DELETE i 
FROM bankItems i
JOIN characters c ON c.accountId = i.accountId
WHERE c.name = 'samymewtu' --Delete the inventory where you are going to paste

INSERT INTO bankItems (accountId, ITEMID, AMOUNT, SORT, MAXUPGRADE, CURRENTUPGRADE, STATMOD, ISTRADABLE, UNIQUEID, ATTACHED)
    SELECT 
        C2.accountId, I.ITEMID, I.AMOUNT, I.SORT, I.MAXUPGRADE,
        I.CURRENTUPGRADE, I.STATMOD, I.ISTRADABLE, I.UNIQUEID, I.ATTACHED
    FROM 
        bankItems I 
    INNER JOIN 
        CHARACTERS C ON I.accountId = C.accountId 
                     AND C.NAME = 'Myths' --The character you want to copy his  bank
    INNER JOIN 
        CHARACTERS C2 ON C2.NAME = 'samymewtu' --The character copying the bank of the character above

这里是复制stats，但是它更新了行，因为只有一个，我保留characterid

UPDATE s1 SET 
s1.exp=s2.exp,
s1.maxExp=s2.maxExp,
s1.totalExp=s2.totalExp,
s1.hp=s2.hp,
s1.maxHP=s2.maxHP,
s1.mp=s2.mp,
s1.maxMP=s2.maxMP,
s1.str=s2.str,
s1.def=s2.def,
s1.mag=s2.mag,
s1.dex=s2.dex,
s1.statByBonus=s2.statByBonus,
s1.usedStat=s2.usedStat,
s1.vitalityByLevel=s2.vitalityByLevel,
s1.vitalityByBonus=s2.vitalityByBonus,
s1.usedVitality=s2.usedVitality,
s1.spellByLevel=s2.spellByLevel,
s1.spellByBonus=s2.spellByBonus,
s1.usedSpell=s2.usedSpell,
s1.spellCapacity=s2.spellCapacity
FROM stats s1
CROSS JOIN stats s2
INNER JOIN characters c1 ON c1.characterId=s1.characterId
INNER JOIN characters c2 ON c2.characterId=s2.characterId
WHERE c1.name='samymewtu' --Character who must copy stats
AND c2.name='myths' -- The character from who you copy stats

还有一些，但我想你明白了，每次我需要进入c1.name='samymewtu'和c2.name='myths'

在sql server中，可以使用变量使脚本\查询成为动态的。

DECLARE @C1 VARCHAR(100)
DECLARE @C2 VARCHAR(100)

SET @C1 = 'Myths'
SET @C2 = 'samymewtu'

DELETE i FROM items i
JOIN characters c ON c.characterId=i.characterId
WHERE c.name = @C2 --Delete the inventory where you are going to paste

INSERT INTO ITEMS (CHARACTERID,ITEMID,AMOUNT,SORT,EQUIPSLOT,MAXUPGRADE,CURRENTUPGRADE,STATMOD,ISTRADABLE, UNIQUEID, ATTACHED)
SELECT C2.CHARACTERID,I.ITEMID,I.AMOUNT,I.SORT,I.EQUIPSLOT,I.MAXUPGRADE,I.CURRENTUPGRADE,I.STATMOD,I.ISTRADABLE,I.UNIQUEID,I.ATTACHED
FROM ITEMS I INNER JOIN CHARACTERS C 
ON I.CHARACTERID = C.CHARACTERID AND C.NAME = @C1 --The character you want to copy his inventory
INNER JOIN CHARACTERS C2 ON C2.NAME = @C2 --The character copying the inventory of the character above

组合不同的sql请求，但它们的位置都相同

1条答案

相关问题

热门标签

最新问答