30기용 코드 조각

SELECT

COALESCE(json_value(`data`, '$.dex0'),json_value(`data`, '$.dex1'))as `dex1`,

COALESCE(json_value(`data`, '$.dex10'),json_value(`data`, '$.dex2')) AS `dex2`,

COALESCE(json_value(`data`, '$.dex20'),json_value(`data`, '$.dex3')) AS `dex3`,

COALESCE(json_value(`data`, '$.dex30'),json_value(`data`, '$.dex4')) AS `dex4`,

COALESCE(json_value(`data`, '$.dex40'),json_value(`data`, '$.dex5')) AS `dex5`,

FROM ng_old_generals;

 

 

ALTER TABLE `ng_old_generals` ADD COLUMN `dex1` INT NOT NULL;

ALTER TABLE `ng_old_generals` ADD COLUMN `dex2` INT NOT NULL;

ALTER TABLE `ng_old_generals` ADD COLUMN `dex3` INT NOT NULL;

ALTER TABLE `ng_old_generals` ADD COLUMN `dex4` INT NOT NULL;

ALTER TABLE `ng_old_generals` ADD COLUMN `dex5` INT NOT NULL;

 

update ng_old_generals set

dex1 = COALESCE(json_value(`data`, '$.dex0'),json_value(`data`, '$.dex1')),

dex2 = COALESCE(json_value(`data`, '$.dex10'),json_value(`data`, '$.dex2')),

dex3 = COALESCE(json_value(`data`, '$.dex20'),json_value(`data`, '$.dex3')),

dex4 = COALESCE(json_value(`data`, '$.dex30'),json_value(`data`, '$.dex4')),

dex5 = COALESCE(json_value(`data`, '$.dex40'),json_value(`data`, '$.dex5'))

 where 1;

 

ALTER TABLE `ng_old_generals` ADD COLUMN `dex_sum` INT NOT NULL;

 

DELETE FROM ng_old_generals WHERE dex_sum  < 100000

 

SELECT server_id, `owner`, `name`,

coalesce(json_value(`data`, '$.leader'),json_value(`data`, '$.leadership')) AS leadership,

coalesce(json_value(`data`, '$.power'),json_value(`data`, '$.strength')) AS strength,

json_value(`data`, '$.intel') AS intel,

FROM ng_old_generals;

 

ALTER TABLE `ng_old_generals` ADD COLUMN `leadership` INT NOT NULL,ADD COLUMN `strength` INT NOT NULL,ADD COLUMN `intel` INT NOT NULL;

 

UPDATE ng_old_generals SET

leadership = coalesce(json_value(`data`, '$.leader'),json_value(`data`, '$.leadership')) ,

strength = coalesce(json_value(`data`, '$.power'),json_value(`data`, '$.strength')),

intel = json_value(`data`, '$.intel') WHERE 1

 

SELECT server_id, NAME, json_value(`data`, '$.special2') FROM ng_old_generals;

//숫자는 어쩔 수 없나..?

 

ALTER TABLE `ng_old_generals` ADD COLUMN `special2` VARCHAR(50) NULL;

UPDATE ng_old_generals SET special2 =  json_value(`data`, '$.special2') WHERE 1;

 

update ng_old_generals set `special2` = case

when `special2` = '0' then null

when `special2` = '1' then 'che_경작' when `special2` = '2' then 'che_상재' when `special2` = '3' then 'che_발명'

when `special2` = '10' then 'che_축성' when `special2` = '11' then 'che_수비' when `special2` = '12' then 'che_통찰'

when `special2` = '20' then 'che_인덕' when `special2` = '30' then 'che_거상' when `special2` = '31' then 'che_귀모'

when `special2` = '40' then 'che_귀병' when `special2` = '41' then 'che_신산' when `special2` = '42' then 'che_환술'

when `special2` = '43' then 'che_집중' when `special2` = '44' then 'che_신중' when `special2` = '45' then 'che_반계'

when `special2` = '50' then 'che_보병' when `special2` = '51' then 'che_궁병' when `special2` = '52' then 'che_기병'

when `special2` = '53' then 'che_공성' when `special2` = '60' then 'che_돌격' when `special2` = '61' then 'che_무쌍'

when `special2` = '62' then 'che_견고' when `special2` = '63' then 'che_위압' when `special2` = '70' then 'che_저격'

when `special2` = '71' then 'che_필살' when `special2` = '72' then 'che_징병' when `special2` = '73' then 'che_의술'

when `special2` = '74' then 'che_격노' when `special2` = '75' then 'che_척사' 

else `special2`

END

where 1

 

SELECT server_id, OWNER, NAME, COUNT(*) AS cnt FROM ng_old_generals GROUP BY server_id, OWNER ORDER by cnt desc;

//세 건이라 수작업 해줬음

 

SELECT server_id, `owner`, `name`, `leadership`, `strength`, `intel`, `special2`, `dex1`,`dex2`,`dex3`,`dex4`,`dex5`,cast(json_value(`data`, '$.imgsvr') AS int) AS `imgsvr`,json_value(`data`, '$.picture') AS `picture` FROM ng_old_generals

//데이터 추출 끝!