· 8 years ago · May 16, 2018, 09:26 PM
1DROP TABLE IF EXISTS all_events_rank;
2CREATE TABLE all_events_rank
3(AllEventsRank INT NOT NULL AUTO_INCREMENT,
4PRIMARY KEY(personId),
5KEY(AllEventsRank))
6SELECT
7 s.personId,
8 p.name,
9 p.countryId,
10 SUM(CASE WHEN s.eventId = '333' THEN s.best END) `333s`,
11 SUM(CASE WHEN s.eventId = '333' THEN s.worldrank END) `333sRank`,
12 SUM(CASE WHEN a.eventId = '333' THEN a.best END) `333a`,
13 SUM(CASE WHEN a.eventId = '333' THEN a.worldrank END) `333aRank`,
14 SUM(CASE WHEN s.eventId = '222' THEN s.best END) `222s`,
15 SUM(CASE WHEN s.eventId = '222' THEN s.worldrank END) `222sRank`,
16 SUM(CASE WHEN a.eventId = '222' THEN a.best END) `222a`,
17 SUM(CASE WHEN a.eventId = '222' THEN a.worldrank END) `222aRank`,
18 SUM(CASE WHEN s.eventId = '444' THEN s.best END) `444s`,
19 SUM(CASE WHEN s.eventId = '444' THEN s.worldrank END) `444sRank`,
20 SUM(CASE WHEN a.eventId = '444' THEN a.best END) `444a`,
21 SUM(CASE WHEN a.eventId = '444' THEN a.worldrank END) `444aRank`,
22 SUM(CASE WHEN s.eventId = '555' THEN s.best END) `555s`,
23 SUM(CASE WHEN s.eventId = '555' THEN s.worldrank END) `555sRank`,
24 SUM(CASE WHEN a.eventId = '555' THEN a.best END) `555a`,
25 SUM(CASE WHEN a.eventId = '555' THEN a.worldrank END) `555aRank`,
26 SUM(CASE WHEN s.eventId = '666' THEN s.best END) `666s`,
27 SUM(CASE WHEN s.eventId = '666' THEN s.worldrank END) `666sRank`,
28 SUM(CASE WHEN a.eventId = '666' THEN a.best END) `666a`,
29 SUM(CASE WHEN a.eventId = '666' THEN a.worldrank END) `666aRank`,
30 SUM(CASE WHEN s.eventId = '777' THEN s.best END) `777s`,
31 SUM(CASE WHEN s.eventId = '777' THEN s.worldrank END) `777sRank`,
32 SUM(CASE WHEN a.eventId = '777' THEN a.best END) `777a`,
33 SUM(CASE WHEN a.eventId = '777' THEN a.worldrank END) `777aRank`,
34 SUM(CASE WHEN s.eventId = '333bf' THEN s.best END) `333bfs`,
35 SUM(CASE WHEN s.eventId = '333bf' THEN s.worldrank END) `333bfsRank`,
36 SUM(CASE WHEN a.eventId = '333bf' THEN a.best END) `333bfa`,
37 SUM(CASE WHEN a.eventId = '333bf' THEN a.worldrank END) `333bfaRank`,
38 SUM(CASE WHEN s.eventId = '333fm' THEN s.best END) `333fms`,
39 SUM(CASE WHEN s.eventId = '333fm' THEN s.worldrank END) `333fmsRank`,
40 SUM(CASE WHEN a.eventId = '333fm' THEN a.best END) `333fma`,
41 SUM(CASE WHEN a.eventId = '333fm' THEN a.worldrank END) `333fmaRank`,
42 SUM(CASE WHEN s.eventId = '333oh' THEN s.best END) `333ohs`,
43 SUM(CASE WHEN s.eventId = '333oh' THEN s.worldrank END) `333ohsRank`,
44 SUM(CASE WHEN a.eventId = '333oh' THEN a.best END) `333oha`,
45 SUM(CASE WHEN a.eventId = '333oh' THEN a.worldrank END) `333ohaRank`,
46 SUM(CASE WHEN s.eventId = '333ft' THEN s.best END) `333fts`,
47 SUM(CASE WHEN s.eventId = '333ft' THEN s.worldrank END) `333ftsRank`,
48 SUM(CASE WHEN a.eventId = '333ft' THEN a.best END) `333fta`,
49 SUM(CASE WHEN a.eventId = '333ft' THEN a.worldrank END) `333ftaRank`,
50 SUM(CASE WHEN s.eventId = 'clock' THEN s.best END) `clocks`,
51 SUM(CASE WHEN s.eventId = 'clock' THEN s.worldrank END) `clocksRank`,
52 SUM(CASE WHEN a.eventId = 'clock' THEN a.best END) `clocka`,
53 SUM(CASE WHEN a.eventId = 'clock' THEN a.worldrank END) `clockaRank`,
54 SUM(CASE WHEN s.eventId = 'minx' THEN s.best END) `minxs`,
55 SUM(CASE WHEN s.eventId = 'minx' THEN s.worldrank END) `minxsRank`,
56 SUM(CASE WHEN a.eventId = 'minx' THEN a.best END) `minxa`,
57 SUM(CASE WHEN a.eventId = 'minx' THEN a.worldrank END) `minxaRank`,
58 SUM(CASE WHEN s.eventId = 'pyram' THEN s.best END) `pyrams`,
59 SUM(CASE WHEN s.eventId = 'pyram' THEN s.worldrank END) `pyramsRank`,
60 SUM(CASE WHEN a.eventId = 'pyram' THEN a.best END) `pyrama`,
61 SUM(CASE WHEN a.eventId = 'pyram' THEN a.worldrank END) `pyramaRank`,
62 SUM(CASE WHEN s.eventId = 'skewb' THEN s.best END) `skewbs`,
63 SUM(CASE WHEN s.eventId = 'skewb' THEN s.worldrank END) `skewbsRank`,
64 SUM(CASE WHEN a.eventId = 'skewb' THEN a.best END) `skewba`,
65 SUM(CASE WHEN a.eventId = 'skewb' THEN a.worldrank END) `skewbaRank`,
66 SUM(CASE WHEN s.eventId = 'sq1' THEN s.best END) `sq1s`,
67 SUM(CASE WHEN s.eventId = 'sq1' THEN s.worldrank END) `sq1sRank`,
68 SUM(CASE WHEN a.eventId = 'sq1' THEN a.best END) `sq1a`,
69 SUM(CASE WHEN a.eventId = 'sq1' THEN a.worldrank END) `sq1aRank`,
70 SUM(CASE WHEN s.eventId = '444bf' THEN s.best END) `444bfs`,
71 SUM(CASE WHEN s.eventId = '444bf' THEN s.worldrank END) `444bfsRank`,
72 SUM(CASE WHEN s.eventId = '555bf' THEN s.best END) `555bfs`,
73 SUM(CASE WHEN s.eventId = '555bf' THEN s.worldrank END) `555bfsRank`,
74 SUM(CASE WHEN s.eventId = '333mbf' THEN s.best END) `333mbfs`,
75 SUM(CASE WHEN s.eventId = '333mbf' THEN s.worldrank END) `333mbfsRank`
76FROM
77 wca_dev.rankssingle s
78LEFT JOIN
79 wca_dev.ranksaverage a
80ON
81 s.personId = a.personId
82 AND
83 s.eventId = a.eventId
84JOIN
85 wca_dev.persons p
86ON
87 s.personId = p.id
88 AND
89 p.subid = 1
90GROUP BY
91 s.personId
92ORDER BY
93 s.personId ASC
94 ;