09t3KCaX

1DROP TABLE IF EXISTS character_achievement_points_tmp;
2
3CREATE TABLE `character_achievement_points_tmp` (
4  `guid` int(11) unsigned NOT NULL,
5  `achievement` int(11) unsigned NOT NULL,
6  `points` int(11) NOT NULL default '0',
7  PRIMARY KEY  (`guid`,`achievement`)
8) ENGINE=MyISAM DEFAULT CHARSET=utf8;
9
10INSERT INTO `character_achievement_points_tmp` (`guid`, `achievement`) SELECT DISTINCT `guid`, `achievement` FROM `character_achievement`;
11
12UPDATE character_achievement_points_tmp SET points = (SELECT rewardPoints FROM dbc_achievement WHERE dbc_achievement.Id = character_achievement_points_tmp.achievement);
13
14DROP TABLE IF EXISTS character_achievement_points;
15
16CREATE TABLE `character_achievement_points` (
17  `guid` int(11) unsigned NOT NULL,
18  `name` varchar(12) NOT NULL default '',
19  `achievement_points` int(11) NOT NULL default '0',
20  PRIMARY KEY  (`guid`)
21) ENGINE=MyISAM DEFAULT CHARSET=utf8;
22
23INSERT INTO `character_achievement_points` (`guid`, `name`) SELECT DISTINCT `guid`, `name` FROM `characters`;
24
25UPDATE character_achievement_points SET achievement_points = (SELECT SUM(points) FROM character_achievement_points_tmp WHERE character_achievement_points.guid = character_achievement_points_tmp.guid);
26
27DROP TABLE IF EXISTS character_achievement_points_tmp;
28
29SELECT guid, name, achievement_points FROM character_achievement_points ORDER BY achievement_points DESC LIMIT 10;