max, min & highest in één query
1) de grootste overwinning
2) het grootste verlies
3) de wedstrijd met de meeste doelpunten
Met dit script krijg ik alle wedstrijdn maar hoe filter ik de 1) 2) & 3) records eruit?
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
SELECT
a.game_id,
a.season_id,
s.Season_Name,
a.Game_Played,
DATE_FORMAT(a.Game_DateTime, '%d-%m-%Y') AS Game_Date_short,
DATE_FORMAT(a.Game_DateTime, '%H:%i') AS GameTime,
gt.GameType_Name,
t1.Team_ID,
t1.TeamName,
t2.Team_ID,
t2.TeamName,
a.Home_Score_FT,
a.Away_Score_FT,
a.Home_Score_FT - a.Away_Score_FT AS saldo,
a.Home_Score_FT + a.Away_Score_FT AS highstscore,
a.Home_Score_HT,
a.Away_Score_HT
FROM games a, seasons s, teams t1, teams t2, gametype gt
WHERE a.Season_ID = s.Season_ID
AND a.Home_Team_ID = t1.Team_ID
AND a.Away_Team_ID = t2.Team_ID
AND a.GameType_ID = gt.GameType_ID
AND a.Game_Played="Y"
AND a.Away_Team_ID= '".addslashes($team_id)."'
ORDER BY Saldo DESC;
a.game_id,
a.season_id,
s.Season_Name,
a.Game_Played,
DATE_FORMAT(a.Game_DateTime, '%d-%m-%Y') AS Game_Date_short,
DATE_FORMAT(a.Game_DateTime, '%H:%i') AS GameTime,
gt.GameType_Name,
t1.Team_ID,
t1.TeamName,
t2.Team_ID,
t2.TeamName,
a.Home_Score_FT,
a.Away_Score_FT,
a.Home_Score_FT - a.Away_Score_FT AS saldo,
a.Home_Score_FT + a.Away_Score_FT AS highstscore,
a.Home_Score_HT,
a.Away_Score_HT
FROM games a, seasons s, teams t1, teams t2, gametype gt
WHERE a.Season_ID = s.Season_ID
AND a.Home_Team_ID = t1.Team_ID
AND a.Away_Team_ID = t2.Team_ID
AND a.GameType_ID = gt.GameType_ID
AND a.Game_Played="Y"
AND a.Away_Team_ID= '".addslashes($team_id)."'
ORDER BY Saldo DESC;
met deze code bepaal ik de het doelsaldo en highests core
Code (php)
1
2
2
a.Home_Score_FT - a.Away_Score_FT AS saldo,
a.Home_Score_FT + a.Away_Score_FT AS highestscore,
a.Home_Score_FT + a.Away_Score_FT AS highestscore,
ps de FT staat voor Full Time
Gewijzigd op 01/01/1970 01:00:00 door Mar kla
Code (php)
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
SELECT
a.Home_Score_FT - a.Away_Score_FT AS verschil_won,
a.Away_Score_FT - a.Home_Score_FT AS verschil_lose,
a.Home_Score_FT + a.Away_Score_FT AS totaal_goals
FROM
games a
WHERE
....
a.Home_Score_FT - a.Away_Score_FT AS verschil_won,
a.Away_Score_FT - a.Home_Score_FT AS verschil_lose,
a.Home_Score_FT + a.Away_Score_FT AS totaal_goals
FROM
games a
WHERE
....
Verschil_won is het grootst bij de grootste overwinning, verschil_lose is het grootst bij het grootste verlies. Je kunt het beste deze drie dingen gebruiken in de WHERE-clauses om de juiste wedstrijden (en dus de juiste uitslagen) te krijgen.
EDIT: Ik gok dat je JOINs moet gaan gebruiken...
Gewijzigd op 01/01/1970 01:00:00 door Jesper Diovo
Maar ik zit er nu mee dat ik twijfel hoe en wara ik de juiste records kan selecteren.
a) in de Query
b) in PHP script