· 8 years ago · Apr 06, 2018, 01:24 PM
1DROP TABLE IF EXISTS `PersonsXtra`;
2
3CREATE TABLE `PersonsXtra` AS
4SELECT a.id 'personId', a.name 'personName', a.countryId 'personCountryId', b.personContinentId, a.gender,
5c.comps, j.compCountries, c.solves, c.DNFs, c.eAtt 'eventsAttempted', c.eSuc 'eventsSucceeded', c.eAve 'eventsAverage',
6IFNULL(d.podiums,0) 'podiums', IFNULL(d.gold,0) 'gold', IFNULL(d.silver,0) 'silver', IFNULL(d.bronze,0) 'bronze',
7IFNULL(d.eventPodiums,0) 'eventPodiums', IFNULL(d.eventWins,0) 'eventWins',
8IFNULL(d.wcPodium,0) 'wcPodium', IFNULL(d.wChampion,0) 'wChampion',
9IFNULL(e.records,0) 'records', IFNULL(e.WR,0) 'WR', IFNULL(e.CR,0) 'CR', IFNULL(e.NR,0) 'NR',
10g.minWorldRank,
11(CASE
12 WHEN c.eSuc = 18 AND c.eAve < 15 THEN 'Bronze'
13 WHEN c.eSuc = 18 AND c.eAve = 15 AND IFNULL(e.WR,0) = 0 AND IFNULL(e.CR,0) = 0 AND IFNULL(d.wcPodium,0) = 0 THEN 'Silver'
14 WHEN c.eSuc = 18 AND c.eAve = 15 AND (IFNULL(e.WR,0) > 0 OR IFNULL(e.CR,0) > 0 OR IFNULL(d.wcPodium,0) > 0) AND NOT (IFNULL(e.WR,0) > 0 AND IFNULL(e.CR,0) > 0 AND IFNULL(d.wcPodium,0) > 0) THEN 'Gold'
15 WHEN c.eSuc = 18 AND c.eAve = 15 AND IFNULL(e.WR,0) > 0 AND IFNULL(e.CR,0) > 0 AND IFNULL(d.wcPodium,0) > 0 THEN 'Platinum'
16 ELSE 'None'
17END) 'membership'
18FROM
19 (SELECT id, name, countryId, gender
20 FROM `Persons`
21 WHERE subid = 1) a
22JOIN
23 (SELECT id, MID(continentId,2,19) 'personContinentId'
24 FROM `Countries`) b
25ON a.countryId = b.id
26LEFT JOIN
27 (SELECT personId,
28 COUNT(DISTINCT competitionId) 'comps',
29 COUNT(DISTINCT (CASE WHEN eventId NOT IN ('333mbo','magic','mmagic') THEN eventId END)) 'eAtt',
30 COUNT(DISTINCT (CASE WHEN eventId NOT IN ('333mbo','magic','mmagic') AND best > 0 THEN eventId END)) 'eSuc',
31 COUNT(DISTINCT (CASE WHEN eventId NOT IN ('333mbo','magic','mmagic') AND average > 0 THEN eventId END)) 'eAve',
32 COUNT(CASE WHEN value1 > 0 THEN 1 END)+COUNT(CASE WHEN value2 > 0 THEN 1 END)+COUNT(CASE WHEN value3 > 0 THEN 1 END)+COUNT(CASE WHEN value4 > 0 THEN 1 END)+COUNT(CASE WHEN value5 > 0 THEN 1 END) 'solves',
33 COUNT(CASE WHEN value1 = -1 THEN 1 END)+COUNT(CASE WHEN value2 = -1 THEN 1 END)+COUNT(CASE WHEN value3 = -1 THEN 1 END)+COUNT(CASE WHEN value4 = -1 THEN 1 END)+COUNT(CASE WHEN value5 = -1 THEN 1 END) 'DNFs'
34 FROM `Results`
35 GROUP BY personId) c
36ON a.id = c.personId
37LEFT JOIN
38 (SELECT personId,
39 COUNT(*) 'podiums',
40 COUNT(CASE WHEN pos = 1 THEN 1 END) 'gold',
41 COUNT(CASE WHEN pos = 2 THEN 1 END) 'silver',
42 COUNT(CASE WHEN pos = 3 THEN 1 END) 'bronze',
43 COUNT(DISTINCT (CASE WHEN eventId NOT IN ('333mbo','magic','mmagic') THEN eventId END)) 'eventPodiums',
44 COUNT(DISTINCT (CASE WHEN eventId NOT IN ('333mbo','magic','mmagic') AND pos = 1 THEN eventId END)) 'eventWins',
45 COUNT(CASE WHEN competitionId IN (SELECT competition_id FROM `championships` WHERE championship_type = 'world') THEN 1 END) 'wcPodium',
46 COUNT(CASE WHEN competitionId IN (SELECT competition_id FROM `championships` WHERE championship_type = 'world') AND pos = 1 THEN 1 END) 'wChampion'
47 FROM `Results`
48 WHERE roundTypeId IN ('c','f') AND pos <= 3 AND best > 0 GROUP BY personId) d
49ON a.id = d.personId
50LEFT JOIN
51 (SELECT personId,
52 COUNT(CASE WHEN regionalSingleRecord != '' THEN 1 END)+COUNT(CASE WHEN regionalAverageRecord != '' THEN 1 END) 'records',
53 COUNT(CASE WHEN regionalSingleRecord = 'WR' THEN 1 END)+COUNT(CASE WHEN regionalAverageRecord = 'WR' THEN 1 END) 'WR',
54 COUNT(CASE WHEN regionalSingleRecord NOT IN ('','NR','WR') THEN 1 END)+COUNT(CASE WHEN regionalAverageRecord NOT IN ('','NR','WR') THEN 1 END) 'CR',
55 COUNT(CASE WHEN regionalSingleRecord = 'NR' THEN 1 END)+COUNT(CASE WHEN regionalAverageRecord = 'NR' THEN 1 END) 'NR'
56 FROM `Results`
57 WHERE regionalSingleRecord != '' OR regionalAverageRecord != ''
58 GROUP BY personId) e
59ON a.id = e.personId
60LEFT JOIN
61 (SELECT personId, MIN(worldRank) 'minWorldRank'
62 FROM (SELECT * FROM `RanksSingle` UNION ALL SELECT * FROM `RanksAverage`) f
63 GROUP BY personId) g
64ON a.id = g.personId
65LEFT JOIN
66 (SELECT h.personId, COUNT(DISTINCT i.countryId) 'compCountries'
67 FROM
68 (SELECT personId, competitionId
69 FROM `Results`
70 GROUP BY personId, competitionId) h
71 JOIN
72 (SELECT id, countryId
73 FROM `Competitions`) i
74 ON h.competitionId = i.id
75 WHERE countryId NOT LIKE 'X_'
76 GROUP BY personId) j
77ON a.id = j.personId