开发者

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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜