Ghtweb 4.0.11

[LastDeath 5]

Кто поможет написать запрос для сборки open-team?

[Boolean 121]

О каком запросе речь?

[Elione 265]

c 5го возьми за пример и допили.

[ghostik32 4]

Вот загенерил, распихай их по методам

# Server type: OpenTeam (hf)

# accounts
SELECT
login, password, access_level, lastactive AS last_active
FROM accounts

# characters
SELECT
characters.account_name, characters.obj_Id AS char_id, characters.char_name, characters.sex, characters.x, characters.y, characters.z, characters.karma, characters.pvpkills, characters.pkkills, characters.clanid AS clan_id, characters.title, characters.accesslevel AS access_level, characters.online, characters.onlinetime, character_subclasses.class_id AS base_class, character_subclasses.level, character_subclasses.exp, character_subclasses.sp, character_subclasses.maxHp, character_subclasses.curHp, character_subclasses.maxCp, character_subclasses.curCp, character_subclasses.maxMp, character_subclasses.curMp, clan_data.clan_name, clan_data.clan_level, clan_data.hasCastle, clan_data.crest AS clan_crest, clan_data.reputation_score
FROM characters
LEFT JOIN character_subclasses ON character_subclasses.char_obj_id = characters.obj_Id AND character_subclasses.isBase = 1
LEFT JOIN clan_data ON clan_data.clan_id = characters.clanid

# clans
SELECT
clan_data.clan_id, clan_data.clan_name, clan_data.leader_id, clan_data.clan_level, clan_data.hasCastle, clan_data.crest AS clan_crest, clan_data.reputation_score, (SELECT COUNT(0) FROM characters WHERE characters.clanid = clan_data.clan_id) as ccount, ally_data.ally_name, ally_data.crest AS ally_crest, ally_data.ally_id, characters.account_name, characters.obj_Id AS char_id, characters.char_name, characters.sex, characters.x, characters.y, characters.z, characters.karma, characters.pvpkills, characters.pkkills, characters.title, characters.accesslevel AS access_level, characters.online, characters.onlinetime, character_subclasses.class_id AS base_class, character_subclasses.level, character_subclasses.exp, character_subclasses.sp, character_subclasses.maxHp, character_subclasses.curHp, character_subclasses.maxCp, character_subclasses.curCp, character_subclasses.maxMp, character_subclasses.curMp
FROM clan_data
LEFT JOIN ally_data ON clan_data.ally_id = ally_data.ally_id
LEFT JOIN characters ON characters.obj_Id = clan_data.leader_id
LEFT JOIN character_subclasses ON character_subclasses.char_obj_id = characters.obj_Id AND character_subclasses.isBase = 1

# items
SELECT
items.owner_id, items.object_id, items.item_id, items.count, items.enchant_level, items.loc, items.loc_data
FROM items

# insertItems
INSERT INTO `items_delayed` (`owner_id`, `item_id`, `count`, `enchant_level`, `description`) VALUES(:owner_id, :item_id, :count, :enchant_level, :description)

# getCountRaceHuman
SELECT
COUNT(0) as count
FROM character_subclasses
WHERE class_id IN (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98) AND character_subclasses.isBase = 1

# getCountRaceElf
SELECT
COUNT(0) as count
FROM character_subclasses
WHERE class_id IN (18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 99, 100, 101, 102, 103, 104, 105) AND character_subclasses.isBase = 1

# getCountRaceDarkElf
SELECT
COUNT(0) as count
FROM character_subclasses
WHERE class_id IN (31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 106, 107, 108, 109, 110, 111, 112) AND character_subclasses.isBase = 1

# getCountRaceOrk
SELECT
COUNT(0) as count
FROM character_subclasses
WHERE class_id IN (44, 45, 46, 47, 48, 49, 50, 51, 52, 113, 114, 115, 116) AND character_subclasses.isBase = 1

# getCountRaceDwarf
SELECT
COUNT(0) as count
FROM character_subclasses
WHERE class_id IN (53, 54, 55, 56, 57, 117, 118) AND character_subclasses.isBase = 1

# getCountRaceKamael
SELECT
COUNT(0) as count
FROM character_subclasses
WHERE class_id IN (123, 124, 125, 126, 127, 128, 129, 130, 131, 132, 133, 134, 135, 136, 139, 140, 141, 142, 143, 144, 145, 146) AND character_subclasses.isBase = 1

# getCountAccounts
SELECT COUNT(0) as count FROM accounts

# getCountCharacters
SELECT COUNT(0) as count FROM characters

# getCountOnlineCharacters
SELECT COUNT(0) as count FROM characters WHERE online = 1

# getCountClans
SELECT COUNT(0) as count FROM clan_data

# getCountMen
SELECT COUNT(0) as count FROM characters WHERE sex = 0

# getCountWomen
SELECT COUNT(0) as count FROM characters WHERE sex = 1

# getCastles
SELECT
castle.id, castle.name, castle.taxPercent, castle.siegeDate, clan_data.clan_id, clan_data.clan_name, clan_data.leader_id, clan_data.clan_level, clan_data.hasCastle, clan_data.crest AS clan_crest, clan_data.reputation_score, ally_data.ally_name, ally_data.crest AS ally_crest, ally_data.ally_id
FROM castle
LEFT JOIN clan_data ON castle.id = clan_data.hasCastle
LEFT JOIN ally_data ON clan_data.ally_id = ally_data.ally_id

# getSiege
SELECT
siege_clans.unit_id AS castle_id, siege_clans.type, clan_data.clan_id, clan_data.clan_name, clan_data.leader_id, clan_data.clan_level, clan_data.hasCastle, clan_data.crest AS clan_crest, clan_data.reputation_score, ally_data.ally_name, ally_data.crest AS ally_crest, ally_data.ally_id
FROM siege_clans
LEFT JOIN clan_data ON siege_clans.clan_id = clan_data.clan_id
LEFT JOIN ally_data ON clan_data.ally_id = ally_data.ally_id

 

 

Изменено 25 сентября, 2014 пользователем ghostik32