Tenemos la mejor información que encontramos online. Nuestro deseo es que te sea útil y si quieres aportar algo que nos pueda ayudar a perfeccionar nuestra información hazlo libremente.
Solución:
Primero une la tabla de puntajes intercambiando el equipo local con el equipo visitante e intercambiando el conteo de goles. Esto le brinda algunos datos de origen que se agregan fácilmente y la consulta para generar la tarjeta de puntuación es algo como esto:
select
team,
count(*) played,
count(case when goalsfor > goalsagainst then 1 end) wins,
count(case when goalsagainst> goalsfor then 1 end) lost,
count(case when goalsfor = goalsagainst then 1 end) draws,
sum(goalsfor) goalsfor,
sum(goalsagainst) goalsagainst,
sum(goalsfor) - sum(goalsagainst) goal_diff,
sum(
case when goalsfor > goalsagainst then 3 else 0 end
+ case when goalsfor = goalsagainst then 1 else 0 end
) score
from (
select hometeam team, goalsfor, goalsagainst from scores
union all
select awayteam, goalsagainst, goalsfor from scores
) a
group by team
order by score desc, goal_diff desc;
// connection stuff
$sql = 'select * from matchesTable';
$result = mysql_query($sql)
$standings = array ();
$standingTemplate = array ('matches' => 0, 'wins' => 0, 'draws' => 0, 'losses' => 0, 'goalsfor' => 0, 'goalsagainst' => 0, 'goalsdiff' => 0, 'points' => 0);
while ($row = mysql_fetch_assoc($result))
handleMatch($row['hometeam'], $row['goalsfor'], $row['goalsagainst']);
handleMatch($row['awayteam'], $row['goalsfor'], $row['goalsagainst']);
print_r( usort(standings, 'comparePoints') ); // up to you to format the output as you like
function handleMatch($team, $goalsfor, $goalsagainst)
global $standings, $standingTemplate;
if ($goalsfor > $goalsagainst)
$points = 3;
$win = 1;
$draw = 0;
$loss = 0;
elsif ($goalsfor == $goalsagainst)
$points = 1;
$win = 0;
$draw = 1;
$loss = 0;
else
$points = 0
$win = 0;
$draw = 0;
$loss = 1;
if ( empty($standings[$team]) )$standing = $standingTemplate;
else $standing = $standings[$team];
$standing['matches']++;
$standing['wins'] += $win;
$standing['draws'] += $draw;
$standing['losses'] += $loss;
$standing['goalsfor'] += $goalsfor;
$standing['goalsagainst'] += $goalsagainst;
$standing['goalsdiff'] += $goalsfor - $goalsagainst;
$standing['points'] += $points;
$standings[$team] = $standing;
function comparePoints($a, $b)
if ($a['points'] == $b['points'])
if ($a['goalsdiff'] == $b['goalsdiff']) return 0;
return ($a['goalsdiff'] < $b['goalsdiff']) ? 1 : -1 ;
return ($a['points'] < $b['points']) ? 1 : -1 ;
NOTAS: No lo probé y todo, podría ser un pequeño error (algunos $ o ; desaparecido).
Recientemente tuve que hacer una versión bastante más extendida de datatable. Aunque se basa en mi propio esquema, pero puede ser útil para alguien (se basa en una respuesta anterior):
SELECT
team_id team_id,
t.name team_name,
t.country country,
count(*) matches,
SUM(scored) scored_total,
SUM(conceided) conceided_total,
count(CASE WHEN scored > conceided
THEN 1 END) wins,
count(CASE WHEN scored = conceided
THEN 1 END) draws,
count(CASE WHEN scored < conceided
THEN 1 END) lost,
sum(scored) - sum(conceided) balance,
sum(
CASE WHEN scored > conceided
THEN 3
ELSE 0 END
+ CASE WHEN scored = conceided
THEN 1
ELSE 0 END) points,
count(CASE WHEN place = 'home'
THEN 1 END) home_matches,
count(CASE WHEN place = 'home' AND scored > conceided
THEN 1 END) home_wins,
count(CASE WHEN place = 'home' AND scored = conceided
THEN 1 END) home_draws,
count(CASE WHEN place = 'home' AND scored < conceided
THEN 1 END) home_lost,
SUM(CASE WHEN place = 'home'
THEN scored
ELSE 0 END) home_scored,
SUM(CASE WHEN place = 'home'
THEN conceided
ELSE 0 END) home_conceided,
count(CASE WHEN place = 'away'
THEN 1 END) away_matches,
count(CASE WHEN place = 'away' AND scored > conceided
THEN 1 END) away_wins,
count(CASE WHEN place = 'away' AND scored = conceided
THEN 1 END) away_draws,
count(CASE WHEN place = 'away' AND scored < conceided
THEN 1 END) away_lost,
SUM(CASE WHEN place = 'away'
THEN scored
ELSE 0 END) away_scored,
SUM(CASE WHEN place = 'away'
THEN conceided
ELSE 0 END) away_conceided,
GROUP_CONCAT((CASE
WHEN scored > conceided
THEN 'W'
WHEN scored = conceided
THEN 'D'
WHEN scored < conceided
THEN 'L'
END) ORDER BY date ASC separator '') streak
FROM
(
(SELECT
hm.date date,
hm.home_team_id team_id,
hm.score_home scored,
hm.score_away conceided,
'home' place
FROM matches hm
WHERE hm.season_id = :seasonId)
UNION ALL
(SELECT
am.date date,
am.away_team_id team_id,
am.score_away scored,
am.score_home conceided,
'away' place
FROM matches am
WHERE am.season_id = :seasonId)
) m
JOIN teams t ON t.id = team_id
GROUP BY team_id
ORDER BY points DESC, balance DESC;
Te invitamos a confirmar nuestra misión poniendo un comentario y dejando una valoración te damos la bienvenida.
¡Haz clic para puntuar esta entrada!
(Votos: 0 Promedio: 0)