-- ================================================================================

-- `MySQL NDB Cluster`

-- ================================================================================


-- ----------------------------------------
-- [`ndb_info`]
-- ----------------------------------------

-- 데이터 메모리 사용량 현황
SELECT * FROM `ndbinfo`.`memoryusage`;





-- ================================================================================

-- `eod_configuration`

-- ================================================================================


-- ----------------------------------------
-- [`table_game_server`]
-- ----------------------------------------

-- 게임 서버 현황
SELECT * FROM `eod_configuration`.`table_game_server`;





-- ================================================================================

-- `eod_authentication`

-- ================================================================================


-- ----------------------------------------
-- [`table_account_user`]
-- ----------------------------------------

-- 계정 개수
SELECT COUNT(1) FROM `eod_authentication`.`table_account_user`;

-- 사용자 ID로 계정 찾기
SELECT * FROM `eod_authentication`.`table_account_user` WHERE (`user_id` = 1);

-- 사용자 이름으로 계정 찾기
SELECT * FROM `eod_authentication`.`table_account_user` WHERE (`nickname` = '프리메라');
SELECT * FROM `eod_authentication`.`table_account_user` WHERE (`nickname` IN('앵김떠줄께', '진양호풋살'));
SELECT * FROM `eod_authentication`.`table_account_user` WHERE (`nickname` = '414');
table_billingtable_billing

SELECT * FROM `eod_authentication`.`table_account_user` 
WHERE (DATE_FORMAT(`join_datetime`, '%Y-%m-%d') = '2017-03-07');

-- 시리얼 코드로 계정 찾기
SELECT * FROM `eod_authentication`.`table_account_user` 
WHERE (`serial_code` IN ('8L66PTG62KUMSV0H'));


-- ----------------------------------------
-- [`table_account_relationship`]
-- ----------------------------------------

-- 
SELECT COUNT(1) FROM `eod_authentication`.`table_account_relationship`;
SELECT * FROM `eod_authentication`.`table_account_relationship` WHERE (`user_id` = 44);
SELECT * FROM `eod_authentication`.`table_account_relationship` WHERE (`user_id` = 1);

-- 특정 이름에 해당하는 계정 연동 정보
SELECT * FROM `eod_authentication`.`table_account_relationship` 
WHERE (`user_id` = (SELECT `user_id` FROM `eod_authentication`.`table_account_user` WHERE (`nickname` = 'koko99')));

-- 특정 시리얼 코드에 해당하는 계정 연동 정보
SELECT * FROM `eod_authentication`.`table_account_relationship` AS relationshipTable
RIGHT JOIN 
(
    SELECT 
        `user_id`, 
        `serial_code`, 
        `nickname`
    FROM `eod_authentication`.`table_account_user` 
    WHERE (`serial_code` IN ('JFXZSD4NQH009IXF'))
) AS accountTable
ON (`relationshipTable`.`user_id` = `accountTable`.`user_id`);

-- 특정 사용자의 Google 계정 연동을 해제하기
-- : 연동 정보를 제거하고, 게스트 상태로 되돌린다.
EXPLAIN UPDATE `eod_authentication`.`table_account_relationship`
SET
    `user_id` = 0,
    `related` = 0
WHERE (`user_id` IN(10000000000002915));

EXPLAIN UPDATE `eod_game`.`table_player`
SET
    `related_account_facebook` = ''
WHERE (`user_id` IN(7697));

SELECT * FROM `eod_game`.`table_player` WHERE (`user_id` IN(7697));

SELECT * FROM `eod_authentication`.`table_account_relationship`
WHERE (`user_id` = 81);

EXPLAIN UPDATE `eod_authentication`.`table_account_relationship`
SET
    `user_id` = 0,
    `related` = 0
WHERE (`user_id` = (SELECT `user_id` FROM `eod_authentication`.`table_account_user` WHERE (`serial_code` = 'R8VAGIIQDH1R9USO')));


-- 특정 사용자의 Facebook 계정 연동을 해제하기
SELECT * FROM `eod_game`.`table_player` WHERE (`user_id` = 81);
EXPLAIN UPDATE `eod_game`.`table_player`
SET
    `related_account_facebook` = ''
WHERE (`user_id` = 81);



-- ================================================================================

-- `eod_game`

-- ================================================================================


-- ----------------------------------------
-- [`table_attendance_special`]
-- ----------------------------------------

SELECT * FROM `eod_game`.`table_attendance_special` WHERE (`character_id` IN(10000000000019515,
10000000000002170,
10000000000030539,
10000000000030494));

SELECT * FROM `eod_game`.`table_attendance_special` WHERE (`event_code` = 1002) AND (`attendance_count` > 1);



-- ----------------------------------------
-- [`table_player`]
-- ----------------------------------------

-- 플레이어의 개수
SELECT COUNT(1) FROM `eod_game`.`table_player` WHERE 1;

-- 사용자 ID로 플레이어 찾기
SELECT * FROM `eod_game`.`table_player` WHERE (`user_id` = 173);
SELECT * FROM `eod_game`.`table_player` WHERE (`user_id` = 20000000000057696);

-- 사용자 이름으로 플레이어 찾기
SELECT * FROM `eod_game`.`table_player` WHERE (`nickname` = '반항아');
SELECT * FROM `eod_game`.`table_player` WHERE (`nickname` = '마이쿤이');
SELECT * FROM `eod_game`.`table_player` WHERE (`nickname` IN('맹꽁이', '활화산', '해류', '쨉쨉'));
SELECT * FROM `eod_game`.`table_player`  
WHERE (`user_id` IN (SELECT `owner_user_id` FROM `eod_game`.`table_player_character` WHERE (`character_id` IN (61))));
#DESCRIBE DELETE FROM `eod_game`.`table_player` WHERE (`user_id` = 30);

-- 플레이어 캐릭터 번호로 계정 찾기
SELECT * FROM `eod_game`.`table_player` 
WHERE (`user_id` = (SELECT `owner_user_id` FROM `eod_game`.`table_player_character` WHERE (`character_id` IN (32))));

-- 수호자 레벨 높은 순으로...
SELECT * FROM `eod_game`.`table_player` 
ORDER BY `guardian_level` DESC
LIMIT 100;



-- ----------------------------------------
-- [`table_player_character`]
-- ----------------------------------------

-- 특정 레벨 이상의 캐릭터 개수
SELECT COUNT(1) FROM `eod_game`.`table_player_character` WHERE (`level` >= 1);

SELECT 
    `character_id`, 
    `owner_user_id`,
    `character_class_type`,
    `level`
FROM `eod_game`.`table_player_character`
ORDER BY `owner_user_id`;

SELECT 
    `characterTable`.`owner_user_id`,
    `playerTable`.`nickname`,
    `characterTable`.`character_id`, 
    `characterTable`.`character_class_type`,
    `characterTable`.`level`
FROM `eod_game`.`table_player_character` AS `characterTable`
INNER JOIN
(
    SELECT `user_id`, `nickname` FROM `eod_game`.`table_player`
) AS playerTable
ON (`characterTable`.`owner_user_id` = `playerTable`.`user_id`);

-- 사용자 ID의 플레이어 캐릭터들 찾기
SELECT * FROM `eod_game`.`table_player_character` WHERE (`owner_user_id` = 1);
SELECT * FROM `eod_game`.`table_player_character` WHERE (`owner_user_id` IN(10000000000043813));

SELECT * FROM `eod_game`.`table_player_character` WHERE (`character_id` = 173);
SELECT * FROM `eod_game`.`table_player_character` WHERE (`character_id` = 20000000000061566);

-- 특정 사용자 이름의 플레이어 캐릭터들 전부 가져오기
SELECT * FROM `eod_game`.`table_player_character` AS tableCharacter
INNER JOIN
(
    SELECT `user_id` FROM `eod_game`.`table_player` WHERE (`nickname` = '문제가뭐냐')
) AS tablePlayer
ON (`tableCharacter`.`owner_user_id` = `tablePlayer`.`user_id`);

SELECT * FROM `eod_game`.`table_player` 
ORDER BY `money_gamecash_by_bonus` + `money_gamecash_by_purchase` DESC LIMIT 100;

SELECT * FROM `eod_game`.`table_player_character` 
ORDER BY `level` DESC LIMIT 100;

SELECT * FROM `eod_game`.`table_player` AS table1 WHERE (`user_id` = (SELECT `owner_user_id` FROM `eod_game`.`table_player_character` AS table2))
ORDER BY `table2`.`money_gameplay` DESC LIMIT 100;


-- ----------------------------------------
-- [`table_equipment_item`]
-- ----------------------------------------

SELECT * FROM `eod_game`.`table_equipment_item` WHERE (`item_id` = 479);

-- 최대 부여한 아이템의 ID
SELECT MAX(`item_id`) FROM `eod_game`.`table_equipment_item`;

SELECT * FROM `eod_game`.`table_equipment_item` WHERE (`owner_character_id` = 173) AND (`state` = 0) LIMIT 1000;

SELECT COUNT(1) FROM `eod_game`.`table_equipment_item` WHERE (`state` = 1);

-- 옵션 계수가 0보다 큰 아이템들의 존재 유무
SELECT COUNT(1) FROM `eod_game`.`table_equipment_item` WHERE (`option_factor` > 0);

SELECT * FROM `eod_game`.`table_equipment_item` WHERE (`item_id` = 26263611);


-- ----------------------------------------
-- [`table_equipment_item_equipped`]
-- ----------------------------------------

-- 특정 캐릭터의 아이템 장착 정보
SELECT * FROM `eod_game`.`table_equipment_item_equipped` WHERE (`character_id` = 1);


-- ----------------------------------------
-- [``]
-- ----------------------------------------

DESCRIBE DELETE FROM `eod_game`.`table_ticket` WHERE 1;


-- ----------------------------------------
-- [`table_skill`]
-- ----------------------------------------

SELECT * FROM `eod_game`.`table_skill` WHERE (`character_id` = 95);
DESCRIBE DELETE FROM `eod_game`.`table_skill` WHERE (`character_id` = 95);


-- ----------------------------------------
-- [`table_skill_equipped`]
-- ----------------------------------------

SELECT * FROM `eod_game`.`table_skill_equipped` WHERE (`character_id` = 94);


-- ----------------------------------------
-- [`table_stage_record`]
-- ----------------------------------------

SELECT * FROM `eod_game`.`table_stage_record` WHERE (`character_id` = 3);
SELECT * FROM `eod_game`.`table_stage_record` WHERE (`normal_last_played_act_number` > 8);
SELECT 
    `character_id`,
    `arena_async_auth_number`, 
    `arena_async_start_datetime`,     
    `arena_async_remain_play_count`, 
    `arena_async_play_count_max`, 
    `arena_async_reset_count`, 
    `arena_async_reset_datetime`, 
	`arena_async_straight_win_count`, 
	`arena_async_straight_defeat_count`, 
	`update_datetime`
FROM `eod_game`.`table_stage_record` WHERE (`character_id` = 1);
UPDATE `eod_game`.`table_stage_record`
SET
    `arena_async_reset_count` = 5,
    `arena_async_remain_play_count` = 0
WHERE (`character_id` = 68);
SELECT * FROM `eod_game`.`table_stage_record` WHERE (`character_id` = 17);
SELECT * FROM `eod_game`.`table_stage_record` 
WHERE (`normal_far_played_act_number` = 8) AND (`normal_far_played_stage_number_in_act` >= 1) 
ORDER BY `normal_far_played_stage_number_in_act` DESC 
LIMIT 100;
DESCRIBE DELETE FROM  `eod_game`.`table_stage_record` WHERE (`character_id` = 1);


-- ----------------------------------------
-- [`table_stage_normal_accomplishment`]
-- ----------------------------------------

SELECT * FROM `eod_game`.`table_stage_normal_accomplishment` WHERE (`character_id` = 92) AND (`act_number` = 1);

SELECT * FROM `eod_game`.`table_stage_normal_accomplishment` 
WHERE (`character_id` = 45977) AND
      (`act_number` = 2) AND
      (`stage_number_in_act` = 4);
      
-- 특정 액트의 특정 스테이지를 완료한 캐릭터 찾기
SELECT * FROM `eod_game`.`table_stage_normal_accomplishment`
WHERE (`act_number` = 9) AND 
      (`stage_number_in_act` > 0) AND 
      (`clear_grade` >= 3);
      
-- 특정 액트의 특정 스테이지를 완료한 캐릭터의 정보 가져오기
SELECT * FROM `eod_game`.`table_player_character` AS characterTable
INNER JOIN
(
    SELECT `character_id` FROM `eod_game`.`table_stage_normal_accomplishment`
    WHERE (`act_number` = 9) AND 
          (`stage_number_in_act` > 0) AND 
          (`clear_grade` >= 3)
) AS stageClearTable
ON (`characterTable`.`character_id` = `stageClearTable`.`character_id`)
GROUP BY `characterTable`.`character_id`;

SELECT * FROM `eod_game`.`table_player` WHERE (`user_id` = 10000000000000860);


DESCRIBE DELETE FROM  `eod_game`.`table_stage_normal_accomplishment` WHERE 1;


-- ----------------------------------------
-- [`table_stage_elite_accomplishment`]
-- ----------------------------------------

SELECT * FROM `eod_game`.`table_stage_elite_accomplishment` WHERE (`character_id` = 92) AND (`act_number` = 1);
DESCRIBE DELETE FROM  `eod_game`.`table_stage_elite_accomplishment` WHERE 1;
SELECT * FROM `eod_game`.`table_stage_elite_act_record` WHERE (`character_id` = 92);

-- 특정 액트의 특정 스테이지를 완료한 캐릭터 찾기
SELECT * FROM `eod_game`.`table_stage_elite_accomplishment`
WHERE (`act_number` = 8) AND 
      (`stage_number_in_act` > 0) AND 
      (`clear_grade` >= 3);
      
-- 특정 액트의 특정 스테이지를 완료한 캐릭터의 정보 가져오기
SELECT * FROM `eod_game`.`table_player_character` AS characterTable
INNER JOIN
(
    SELECT `character_id` FROM `eod_game`.`table_stage_elite_accomplishment`
    WHERE (`act_number` = 8) AND 
          (`stage_number_in_act` > 0) AND 
          (`clear_grade` >= 3)
) AS stageClearTable
ON (`characterTable`.`character_id` = `stageClearTable`.`character_id`)
GROUP BY `characterTable`.`character_id`;



-- ----------------------------------------
-- [`table_stage_rift_accomplishment`]
-- ----------------------------------------

SELECT * FROM `eod_game`.`table_stage_rift_accomplishment` WHERE (`character_id` = 69);
DESCRIBE DELETE FROM  `eod_game`.`table_stage_rift_accomplishment` WHERE 1;

SELECT * FROM `eod_game`.`table_stage_rift_accomplishment` 
WHERE (`character_id` = 69);


-- ----------------------------------------
-- [`table_stage_transcendence_accomplishment`]
-- ----------------------------------------

SELECT * FROM `eod_game`.`table_stage_transcendence_accomplishment` WHERE (`character_id` = 68);
DESCRIBE DELETE FROM  `eod_game`.`table_stage_transcendence_accomplishment` WHERE 1;

-- 특정 단계의 초월 레벨 찾기
SELECT * FROM `eod_game`.`table_stage_transcendence_accomplishment` WHERE (`stage_grade` = 120) LIMIT 1000;


-- ----------------------------------------
-- [`table_stage_weekly_accomplishment`]
-- ----------------------------------------

SELECT * FROM `eod_game`.`table_stage_weekly_accomplishment` WHERE 1;
DESCRIBE DELETE FROM  `eod_game`.`table_stage_weekly_accomplishment` WHERE 1;


-- ----------------------------------------
-- [`table_stage_occupation_war_accomplishment`]
-- ----------------------------------------

SELECT * FROM `eod_game`.`table_stage_occupation_war_accomplishment` WHERE (`user_id` = 293);
SELECT * FROM `eod_game`.`table_stage_occupation_war_accomplishment` WHERE 1;


-- ----------------------------------------
-- [`table_stage_normal_act_record`]
-- ----------------------------------------

SELECT * FROM `eod_game`.`table_stage_normal_act_record` WHERE (`character_id` = 1);
DESCRIBE DELETE FROM `eod_game`.`table_stage_normal_act_record` WHERE (`character_id` = 57);


-- ----------------------------------------
-- [`table_stage_elite_act_record`]
-- ----------------------------------------

SELECT * FROM `eod_game`.`table_stage_elite_act_record` WHERE (`character_id` = 1);
DESCRIBE DELETE FROM `eod_game`.`table_stage_elite_act_record` WHERE (`character_id` != 0);


-- ----------------------------------------
-- [`table_stage_guardian_raid_accomplishment`]
-- ----------------------------------------

SELECT * FROM `eod_game`.`table_stage_guardian_raid_accomplishment` WHERE (`user_id` = 3);


-- ----------------------------------------
-- [`table_equipment_item`]
-- ----------------------------------------

SELECT * FROM `eod_game`.`table_equipment_item` WHERE (`owner_character_id` = 61);
DESCRIBE DELETE FROM `eod_game`.`table_equipment_item`;

SELECT COUNT(1) FROM `eod_game`.`table_equipment_item` WHERE (`option_factor` > 0);


-- ----------------------------------------
-- [`table_equipment_item_equipped`]
-- ----------------------------------------

SELECT * FROM `eod_game`.`table_equipment_item_equipped` WHERE 1;
DESCRIBE DELETE FROM `eod_game`.`table_equipment_item_equipped` WHERE (`character_id` != 0) AND (`equip_slot_type` = 0);


-- ----------------------------------------
-- [`table_avatar_item`]
-- ----------------------------------------

SELECT * FROM `eod_game`.`table_avatar_item` WHERE (`character_id` = 1);
DESCRIBE DELETE FROM `eod_game`.`table_avatar_item` WHERE (`character_id` = 75);


-- ----------------------------------------
-- [`table_avatar_item_equipped`]
-- ----------------------------------------

SELECT * FROM `eod_game`.`table_avatar_item_equipped` WHERE (`character_id` = 317);
DESCRIBE DELETE FROM `eod_game`.`table_avatar_item_equipped` WHERE (`character_id` = 75);


-- ----------------------------------------
-- [`table_guardian_stone`]
-- ----------------------------------------

SELECT * FROM `eod_game`.`table_guardian_stone` WHERE (`character_id` = 285);
DESCRIBE DELETE FROM `eod_game`.`table_guardian_stone` WHERE (`character_id` = 75);


-- ----------------------------------------
-- [`table_guardian_stone_equipped`]
-- ----------------------------------------

SELECT * FROM `eod_game`.`table_guardian_stone_equipped` WHERE (`character_id` = 285);
DESCRIBE DELETE FROM `eod_game`.`table_guardian_stone_equipped` WHERE (`character_id` = 47);


-- ----------------------------------------
-- [`table_worth`]
-- ----------------------------------------

SELECT COUNT(1) FROM `eod_game`.`table_worth` WHERE (`character_id` = 3);

SELECT * FROM `eod_game`.`table_worth` WHERE (`character_id` = 3);
SELECT * FROM `eod_game`.`table_worth` WHERE (`character_id` = 3) AND (`worth_code` = 160004005);

SELECT * FROM `eod_game`.`table_worth` 
WHERE (`character_id` = 1);

SELECT * FROM `eod_game`.`table_worth` 
WHERE (`character_id` = 115) AND (`worth_code` BETWEEN 160003101 AND 160003108);


-- ----------------------------------------
-- [`table_stage_elite_accomplishment`]
-- ----------------------------------------

SELECT * FROM `eod_game`.`table_stage_elite_accomplishment` WHERE 1;


-- ----------------------------------------
-- [`table_servant`]
-- ----------------------------------------

-- 특정 캐릭터의 조력자 찾기
SELECT * FROM `eod_game`.`table_servant` WHERE (`character_id` = 1);
DESCRIBE DELETE FROM `eod_game`.`table_servant` WHERE 1;


-- ----------------------------------------
-- [`table_servant_equipped`]
-- ----------------------------------------

SELECT * FROM `eod_game`.`table_servant_equipped` WHERE (`character_id` = 467);


-- ----------------------------------------
-- [`table_servant_collection_reward`]
-- ----------------------------------------

SELECT * FROM `eod_game`.`table_servant_collection_reward` WHERE (`character_id` = 68);


-- ----------------------------------------
-- [`table_post`]
-- ----------------------------------------

-- 현재까지 부여된 가장 높은 ID 값
SELECT MAX(`no`) FROM `eod_game`.`table_post`;

SELECT * FROM `eod_game`.`table_post` WHERE (`no` = 5567);

SELECT * FROM `eod_game`.`table_post` WHERE (`receiver_id` = 5567) 
ORDER BY `no` DESC
LIMIT 1000;

DESCRIBE DELETE FROM `eod_game`.`table_post` WHERE (`receiver_id` = 101);

SELECT * FROM `eod_game`.`table_post` 
WHERE (`receiver_id` = 5567) AND 
      (`received_datetime` BETWEEN '2017-05-22 00:00:00' AND '2017-05-22 23:59:59') 
ORDER BY `no` DESC;


-- ----------------------------------------
-- [`table_achievement`]
-- ----------------------------------------

SELECT * FROM `eod_game`.`table_achievement` WHERE (`user_id` = 87);
DESCRIBE DELETE FROM `eod_game`.`table_achievement` WHERE (`user_id` = 1);

SELECT * FROM `eod_game`.`table_achievement` 
WHERE (`user_id` = 87) AND (`character_id` = 141);

SELECT * FROM `eod_game`.`table_achievement` 
WHERE (`user_id` = 1) AND (`character_id` = 1);

SELECT * FROM `eod_game`.`table_achievement` 
WHERE (`user_id` = 1) AND (`character_id` = 1) AND (`achievement_code` > 908231000);

DESCRIBE DELETE FROM `eod_game`.`table_achievement` 
WHERE (`achievement_code` > 908231000);


-- ----------------------------------------
-- [`table_achievement_record`]
-- ----------------------------------------

SELECT * FROM `eod_game`.`table_achievement_record` WHERE (`user_id` = 1);

SELECT * FROM `eod_game`.`table_achievement_record` WHERE (`user_id` = 1) AND (`character_id` = 0);
SELECT * FROM `eod_game`.`table_achievement_record` WHERE (`user_id` = 1) AND (`character_id` = 1);

DESCRIBE DELETE FROM `eod_game`.`table_achievement_record` WHERE (`user_id` = 1) AND (`character_id` = 0);
DESCRIBE DELETE FROM `eod_game`.`table_achievement_record` 
WHERE (`group_high` = 7) AND (`group_mid` = 1);


-- ----------------------------------------
-- [`table_mission`]
-- ----------------------------------------

-- 특정 캐릭터의 미션 데이터 가져오기
SELECT * FROM `eod_game`.`table_mission` WHERE (`character_id` = 23816);

-- 특정 캐릭터의 특정 미션 데이터 가져오기
SELECT * FROM `eod_game`.`table_mission` WHERE (`character_id` = 1) AND (`mission_code` IN(703411001));

-- 특정 캐릭터의 미션 데이터들 지우기
DESCRIBE DELETE FROM `eod_game`.`table_mission` WHERE (`character_id` = 1);

SELECT * FROM `eod_game`.`table_mission` 
WHERE (`expired_datetime` = '2017-03-01 00:00:00')
ORDER BY `character_id`;

SELECT * FROM `eod_game`.`table_mission` 
WHERE (`expired_datetime` = '2017-04-10 00:00:00')
ORDER BY `character_id`;

SELECT * FROM `eod_game`.`table_mission` 
WHERE (`mission_code` BETWEEN 300000000 AND 399999999) AND (`expired_datetime` = '2017-04-01 00:00:00') 
ORDER BY `character_id`, `mission_code`;

SELECT * FROM `eod_game`.`table_mission` 
WHERE (`character_id` = 23816) AND
      (`mission_code` BETWEEN 700000000 AND 799999999);
      
SELECT * FROM `eod_game`.`table_mission` 
WHERE (`character_id` IN(47094, 48589, 50695, 51411, 53533)) AND
      (`mission_code` BETWEEN 300000000 AND 399999999)
ORDER BY `character_id`, `mission_code`;
      
      
SELECT `expired_datetime` FROM `eod_game`.`table_mission` 
WHERE (`mission_code` BETWEEN 300000000 AND 399999999) AND (`expired_datetime` < '2017-05-01 00:00:00')
ORDER BY `expired_datetime` DESC LIMIT 100;

SELECT * FROM `eod_game`.`table_player_character` AS characterTable
INNER JOIN
(
    SELECT `character_id` FROM `eod_game`.`table_mission` 
    WHERE (`mission_code` BETWEEN 300000000 AND 399999999) AND (`expired_datetime` = '2017-04-01 00:00:00')
) AS missionTable
ON (`characterTable`.`character_id` = `missionTable`.`character_id`) AND (`last_played_datetime` < '2017-03-24 14:00:00')
GROUP BY `characterTable`.`character_id`
ORDER BY `owner_user_id`;


SELECT 
    `user_id`, 
    `nickname`, 
    `join_datetime` 
FROM `eod_authentication`.`table_account_user` AS playerTable
INNER JOIN
(
    SELECT 
        `characterTable`.`character_id`, 
        `characterTable`.`owner_user_id`, 
        `characterTable`.`character_class_type`, 
        `characterTable`.`level`
    FROM `eod_game`.`table_player_character` AS characterTable
    INNER JOIN
    (
        SELECT `character_id` 
        FROM `eod_game`.`table_mission` 
        WHERE (`mission_code` BETWEEN 300000000 AND 399999999) AND (`expired_datetime` = '2017-04-01 00:00:00')
    ) AS missionTable
    ON (`characterTable`.`character_id` = `missionTable`.`character_id`)
    GROUP BY `characterTable`.`character_id`
) AS t1
ON (`playerTable`.`user_id` = `t1`.`owner_user_id`) AND (`playerTable`.`join_datetime` < '2017-03-25 00:00:00')
GROUP BY `user_id`
ORDER BY `user_id`;


-- ----------------------------------------
-- [`table_quest`]
-- ----------------------------------------

SELECT * FROM `eod_game`.`table_quest` WHERE (`character_id` = 126);


-- ----------------------------------------
-- [``]
-- ----------------------------------------

SELECT * FROM `eod_game`.`table_player_character` WHERE (`character_id` = 20000000000002770);

SELECT * FROM `eod_game`.`table_shop_buy_count` WHERE (`user_id` = 65) AND (`character_id` = 216);

SELECT * FROM `eod_game`.`table_shop_buy_count` WHERE (`user_id` = 20000000000003528) ORDER BY `product_code`;


-- ----------------------------------------
-- [``]
-- ----------------------------------------

SELECT * FROM `eod_game`.`table_tutorial` WHERE (`character_id` IN (3265, 3266, 3267));


SELECT * FROM `eod_game`.`table_post` WHERE (`no` = 327);
SELECT * FROM `eod_game`.`table_post` WHERE (`receiver_character_id` = 1) ORDER BY `received_datetime` DESC LIMIT 100;
SELECT * FROM `eod_game`.`table_post` WHERE (`receiver_character_id` = 278) AND (`state` = 0) ORDER BY `received_datetime` DESC LIMIT 100;
SELECT COUNT(1) FROM `eod_game`.`table_post` 
WHERE (`receiver_character_id` = 278) AND (`state` = 0) AND (`item_code` = 155101001)
ORDER BY `received_datetime` DESC LIMIT 100;







-- ================================================================================

--  `eod_community`

-- ================================================================================


-- ----------------------------------------
-- [`table_guild`]
-- ----------------------------------------

SELECT COUNT(1) FROM `eod_community`.`table_guild`;

SELECT * FROM `eod_community`.`table_guild` WHERE (`guild_id` = 10000000000000004);

SELECT * FROM `eod_community`.`table_guild` WHERE (`name` = '빛한별');

-- 길드의 레벨 순위
SELECT * FROM `eod_community`.`table_guild` ORDER BY `level` DESC LIMIT 100;


SELECT * FROM `eod_community`.`table_guild` WHERE (`state` != 3) AND (`master_player_id` = 0);


-- ----------------------------------------
-- [`table_guild_membership`]
-- ----------------------------------------

SELECT COUNT(1) FROM `eod_community`.`table_guild_membership` WHERE (`guild_id` = );

-- 길드 멤버 가져오기
SELECT * FROM `eod_community`.`table_guild_membership` WHERE (`guild_id` = 16);
SELECT * FROM `eod_community`.`table_guild_membership` WHERE (`guild_id` = 3);

SELECT * FROM `eod_community`.`table_guild_membership` WHERE (`player_id` = 10000000000000003);

SELECT * FROM `eod_community`.`table_guild_membership` 
WHERE (`guild_id` = (SELECT `guild_id` FROM `eod_community`.`table_guild` WHERE (`name` = 'testguild') LIMIT 1));


-- ----------------------------------------
-- [`table_guild_join_request`]
-- ----------------------------------------

SELECT * FROM `eod_community`.`table_guild_join_request` WHERE (`guild_id` = 429);
SELECT * FROM `eod_community`.`table_guild_join_request` 
WHERE (`guild_id` = (SELECT `guild_id` FROM `eod_community`.`table_guild` WHERE (`name` = 'testguild') LIMIT 1));

SELECT * FROM `eod_community`.`table_guild_join_request` WHERE (`guild_id` = 5);


-- 추천 길드 찾기
SELECT
    `guild_id`,
    `name`,
    `mark_code`,
    `description`,
    `level`,
    `experience`,
    `state`,
    `need_join_permission`,
    `master_player_id`,
    `change_name_count`,
    `change_name_datetime`,
    `change_mark_count`,
    `change_mark_datetime`,
    `change_description_count`,
    `change_description_datetime`,
    `skill_reset_count`,
    `war_count`,
    `raid_count`,
    `generate_datetime`
FROM `eod_community`.`table_guild` AS guild
INNER JOIN
(
    SELECT CEIL(RAND() * (SELECT MAX(`guild_id`) FROM `eod_community`.`table_guild`)) AS pickedId
) AS tempGuild
ON (guild.`guild_id` >= tempGuild.pickedId) AND 
   (`state` = 0)
LIMIT 500;




-- ================================================================================

-- `eod_log`

-- ================================================================================


-- ----------------------------------------
-- [`table_admission`]
-- ----------------------------------------

-- 특정 사용자의 입장권 소비 경로
SELECT * FROM `eod_log`.`table_admission` 
WHERE (`user_id` = 118) AND
      (`log_time` >= '2017-03-23 11:00:00') AND (`log_time` < '2017-03-23 11:40:00') AND
      (`consume_route` = 28)
ORDER BY `log_time` DESC;


-- ----------------------------------------
-- [`table_stage_arena_async_reward`]
-- ----------------------------------------

SELECT * FROM `eod_log`.`table_stage_arena_async_reward`
WHERE (`user_id` IN(3576, 4393, 18564, 35756)) 
ORDER BY `log_no` DESC;


SELECT * FROM `eod_log`.`table_stage_arena_async_reward`
WHERE (`log_time` > '2017-04-24 00:00:00') 
ORDER BY `log_no` DESC
LIMIT 1000;


-- ----------------------------------------
-- [`table_billing`]
-- ----------------------------------------

-- 로그 번호로 찾기
SELECT * FROM `eod_log`.`table_billing` WHERE (`log_no` = 17651);

-- 특정 시간 대역에서 플레이어의 결제 내역
SELECT * FROM `eod_log`.`table_billing` 
WHERE (`log_time` BETWEEN '2017-03-01 00:00:00' AND '2017-06-14 23:59:59') AND
      (`user_id` = 10000000000043813)
ORDER BY `log_no` DESC;

-- 영수증 번호로 찾기
SELECT * FROM `eod_log`.`table_billing` WHERE (`order_number` = 'GPA.3334-5933-8117-03767');


SELECT * FROM `eod_log`.`table_billing` WHERE (`log_time` >= '2017-03-21 00:00:00') ORDER BY `log_time` DESC;

SELECT * FROM `eod_log`.`table_billing` WHERE (`character_id` = 50);
SELECT * FROM `eod_log`.`table_billing` WHERE (`user_id` = 11) AND (`log_time` >= '2017-03-29 00:00:00');
SELECT * FROM `eod_log`.`table_billing` WHERE (`user_id` = 5065);

SELECT * FROM `eod_log`.`table_billing` WHERE (`character_id` = 15625) ORDER BY `log_time` DESC;
SELECT * FROM `eod_log`.`table_billing` 
WHERE (`log_time` >= '2017-03-10 00:00:00')
ORDER BY `log_time` DESC;


-- ----------------------------------------
-- [`table_equipment`]
-- ----------------------------------------

SELECT MAX(`log_no`) FROM `eod_log`.`table_equipment`;

SELECT * FROM `eod_log`.`table_equipment` WHERE (`log_time` >= '2016-10-31 21:40:00');

-- 연금술 진행한 로그
SELECT * FROM `eod_log`.`table_equipment` WHERE (`log_time` >= '2017-06-15 00:00:00') AND (`consume_route` BETWEEN 68 AND 70);

SELECT * FROM `eod_log`.`table_equipment` 
WHERE (`log_time` BETWEEN '2017-06-15 00:00:00' AND '2017-06-16 23:59:59') AND 
      (`consume_route` BETWEEN 68 AND 70);

SELECT * FROM `eod_log`.`table_equipment` 
WHERE (`user_id` = 10000000000008523) AND 
      (`log_time` > '2017-05-25 10:51:00') AND 
      (`item_id` IN (10000000000853990,
10000000000854008,
10000000000854056,
10000000000854083,
10000000000854098,
10000000000854122,
10000000000854150));


-- 특정 사용자의 아이템 획득 경로
SELECT * FROM `eod_log`.`table_equipment` 
WHERE (`user_id` = 17217) AND
      (`log_time` BETWEEN '2017-04-05 10:49:00' AND '2017-04-05 10:49:30');

-- 특정 캐릭터의 아이템 획득 경로
SELECT * FROM `eod_log`.`table_equipment` 
WHERE (`character_id` = 29727) AND 
      (`get_route` = 4) AND 
      (`log_time` >= '2017-03-14 00:00:00');


-- ----------------------------------------
-- [`table_gem`]
-- ----------------------------------------

-- 특정 플레이어의 젬 획득 내역
SELECT * FROM `eod_log`.`table_gem` 
WHERE (`user_id` = 10000000000043813) AND
      (`log_time` BETWEEN '2017-06-11 00:00:00' AND '2017-06-12 23:39:59')
ORDER BY `log_no` DESC;

SELECT 
    `log_no`,
    `log_time`,
    `user_id`,
    `character_id`,
    `amount`
FROM `eod_log`.`table_gem` 
WHERE (`log_time` BETWEEN '2017-03-07 00:00:00' AND '2017-03-20 18:39:59') AND
      (`get_route` = 50)
ORDER BY `log_no` DESC;


-- 특정 플레이어의 젬 획득 내역
SELECT * FROM `eod_log`.`table_gem` 
WHERE (`user_id` = 157) AND 
      (`log_time` >= '2017-06-08 00:00:00') AND (`log_time` <= '2017-06-08 19:39:59')
ORDER BY `log_no` DESC;


-- 특정 플레이어의 젬 소모 내역
SELECT * FROM `eod_log`.`table_gem` 
WHERE (`user_id` = 21) AND 
      (`log_time` >= '2017-04-10 00:00:00') AND (`log_time` <= '2017-04-11 23:59:59') AND
      (`consume_route` = 30);
      
      
SELECT * FROM `eod_log`.`table_gem` 
WHERE (`log_time` >= '2017-03-28 14:00:00') AND (`log_time` <= '2017-03-29 23:59:59') AND
      (`consume_route` = 2) AND 
      (`amount` = 100)
ORDER BY `log_no` DESC;


-- ----------------------------------------
-- [`table_item`]
-- ----------------------------------------

-- 특정 플레이어의 재화 획득 내역
SELECT * FROM `eod_log`.`table_gem` 
WHERE (`user_id` = 3) AND 
      (`log_time` BETWEEN '2017-04-14 13:00:00' AND '2017-04-14 23:59:59') AND
      (`get_route` = 50)
ORDER BY `log_no` DESC;


SELECT * FROM `eod_log`.`table_item` 
WHERE (`user_id` = 41885) AND 
      (`log_time` BETWEEN '2017-05-13 00:00:00' AND '2017-05-14 23:59:59') AND
      (`general_type_code` = 160004005)
ORDER BY `log_no` DESC;



-- ----------------------------------------
-- [`table_weekly_mission`]
-- ----------------------------------------

SELECT * FROM `eod_log`.`table_weekly_mission`
WHERE (`user_id` = 31475) AND 
      (`log_time` BETWEEN '2017-03-07 00:00:00' AND '2017-03-30 23:59:59') AND
      (`general_type_code` = 702111001) AND (`get_reward` = 1)
ORDER BY `log_no` DESC;


SELECT * FROM `eod_log`.`table_monthly_mission`
WHERE (`user_id` = 31475) AND 
      (`log_time` BETWEEN '2017-03-07 00:00:00' AND '2017-03-30 23:59:59')
ORDER BY `log_no` DESC;



-- ----------------------------------------
-- [`table_stage_arena_async`]
-- ----------------------------------------
SELECT * FROM `eod_log`.`table_stage_arena_async`
WHERE (`user_id` = 10000000000037947) AND
      (`log_time` BETWEEN '2017-06-12 00:00:00' AND '2017-06-12 23:59:59');
      
      
-- ----------------------------------------
-- [`table_stage_weekly`]
-- ----------------------------------------
SELECT * FROM `eod_log`.`table_stage_weekly`
WHERE (`user_id` = 33665) AND
      (`log_time` BETWEEN '2017-06-19 00:00:00' AND '2017-06-19 23:59:59');




-- ================================================================================

--  Miscellaneous

-- ================================================================================

SELECT
	`guild_id`,
	`name`,
	`mark_code`,
	`level`,
	`experience`,
	`state`,
	`need_join_permission`,
	`master_player_id`,
	`change_name_count`,
	`change_name_datetime`,
	`change_mark_count`,
	`change_mark_datetime`,
	`skill_reset_count`,
	`war_count`,
	`raid_count`,
	`generate_datetime`,
	`update_datetime`
FROM 
	`eod_community`.`table_guild` AS guild 
	INNER JOIN
		(SELECT 
			CEIL(RAND() * (SELECT MAX(`guild_id`) FROM `eod_community`.`table_guild`))
		AS guildId)
	AS randomGuild
WHERE (guild.guildId >= randomGuild.guildId)
ORDER BY guild.guildId ASC
LIMIT 1



SELECT 
    `character_id`, 
    `owner_user_id`,
    `character_class_type`, 
    COUNT(`owner_user_id`),
    COUNT(`character_class_type`)
FROM
    `eod_game`.`table_player_character`
GROUP BY `owner_user_id`, `character_id`
HAVING (COUNT(`character_class_type`) > 1);





select `owner_user_id`, `character_class_type`, MIN(`character_id`), count(`character_class_type`)
  from table_player_character
 group by `owner_user_id`, `character_class_type`
having count(`character_class_type`) > 1;

explain drop table `temp_character_id`;
explain create table `temp_character_id`
( `character_id` bigint(20) unsigned not null )


explain insert into `temp_character_id`
select t2.character_id
from 
   (select 
       `owner_user_id`, 
       `character_class_type`, 
       min(`character_id`) as min_character_id, 
       count(`character_class_type`)
    from `table_player_character`
	group by 
       `owner_user_id`, 
       `character_class_type`
	having (count(`character_class_type`) > 1)) AS t1
    inner join table_player_character t2
    on (t2.owner_user_id = t1.owner_user_id) and 
       (t2.character_id != t1.min_character_id);
   

explain delete from table_player_character
where character_id in (select character_id from temp_character_id);



SELECT 
    `joined_guild_id`,
    `joined_guild_name`,
    `guild_membership`,
    COUNT(`guild_membership`) AS cotable_playerunt
FROM `eod_game`.`table_player`
GROUP BY 
    `joined_guild_id`,
    `joined_guild_name`,
    `guild_membership`
HAVING 
    (`guild_membership` = 5) AND
    (COUNT(`guild_membership`) > 1);
    
    
    
SELECT
    `guild_id`,
    `membership`,
    COUNT(`membership`) AS count
FROM `eod_community`.`table_guild_membership`
GROUP BY
    `guild_id`,
    `membership`
HAVING 
    (`membership` = 5) AND
    (COUNT(`membership`) > 1);
    

EXPLAIN SELECT * FROM `eod_game`.`table_player`
WHERE (`user_id` NOT IN (1)) AND (`guardian_level` BETWEEN 100 AND 2000);


-- 대전 상태 탐색
SELECT *
FROM `eod_game`.`table_player_character` AS characterTable
    INNER JOIN
        (SELECT * FROM `eod_game`.`table_player` AS playerTable
         WHERE (`guardian_level` BETWEEN 0 AND 2000)) AS selectedCharacter
        ON (`user_id` != 12)
    INNER JOIN 
        (SELECT CEIL(((SELECT COUNT(`character_id`) 
					   FROM `eod_game`.`table_player_character`) - 1) * RAND()) AS pickedId)
		 AS pickedCharacter
    	ON (`character_id` >= pickedCharacter.pickedId) AND (`owner_user_id` != 12)
WHERE (`level` BETWEEN 1 AND 50)
ORDER BY `character_id` 
LIMIT 1;


SELECT CEIL(((SELECT COUNT(`character_id`) 
			  FROM `eod_game`.`table_player_character`) - 1) * RAND());
              
SELECT * FROM `eod_game`.`table_player` AS playerTable
WHERE (`guardian_level` BETWEEN 0 AND 2000) AND 
      (`user_id` != 12);


SELECT * FROM `eod_authentication`.`table_account_relationship` WHERE (`user_id` = 0);


-- 조력자의 등급 값들을 1씩 올리기(enum 변경 때문에...)
DESCRIBE UPDATE `eod_game`.`table_servant`
SET
    `grade` = `grade` + 1;
    



SELECT * FROM `eod_game`.`table_guardian_stone` WHERE (`enhance_level` > 150);

UPDATE `eod_game`.`table_guardian_stone`
SET
    `enhance_level` = `enhance_level` - (`enhance_level` - 200)
WHERE (`enhance_level` > 200);




SELECT * FROM `eod_game`.`table_player` WHERE (`guardian_level` = 1);
UPDATE `eod_game`.`table_player` 
SET
    `guardian_level` = 1
WHERE (`guardian_level` = 0);



-- --------------------------------------------------------------------------------
-- 신규 캐릭터를 생성하였음에도 불구하고, 스킬 데이터가 하나도 생성이 안 된 캐릭터들의 목록
-- --------------------------------------------------------------------------------

SELECT *
FROM `eod_game`.`table_player_character` AS T1
WHERE (`level` <= 1)
GROUP BY 
    `character_id`
HAVING
    ((SELECT count(1) FROM `eod_game`.`table_skill` WHERE (`character_id` = T1.`character_id`)) <= 0)
LIMIT 1000;


-- --------------------------------------------------------------------------------
--  일반 던전과 정예 던전의 액트 기록 정보 비교
-- --------------------------------------------------------------------------------
SELECT * FROM `eod_game`.`table_stage_normal_act_record` WHERE (`character_id` = 1);
SELECT * FROM `eod_game`.`table_stage_elite_act_record` WHERE (`character_id` = 1);

#UPDATE `eod_game`.`table_stage_normal_act_record` SET `rewarded_clear_grade` = 0 WHERE (`character_id` = 1);
#UPDATE `eod_game`.`table_stage_elite_act_record` SET `rewarded_clear_grade` = 0 WHERE (`character_id` = 1);

SELECT * FROM `eod_game`.`table_stage_normal_act_record` AS table1
INNER JOIN
    (SELECT * FROM `eod_game`.`table_stage_elite_act_record`) AS table2
WHERE 
    (`table1`.`character_id` = `table2`.`character_id`) AND
    (`table1`.`act_number` = `table2`.`act_number`) AND
    (`table1`.`rewarded_clear_grade` != `table2`.`rewarded_clear_grade`)
ORDER BY `table1`.`character_id`;





-- --------------------------------------------------------------------------------
--  균열 던전 초기화한 사용자 목록
-- --------------------------------------------------------------------------------
SELECT
    `user_id`,
	`nickname`,
	selectedCharacterTable.`character_id`,
	`character_class_type`,
	`rift_reset_count`,
    `rift_reset_datetime`
FROM `eod_game`.`table_player`
INNER JOIN
(
	SELECT 
		tableCharacter.`character_id`,
		`owner_user_id`,
		`character_class_type`,
		`rift_reset_count`,
		`rift_reset_datetime`
	FROM `eod_game`.`table_player_character` AS tableCharacter
	INNER JOIN
	(
		SELECT 
			`character_id`, 
			`rift_reset_count`,
			`rift_reset_datetime`
		FROM `eod_game`.`table_stage_record`
	) AS tableStageRecord
	ON (tableCharacter.`character_id` = tableStageRecord.`character_id`) AND
	   (`rift_reset_count` > 0) AND 
   (`rift_reset_datetime` < NOW())
) AS selectedCharacterTable
ON (`user_id` = `selectedCharacterTable`.`owner_user_id`)
ORDER BY `user_id`;



-- --------------------------------------------------------------------------------
--  1.05 패치 전에 결투장 보상 받은 플레이어와 캐릭터 목록
-- --------------------------------------------------------------------------------

SELECT * FROM `temp_arena_async_reward` ORDER BY `user_id` LIMIT 1000;

SELECT 
    `player_table`.`user_id`,
    `player_table`.`nickname`
FROM `temp_arena_async_reward` AS temp_table
INNER JOIN
(
    SELECT * FROM `eod_game`.`table_player`
) AS player_table
ON (`temp_table`.`user_id` = `player_table`.`user_id`);


SELECT 
    `character_table`.`character_id`,
    `character_table`.`character_class_type`
FROM `temp_arena_async_reward` AS temp_table
INNER JOIN
(
    SELECT * FROM `eod_game`.`table_player_character`
) AS character_table
ON (`temp_table`.`character_id` = `character_table`.`character_id`);


    