Soccer query - How to extract these data?
I have a Soccer website, and I have a calendar of the match organized in this MySql table (called calendario) :
TEAM1 TEAM2 V1 V2 DAY ID_SEASON
team1 | team2 | 3 | 1 | day1 | 1
team3 | team4 | 1 | 2 | day1 | 1
team1 | team4 | 0 | 0 | day2 | 1
team3 | team2 | 4 | 2 | day2 | 1
team1 | team3 | 2 | 3 | day3 | 1
team4 | team2 | 0 | 1 | day3 | 1
team1 | team2 | 3 | 1 | day1 | 2
team3 | team4 | 1 | 2 | day1 | 2
team1 | team4 | 0 | 0 | day2 | 2
team3 | team2 | 4 | 2 | day2 | 2
team1 | team3 | 2 | 3 | day3 | 2
team4 | team2 | 0 | 1 | day3 | 2
and I extract my result with this (long) query :
select
squadra,
sum(punteggio) as punti,
sum(if(fatti!='-' and fatti != 's',1,0)) as gioc,
sum(if(punteggio=3,1,0)) as vt,
sum(if(punteggio=1,1,0)) as nt,
sum(if(punteggio=0 and fatti != '-' and fatti != 's' ,1,0)) as pt,
sum(if(punteggio=3 and dove = 'C',1,0)) as vc,
sum开发者_Python百科(if(punteggio=1 and dove = 'C',1,0)) as nc,
sum(if(punteggio=0 and dove = 'C' and fatti != '-' and fatti != 's',1,0)) as pc,
sum(if(punteggio=3 and dove = 'T',1,0)) as vf,
sum(if(punteggio=1 and dove = 'T',1,0)) as nf,
sum(if(punteggio=0 and dove = 'T' and fatti != '-' and fatti != 's',1,0)) as pf,
abs(sum(fatti)) as gtf,
abs(sum(subiti)) as gts,
sum(if(dove='C',fatti,0)) as gfc,
sum(if(dove='C',subiti,0)) as gsc,
sum(if(dove='T',fatti,0)) as gff,
sum(if(dove='T',subiti,0)) as gsf,
(sum(if(punteggio=1 and dove = 'C',1,0)) * -2) +
(sum(if(punteggio=0 and dove = 'C' and fatti != '-' and fatti != 's',1,0)) * -3) +
(sum(if(punteggio=3 and dove = 'T',1,0)) * 2) -
(sum(if(punteggio=0 and dove = 'T' and fatti != '-' and fatti != 's',1,0))) as mi
from (
select team1 as squadra, v1 as fatti, v2 as subiti,'C' as dove,
case
when v1 > v2 then 3
when v1 = v2 and v1 <> '-' and v1 <> 's' then 1
else 0
end as punteggio
from calendario
union all
select team2 as squadra, v2 as fatti,v1 as subiti,'T',
case
when v2 > v1 then 3
when v2 = v1 and v2 <> '-' and v2 <> 's' then 1
else 0
end as punteggio
from calendario
) as tab
group by squadra
order by punti desc
unfortunatly, this extract ALL result from that table.
In fact I need to extract only results of the season 2 (ID_SEASON=2).
How can I do it?
Try with:
select
squadra,
sum(punteggio) as punti,
sum(if(fatti!='-' and fatti != 's',1,0)) as gioc,
sum(if(punteggio=3,1,0)) as vt,
sum(if(punteggio=1,1,0)) as nt,
sum(if(punteggio=0 and fatti != '-' and fatti != 's' ,1,0)) as pt,
sum(if(punteggio=3 and dove = 'C',1,0)) as vc,
sum(if(punteggio=1 and dove = 'C',1,0)) as nc,
sum(if(punteggio=0 and dove = 'C' and fatti != '-' and fatti != 's',1,0)) as pc,
sum(if(punteggio=3 and dove = 'T',1,0)) as vf,
sum(if(punteggio=1 and dove = 'T',1,0)) as nf,
sum(if(punteggio=0 and dove = 'T' and fatti != '-' and fatti != 's',1,0)) as pf,
abs(sum(fatti)) as gtf,
abs(sum(subiti)) as gts,
sum(if(dove='C',fatti,0)) as gfc,
sum(if(dove='C',subiti,0)) as gsc,
sum(if(dove='T',fatti,0)) as gff,
sum(if(dove='T',subiti,0)) as gsf,
(sum(if(punteggio=1 and dove = 'C',1,0)) * -2) +
(sum(if(punteggio=0 and dove = 'C' and fatti != '-' and fatti != 's',1,0)) * -3) +
(sum(if(punteggio=3 and dove = 'T',1,0)) * 2) -
(sum(if(punteggio=0 and dove = 'T' and fatti != '-' and fatti != 's',1,0))) as mi
from (
( select team1 as squadra, v1 as fatti, v2 as subiti,'C' as dove,
case
when v1 > v2 then 3
when v1 = v2 and v1 <> '-' and v1 <> 's' then 1
else 0
end as punteggio
from calendario WHERE ID_SEASON = 2 )
union all
( select team2 as squadra, v2 as fatti,v1 as subiti,'T',
case
when v2 > v1 then 3
when v2 = v1 and v2 <> '-' and v2 <> 's' then 1
else 0
end as punteggio
from calendario
WHERE ID_SEASON = 2 )
) as tab
group by squadra
order by punti desc
My request:
select
squadra,
sum(punteggio) as punti,
sum(if(fatti!='-' and fatti != 's',1,0)) as gioc,
sum(if(punteggio=3,1,0)) as vt,
sum(if(punteggio=1,1,0)) as nt,
sum(if(punteggio=0 and fatti != '-' and fatti != 's' ,1,0)) as pt,
sum(if(punteggio=3 and dove = 'C',1,0)) as vc,
sum(if(punteggio=1 and dove = 'C',1,0)) as nc,
sum(if(punteggio=0 and dove = 'C' and fatti != '-' and fatti != 's',1,0)) as pc,
sum(if(punteggio=3 and dove = 'T',1,0)) as vf,
sum(if(punteggio=1 and dove = 'T',1,0)) as nf,
sum(if(punteggio=0 and dove = 'T' and fatti != '-' and fatti != 's',1,0)) as pf,
abs(sum(fatti)) as gtf,
abs(sum(subiti)) as gts,
sum(if(dove='C',fatti,0)) as gfc,
sum(if(dove='C',subiti,0)) as gsc,
sum(if(dove='T',fatti,0)) as gff,
sum(if(dove='T',subiti,0)) as gsf,
(sum(if(punteggio=1 and dove = 'C',1,0)) * -2) +
(sum(if(punteggio=0 and dove = 'C' and fatti != '-' and fatti != 's',1,0)) * -3) +
(sum(if(punteggio=3 and dove = 'T',1,0)) * 2) -
(sum(if(punteggio=0 and dove = 'T' and fatti != '-' and fatti != 's',1,0))) as mi
from (
select team1 as squadra, v1 as fatti, v2 as subiti,'C' as dove, ID_SEASON,
case
when v1 > v2 then 3
when v1 = v2 and v1 <> '-' and v1 <> 's' then 1
else 0
end as punteggio
from calendario
union all
select team2 as squadra, v2 as fatti,v1 as subiti,'T' as dove, ID_SEASON,
case
when v2 > v1 then 3
when v2 = v1 and v2 <> '-' and v2 <> 's' then 1
else 0
end as punteggio
from calendario
) as tab
where ID_SEASON = 2
group by squadra
order by punti desc
精彩评论