|
Query:
select pl.id, pl.name, tm.name, pl.retired,
if(pl.own_team, pl.own_team, plch.team) as own_team,
ifnull(ch1.level, tm.head_coach) as head_coach,
ifnull(ch2.level, tm.tech_coach) as tech_coach,
ifnull(ch3.level, tm.gk_coach) as gk_coach,
ifnull(ch4.level, tm.tact_coach) as tact_coach,
ifnull(ch5.level, tm.phys_coach) as phys_coach,
if(ifnull(ch1.level, 0), ch1.player - 50929, 0) as has_head_coach,
if(ifnull(ch2.level, 0), ch2.player - 50929, 0) as has_tech_coach,
if(ifnull(ch3.level, 0), ch3.player - 50929, 0) as has_gk_coach,
if(ifnull(ch4.level, 0), ch4.player - 50929, 0) as has_tact_coach,
if(ifnull(ch5.level, 0), ch5.player - 50929, 0) as has_phys_coach,
ifnull(ch1a.wage_per_level, 30) as head_coach_wpl,
ifnull(ch2a.wage_per_level, 15) as tech_coach_wpl,
ifnull(ch3a.wage_per_level, 15) as gk_coach_wpl,
ifnull(ch4a.wage_per_level, 15) as tact_coach_wpl,
ifnull(ch5a.wage_per_level, 15) as phys_coach_wpl,
ifnull(plch.job, 0) as is_coach, if(pl.age > 29, 1, 0) as age_ok,
if(sum(if(pps.position = pl.main_position, ifnull(ps.minutes, 0), 0)) < 1500, 1, 0) as min_ok,
sum(if(pps.position = pl.main_position, ps.minutes, 0)) as minutes,
greatest(floor(greatest(least((pl.experience - 100) / 2.75 + (pl.leadership - 15) + 0.5 * (pl.discipline - 10), 44), 4) / 4) - 1, ifnull(cml1a.max_level, 0)) as hl,
greatest(if(pl.main_position = 1, 0, 1) * (floor(greatest(least((pl.experience - 100) / 4.5 + 0.5 * (pl.leadership - 10) + 0.5 * (pl.discipline - 10) +
8 * ln(1 + heading + tackling + passing + dribbling + shooting + interception) / ln(2), 60), 10) / 5) - 2), ifnull(cml2a.max_level, 0)) as tql,
greatest(if(pl.main_position = 1, 1, 0) * (floor(greatest(least((pl.experience - 100) / 3.5 + 0.5 * (pl.leadership - 10) + 0.5 * (pl.discipline - 10) +
3 * ln(1 + reflexes + one_on_ones + gk_technique + rushing_out) / ln(2), 60), 10) / 5) - 2), ifnull(cml3a.max_level, 0)) as gl,
greatest(floor(greatest(least((pl.experience - 100) / 4.5 + 0.5 * (pl.leadership - 10) + 0.5 * (pl.discipline - 10) - 15 +
8 * greatest(count(distinct pps.id) - 6, 0), 55), 5) / 5) - 1, ifnull(cml4a.max_level, 0)) as ttl,
greatest(floor(greatest(least((pl.experience - 100) / 7 + 3 * (pl.will - 15) + 3 * (pl.discipline - 15) - 1.35 * pl.start_potential + 100, 44), 4) / 4) - 1,
ifnull(cml5a.max_level, 0)) as phl,
pl.experience, pl.leadership, pl.discipline, count(distinct pps.id) as posc,
heading + tackling + passing + dribbling + shooting + interception as sum_spec
from players pl left join player_statistics ps on ps.player = pl.id and ps.tournament not in (1, 5)
left join teams tm on tm.id = 0
left join coaches cha on cha.player = pl.id
left join coach_max_levels cml1a on cml1a.coach = cha.id and cml1a.job = 1
left join coach_max_levels cml2a on cml2a.coach = cha.id and cml2a.job = 2
left join coach_max_levels cml3a on cml3a.coach = cha.id and cml3a.job = 3
left join coach_max_levels cml4a on cml4a.coach = cha.id and cml4a.job = 4
left join coach_max_levels cml5a on cml5a.coach = cha.id and cml5a.job = 5
left join coaches ch1a on ch1a.player = pl.id and ch1a.job = 1
left join coaches ch2a on ch2a.player = pl.id and ch2a.job = 2
left join coaches ch3a on ch3a.player = pl.id and ch3a.job = 3
left join coaches ch4a on ch4a.player = pl.id and ch4a.job = 4
left join coaches ch5a on ch5a.player = pl.id and ch5a.job = 5
left join coaches ch1 on ch1.team = 0 and ch1.job = 1
left join coaches ch2 on ch2.team = 0 and ch2.job = 2
left join coaches ch3 on ch3.team = 0 and ch3.job = 3
left join coaches ch4 on ch4.team = 0 and ch4.job = 4
left join coaches ch5 on ch5.team = 0 and ch5.job = 5
left join coaches plch on plch.player = pl.id and plch.team,
player_positions pps
where pps.player = pl.id and pl.id = 50929
group by pl.id
error: Out of resources when opening file './fb/coach_max_levels.MYD' (Errcode: 24) |
