自從我搞亂 SQL 以來已經很長時間了，當我建立自己的視訊遊戲時，我不得不重新開始使用它。我正在使用 MySQL，並且在這裡遇到了一些複雜的問題。
這是我的兩個陣列；
SET @myArrayofRaces = '"Dwarf", "Elf", "Halfling", "Human", "Dragonborn", "Gnome", "Half-Elf", "Half-Orc", "Tiefling"'; SET @myArrayofClasses = '"Barbarian", "Bard", "Cleric", "Druid", "Fighter", "Monk", "Paladin", "Ranger", "Rogue", "Sorcerer", "Warlock", "Wizard"';
正如我們所看到的，我有 9 場比賽和 12 個班級。我想要使用這些陣列來編寫 INSERT INTO 語句的程式碼，這樣我就不必手動輸入 108 行。
這是我正在執行的 INSERT INTO 語句；
INSERT INTO world_content.character_create_template (
race,
aspect,
instance,
pos_x,
pos_y,
pos_z,
orientation,
faction,
autoAttack,
race_icon,
class_icon,
race_description,
class_description,
isactive,
respawnInstance,
respawnPosX,
respawnPosY,
respawnPosZ,
startingLevel,
sprint
)
VALUES
(
437,
428,
29,
- 170,
74,
154,
0,
1,
- 1,
"Assets/Resources/Assets/Icons/Race Icons/Dwarf.png",
"Assets/Resources/Assets/Icons/Class Icons/Druid.png",
"Dwarf",
"Druid",
1,
29,
- 170,
74,
154,
1,
- 1
)
我需要循環這個 INSERT INTO 語句，直到我完成了所有 108 種種族和類別的組合。因此，矮人將作為每個類別出現在資料庫中。然後 Elf 將被插入到資料庫中的每個類別。然後是半身人，然後是人類，等等。
類別數組只是進入 class_description 和 class_icon 中，您可以看到我正在刪除圖像的連結。比賽也將遵循race_icon影像。
有誰知道如何在每次比賽中循環 ArrayofRaces 12 次，以便我可以輕鬆匯入類別和比賽？
提前致謝！
您可以使用交叉連接產生所有組合。例如：
INSERT INTO character_create_template ( race, aspect, instance, pos_x, pos_y, pos_z, orientation, faction, autoAttack, race_icon, class_icon, race_description, class_description, isactive, respawnInstance, respawnPosX, respawnPosY, respawnPosZ, startingLevel, sprint ) with race as ( select 'Dwarf' as name union all select 'Elf' -- repeat this line for more races ), class as ( select 'Barbarian' as name union all select 'Bard' -- repeat this line for more classes ) select 437, 428, 29, - 170, 74, 154, 0, 1, - 1, "Assets/Resources/Assets/Icons/Race Icons/Dwarf.png", "Assets/Resources/Assets/Icons/Class Icons/Druid.png", r.name, c.name, 1, 29, - 170, 74, 154, 1, - 1 from race r cross join class c請參閱 DB Fiddle 處的運行範例。
注意：此範例包括兩場比賽和兩個級別，總共產生 4 種組合。新增其餘部分，查詢將產生全部 108 個。