пїЅ пїЅ пїЅ пїЅ пїЅ
Меню сайта

облако тегов

Интересне SQL запросы)

# вещи на которых можно навариться 
SELECT * FROM `item_template` WHERE (`sellprice` * `buycount` > `buyprice`) and entry in (select item from npc_vendor); 
SELECT * FROM `item_template` WHERE (`buycount` = 0) AND (`sellprice` > `buyprice`) and entry in (select item from npc_vendor); 

#лишние записи по игрокам (все "SELECT *" можно смело менять на "DELETE" ) 
SELECT * FROM `character_action` where guid not in (select guid from `character`); 
SELECT * FROM `character_aura` where guid not in (select guid from `character`); 
SELECT * FROM `character_homebind` where guid not in (select guid from `character`);  
SELECT * FROM `character_inventory` where guid not in (select guid from `character`); 
SELECT * FROM `character_kill` where guid not in (select guid from `character`);  
SELECT * FROM `character_pet` where guid not in (select guid from `character`);  
SELECT * FROM `character_queststatus` where guid not in (select guid from `character`); 
SELECT * FROM `character_reputation` where guid not in (select guid from `character`);  
SELECT * FROM `character_social` where guid not in (select guid from `character`);  
SELECT * FROM `character_spell` where guid not in (select guid from `character`); 
SELECT * FROM `character_spell_cooldown` where guid not in (select guid from `character`);  
SELECT * FROM `character_stable` where guid not in (select guid from `character`);  
SELECT * FROM `character_ticket` where guid not in (select guid from `character`); 
SELECT * FROM `character_tutorial` where guid not in (select guid from `character`);  
SELECT * FROM `corpse` where `player` not in (select guid from `character`); 
SELECT * FROM `character_inventory` where item not in (SELECT guid FROM `item_instance`); 
SELECT * FROM `item_instance` where guid not in (SELECT item FROM `character_inventory`); 
SELECT * FROM `character_queststatus` where quest not in (select `entry` from `quest_template`); 
SELECT * FROM `character_social` where `friend` not in (select guid from `character`);  

# неверные предметы у продавцов ("SELECT *" -> "DELETE" для очистки) 
SELECT * FROM `mangos`.`npc_vendor` WHERE `item` not in (SELECT `entry` FROM `item_template`); 

# предметы для продажи есть, однако NPC не может их продать (flag не содержит 4) 
# фиксить:  
# или изменить флаг (если это действительно продавец), 
# или удалить записи из npc_vendor (если не продавец) 
SELECT * FROM `creature_template` WHERE (`entry` in (SELECT `entry` from `npc_vendor`)) AND (`npcflag` &  4 <> 4); 

# несуществующие продавцы 
SELECT * FROM `npc_vendor` WHERE `entry` not in (SELECT `entry` from `creature_template`); 

# несуществующие тренеры 
SELECT * FROM `npc_trainer` WHERE `entry` not in (SELECT `entry` from `creature_template`); 

# простая проверка на квесты (вообще их куча как в ядре, так и в моей программе) 
SELECT * FROM `mangos`.`quest_template` where minlevel>questlevel 

# аукцион 
SELECT * FROM `auctionhouse` where `auctioneerguid` not in (select guid from `character`); 
SELECT * FROM `auctionhouse` where `itemowner` not in (select guid from `character`); 

# мобы с маленькими / неверными жизнями / уровнями 
SELECT * FROM `mangos`.`creature` where curhealth < 20; 
SELECT * FROM `mangos`.`creature_template` where minhealth>maxhealth; 
SELECT * FROM `mangos`.`creature_template` where (minhealth< 20 ) or (maxhealth<20); 
SELECT * FROM `mangos`.`creature_template` where minlevel>maxlevel; 
SELECT * FROM `mangos`.`creature_template` where (minlevel=0) or (maxlevel=0); 

# несуществующие мобы 
SELECT * FROM `creature` c where id  not in (select entry from creature_template);

Фиксы на 2 первых запроса?

 

Код: 

UPDATE `item_template` SET `sellprice` = (`buyprice` /`buycount`) WHERE (`sellprice` * `buycount` > `buyprice`) and entry in (select item from npc_vendor); 
UPDATE `item_template` SET `sellprice` =  `buyprice` WHERE (`buycount` = 0) AND (`sellprice` > `buyprice`) and entry in (select item from npc_vendor); 


Вход/Регистрация

Добро пожаловать,
Гость

Регистрация или входРегистрация или вход
Потеряли пароль?Потеряли пароль?

Ник:
Пароль:

АдминистрацияАдминистрация:0
ПользователейПользователей:0
Поисковых ботовПоисковых ботов:0
ГостейГостей:6

ОбновитьВсегоВсего:6

Партнеры

Посещаемость сайта
Яндекс.Метрика

Копирование материалов без активной гиперссылки на pelmeshka-wowonline.ru запрещено


The release is prepared by ARTGLOBALS.COM, Sunvas and AS