Log에서 데이터 추출 2

create table game1222 like game1221;
create table game1223 like game1221;
create table game1224 like game1221;
create table game1225 like game1221;
create table game1226 like game1221;

truncate game1222;
truncate game1223;
truncate game1224;
truncate game1225;
truncate game1226;
truncate game1221;

load data local infile 'C:/cs/item_cs_1221/game_item_cs/game20121221_a.txt'
into table item_cs_1221.game1221 fields terminated by ' '(charac_no,inven_type,it_id,count,amount,reason);

load data local infile 'C:/cs/item_cs_1221/game_item_cs/game20121222_a.txt'
into table item_cs_1221.game1222 fields terminated by ' '(charac_no,inven_type,it_id,count,amount,reason);

load data local infile 'C:/cs/item_cs_1221/game_item_cs/game20121223_a.txt'
into table item_cs_1221.game1223 fields terminated by ' '(charac_no,inven_type,it_id,count,amount,reason);

load data local infile 'C:/cs/item_cs_1221/game_item_cs/game20121224_a.txt'
into table item_cs_1221.game1224 fields terminated by ' '(charac_no,inven_type,it_id,count,amount,reason);

load data local infile 'C:/cs/item_cs_1221/game_item_cs/game20121225_a.txt'
into table item_cs_1221.game1225 fields terminated by ' '(charac_no,inven_type,it_id,count,amount,reason);

load data local infile 'C:/cs/item_cs_1221/game_item_cs/game20121226_a.txt'
into table item_cs_1221.game1226 fields terminated by ' '(charac_no,inven_type,it_id,count,amount,reason);

##---------------

load data local infile 'C:/cs/item_cs_1221/game_received3.txt'
into table item_cs_1221.game_received fields terminated by '\t'(postal_id, charac_no, it_id, count);

create table game_received_p1 like game_received;

truncate game_received_p1;

insert into item_cs_1221.game_received_p1(postal_id, charac_no, it_id, count)
SELECT *
FROM item_cs_1221.game_received as t1
where exists(select 1 from item_cs_1221.item_id as t2 where t1.it_id = t2.item_id);

create table game_total like game1221;
create table game_total_raw like game1221;

truncate game_total_raw;
truncate game_total;

ALTER TABLE `item_cs_1221`.`game1221`
ADD INDEX `idx01` (`charac_no` ASC, `it_id` ASC) ;

ALTER TABLE `item_cs_1221`.`game1222`
ADD INDEX `idx01` (`charac_no` ASC, `it_id` ASC) ;

ALTER TABLE `item_cs_1221`.`game1223`
ADD INDEX `idx01` (`charac_no` ASC, `it_id` ASC) ;

ALTER TABLE `item_cs_1221`.`game1224`
ADD INDEX `idx01` (`charac_no` ASC, `it_id` ASC) ;

ALTER TABLE `item_cs_1221`.`game1225`
ADD INDEX `idx01` (`charac_no` ASC, `it_id` ASC) ;

ALTER TABLE `item_cs_1221`.`game1226`
ADD INDEX `idx01` (`charac_no` ASC, `it_id` ASC) ;

insert into game_total_raw(charac_no, inven_type, it_id, count, amount, reason)
select t1.charac_no, t1.inven_type, t1.it_id, t1.count, t1.amount, t1.reason
from game1221 as t1 , game_received_p1 as t2
where t1.charac_no= t2.charac_no and t1.it_id=t2.it_id;

insert into game_total_raw(charac_no, inven_type, it_id, count, amount, reason)
select t1.charac_no, t1.inven_type, t1.it_id, t1.count, t1.amount, t1.reason
from game1222 as t1 , game_received_p1 as t2
where t1.charac_no= t2.charac_no and t1.it_id=t2.it_id;

insert into game_total_raw(charac_no, inven_type, it_id, count, amount, reason)
select t1.charac_no, t1.inven_type, t1.it_id, t1.count, t1.amount, t1.reason
from game1223 as t1 , game_received_p1 as t2
where t1.charac_no= t2.charac_no and t1.it_id=t2.it_id;

insert into game_total_raw(charac_no, inven_type, it_id, count, amount, reason)
select t1.charac_no, t1.inven_type, t1.it_id, t1.count, t1.amount, t1.reason
from game1224 as t1 , game_received_p1 as t2
where t1.charac_no= t2.charac_no and t1.it_id=t2.it_id;

insert into game_total_raw(charac_no, inven_type, it_id, count, amount, reason)
select t1.charac_no, t1.inven_type, t1.it_id, t1.count, t1.amount, t1.reason
from game1225 as t1 , game_received_p1 as t2
where t1.charac_no= t2.charac_no and t1.it_id=t2.it_id;

insert into game_total_raw(charac_no, inven_type, it_id, count, amount, reason)
select t1.charac_no, t1.inven_type, t1.it_id, t1.count, t1.amount, t1.reason
from game1226 as t1 , game_received_p1 as t2
where t1.charac_no= t2.charac_no and t1.it_id=t2.it_id;

explain
select t1.charac_no, t1.it_id, t1count, ifnull(t2count,'N/A') , (t1count - if(t2count is null , 0 , t2count)) as count
into outfile 'C:/cs/item_cs_1221/game.csv' fields terminated by ','
from
(select charac_no, it_id, sum(count) as t1count
 from game_received_p1 group by charac_no, it_id) as t1
left outer join
(select charac_no, it_id, sum(amount) as t2count
 from game_total_raw where reason=19 group by charac_no, it_id) as t2
on t1.charac_no=t2.charac_no
and
t1.it_id=t2.it_id
;

select charac_no, it_id, sum(count) from game_received_p1 group by charac_no, it_id;
select charac_no, it_id, sum(amount) from game_total_raw where reason=19 group by charac_no, it_id;

select charac_no, it_id, sum(amount), reason from game_total_raw where reason=19 group by charac_no, it_id, reason;