stand competitie uitrekenen
Pagina: « vorige 1 2 3 volgende »
Code (php)
1
2
3
2
3
SELECT tbl_wedstrijden.wedstrijdID, thuisclubID, uitclubID, doelpuntID, thuisclub_uitclub
FROM tbl_wedstrijden
LEFT JOIN tbl_doelpunten ON tbl_wedstrijden.wedstrijdID = tbl_doelpunten.wedstrijdID
FROM tbl_wedstrijden
LEFT JOIN tbl_doelpunten ON tbl_wedstrijden.wedstrijdID = tbl_doelpunten.wedstrijdID
Resultaat is een hele lijst met voor elk doelpunt een regel.
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
2
3
4
5
6
7
8
9
10
11
12
13
14
wedstrijdID thuisclubID uitclubID doelpuntID thuisclub_uitclub
00001 22 15 1 thuis
00001 22 15 2 uit
00002 16 2 3 uit
00002 16 2 4 thuis
00002 16 2 5 thuis
00002 16 2 6 thuis
00002 16 2 7 uit
00003 18 13 8 uit
00003 18 13 9 thuis
00004 14 21 NULL NULL
00005 7 3 NULL NULL
00006 8 9 NULL NULL
enz.
00001 22 15 1 thuis
00001 22 15 2 uit
00002 16 2 3 uit
00002 16 2 4 thuis
00002 16 2 5 thuis
00002 16 2 6 thuis
00002 16 2 7 uit
00003 18 13 8 uit
00003 18 13 9 thuis
00004 14 21 NULL NULL
00005 7 3 NULL NULL
00006 8 9 NULL NULL
enz.
Stuit ik gelijk al op een (toekomstig) probleem dat ik eerder al wel een beetje zag aankomen. Zoals je hierboven ziet heb ik nog maar voor de eerste 3 wedstrijden de (9) doelpunten ingevoerd. Maar ik heb alle wedstrijden van het seizoen al in de database gezet. De uitslagen van wedstrijden worden bepaald door de gemaakte doelpunten in de wedstrijd. Als ik straks de (tussen)stand in de loop van de competitie ga bepalen dan wordt het geen tussenstand maar een eindstand waarbij elke wedstrijd die nog niet gespeeld is (lees waarvoor nog geen doelpunten in de db zijn gezet) op 0-0 eindigen en dat is natuurlijk niet de bedoeling. Ik moet dus in mijn query ook een bepaalde tijdsbepaling opnemen. In mijn wedstrijden tabel staat ook een datum en tijd van de wedstrijden. Ik moet dus in mijn query kunnen aangeven dat bij de tussenstand op moment X (datum en tijd) alleen de wedstrijden worden meegenomen die voor dat moment X gespeeld zijn.
Moet ik dit doen middels een WHERE statement? En zo ja hoe?
Ik heb nu de volgende query gedraaid
Code (php)
1
2
3
4
2
3
4
SELECT tbl_wedstrijden.wedstrijdID, thuisclubID, uitclubID, doelpuntID, thuisclub_uitclub
FROM tbl_wedstrijden
LEFT JOIN tbl_doelpunten ON tbl_wedstrijden.wedstrijdID = tbl_doelpunten.wedstrijdID
WHERE datumwedstrijd < 2012-08-12
FROM tbl_wedstrijden
LEFT JOIN tbl_doelpunten ON tbl_wedstrijden.wedstrijdID = tbl_doelpunten.wedstrijdID
WHERE datumwedstrijd < 2012-08-12
maar ik krijg nul resultaten, terwijl de 1e wedstrijd op 2012-08-10 was en de 2,3,4e wedstrijd op 2012-08-11 was. Ik zou dus de eerste vier wedstrijden in mijn resultaat verwachten.
(overigens is de kolom "datumwedstrijd" uit tabel wedstrijden als type= "DATE" ingericht)
Moet ik voor stap 1B (dus het totaal aantal doelpunten voor de uitclub en thuisclub in elke wedstrijd) het statement SUM gebruiken?
Gewijzigd op 08/08/2013 11:48:15 door Jo Immanuel
Je WHERE statement zou overigens wel gewoon moeten werken, ik zie niet waarom het niet goed gaat om eerlijk te zijn. Tenzij het is omdat je quotes om een datum moet zetten:
WHERE wedstrijddatum < '2012-08-12'
Gewijzigd op 07/08/2013 14:53:22 door Erwin H
De query is nu als volgt:
Code (php)
1
2
3
4
2
3
4
SELECT tbl_wedstrijden.wedstrijdID, thuisclubID, uitclubID, doelpuntID, thuisclub_uitclub
FROM tbl_wedstrijden
LEFT JOIN tbl_doelpunten ON tbl_wedstrijden.wedstrijdID = tbl_doelpunten.wedstrijdID
WHERE datumwedstrijd > '2012-08-09' AND datumwedstrijd < '2012-08-12'
FROM tbl_wedstrijden
LEFT JOIN tbl_doelpunten ON tbl_wedstrijden.wedstrijdID = tbl_doelpunten.wedstrijdID
WHERE datumwedstrijd > '2012-08-09' AND datumwedstrijd < '2012-08-12'
En ik krijg alleen de eerste vier wedstrijden als resultaten, zoals ik had gehoopt.
PS: Over die begindatum: stel er is nog een seizoen of wedstrijden uit hetzelfde seizoen maar van een andere competitie... Wat is wijsheid wat betreft performance en onderhoudbaarheid etc.? Voor elk seizoen (of elke competitie) een aparte tabel wedstrijden maken of alles in 1 tabel gooien?
Gewijzigd op 08/08/2013 11:47:23 door Jo Immanuel
Nee, ga geen tabellen maken per onderdeel (in dit geval competitie). Je wilt in principe nooit je tabel structuur aan moeten passen als je gewoon extra data wilt invoeren. In dit geval zou je nog een extra entiteit kunnen opnemen: competitie.
Dat is dus een tabel (competitieID, naam, land, afdeling, etc etc) en bij elke wedstrijd sla je de competitieID op. Dan kan je simpel in deze query ook op competitieID selecteren.
Ik loop trouwens bij stap 1B al tegen problemen aan. Ik moet namelijk volgens mij al met virtuele kolommen gaan werken, aangezien de entiteiten (zoals jij dat zo mooi noemt) "#doelpunnten thuisclub" en "#doelpunnten thuisclub" niet bestaan.
Zoiezo moet ik volgens mij de sql-functie COUNT gaan gebruiken. Ik moet dan voor elke wedstrijd kijken (counten) hoeveel regels er zijn met vulling "thuis" en hoeveel regels er zijn met vulling "uit".
Dan krijg je zoiets?
Code (php)
1
2
3
2
3
SELECT COUNT('thuis')
FROM tbl_doelpunten
WHERE wedstrijdID = '1'
FROM tbl_doelpunten
WHERE wedstrijdID = '1'
Het eerste probleem is dat ik bij deze query als resultaat "2" krijg, terwijl dit 1 moet zijn (de thuisclub heeft immers maar 1 doelpunt gemaakt in wedstrijd 1).
Het tweede probleem is dat dit voor 1 wedstrijd is, terwijl je deze berekening (voor zowel thuis als uitdoelpunten) voor elke wedstrijd moet doen. Geen idee hoe je dat handig voor elkaar krijgt.
Het derde probleem is dat ik totaal niet weet hoe ik deze query moet combineren met de query van 1A.
Het vierde probleem is dat je volgens mij dus met virtuele kolommen moet werken om bij de 'samengevoegde' tabel straks voor elke wedstrijd de kolommen #doelpunten thuisclub en #doelpunten uitclub te krijgen.
Gewijzigd op 08/08/2013 11:47:01 door Jo Immanuel
je zult je resultaten van 1A ook nog moeten groeperen en vervolgens de COUNT uitvoeren.
Code (php)
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
SELECT tbl_wedstrijden.wedstrijdID,
thuisclubID,
uitclubID,
thuisclub_uitclub,
count(thuisclub_uitclub) as doelpunten
FROM tbl_wedstrijden
LEFT JOIN tbl_doelpunten ON tbl_wedstrijden.wedstrijdID = tbl_doelpunten.wedstrijdID
WHERE datumwedstrijd > '2012-08-09' AND datumwedstrijd < '2012-08-12'
GROUP BY tbl_wedstrijden.wedstrijdID, thuisclubID, uitclubID, thuisclub_uitclub
thuisclubID,
uitclubID,
thuisclub_uitclub,
count(thuisclub_uitclub) as doelpunten
FROM tbl_wedstrijden
LEFT JOIN tbl_doelpunten ON tbl_wedstrijden.wedstrijdID = tbl_doelpunten.wedstrijdID
WHERE datumwedstrijd > '2012-08-09' AND datumwedstrijd < '2012-08-12'
GROUP BY tbl_wedstrijden.wedstrijdID, thuisclubID, uitclubID, thuisclub_uitclub
om met thuis en uitdoelpunten te werken zul je de tbl_doelpunten twee keer moeten joinen (1x thuis en 1x uit.
Gewijzigd op 07/08/2013 15:49:39 door Jeroen Jansen
Stap 1B dan
Probeer het eerste inderdaad even voor 1 wedstrijd te doen. Wil je het later voor alle wedstrijden, dan haal je die WHERE clause weg en voeg je een GROUP BY clause toe. Ben je al bijna klaar ;-)
Het splitsen van de uit en thuis doelpunten wordt inderdaad nu even een vraag wat het beste is. Je kan het in twee queries doen (die dan later als twee afzonderlijke joins in je grotere query komen), of we kunnen proberen het met 1 te doen. Doe je het in twee, dan is het simpel, voeg een WHERE clause toe waarop je ofwel op uit, ofwel op thuis selecteert.
Doe je het in 1 query, dan denk ik.... (maar weet het ook niet zeker zonder te testen) dat je gewoon kan groeperen op de uit en thuis kolom. Dan zou je dit krijgen:
Code (php)
1
2
3
4
2
3
4
SELECT thuisclub_uitclub, COUNT(*)
FROM tbl_wedstrijden
WHERE wedstrijdID = 1
GROUP BY thuisclub_uitclub
FROM tbl_wedstrijden
WHERE wedstrijdID = 1
GROUP BY thuisclub_uitclub
Wil je dan vervolgens het voor alle wedstrijden hebben dan wordt het dit:
Code (php)
1
2
3
2
3
SELECT wedstrijdID, thuisclub_uitclub, COUNT(*)
FROM tbl_wedstrijden
GROUP BY wedstrijdID, thuisclub_uitclub
FROM tbl_wedstrijden
GROUP BY wedstrijdID, thuisclub_uitclub
Toevoeging op 07/08/2013 15:55:01:
@Jeroen
Ik zou het eerlijk gezegd niet zo doen. Wat je nu doet is eerst de join maken en daarna groeperen. Dat betekent dat je eerst de dataset gaat vergroten, voor je het gaat verkleinen. Als je andersom doet (eerst in subqueries de doelpunten uitreken en groeperen per wedstrijd en dan de join maakt) verklein je direct je dataset. Zeker bij grotere queries en grotere tabellen kan dat aanzienlijk schelen in hoe snel een query uitgevoerd kan worden.
Gewijzigd op 07/08/2013 15:55:32 door Erwin H
Ik heb nu voor 1 wedstrijd de volgende sql-query:
Code (php)
1
2
3
4
5
2
3
4
5
SELECT tbl_wedstrijden.wedstrijdID, thuisclub_uitclub, COUNT( * )
FROM tbl_wedstrijden
LEFT JOIN tbl_doelpunten ON tbl_wedstrijden.wedstrijdID = tbl_doelpunten.wedstrijdID
WHERE tbl_doelpunten.wedstrijdID = '1'
GROUP BY thuisclub_uitclub
FROM tbl_wedstrijden
LEFT JOIN tbl_doelpunten ON tbl_wedstrijden.wedstrijdID = tbl_doelpunten.wedstrijdID
WHERE tbl_doelpunten.wedstrijdID = '1'
GROUP BY thuisclub_uitclub
en die geeft het volgende resultaat:
Code (php)
1
2
3
2
3
wedstrijdID thuisclub_uitclub COUNT( * )
00001 thuis 1
00001 uit 1
00001 thuis 1
00001 uit 1
Voor alle wedstrijden (inclusief datumafbakening) heb ik nu de volgende sql-query:
Code (php)
1
2
3
4
5
6
2
3
4
5
6
SELECT tbl_wedstrijden.wedstrijdID, thuisclubID, uitclubID, thuisclub_uitclub, COUNT( * )
FROM tbl_wedstrijden
LEFT JOIN tbl_doelpunten ON tbl_wedstrijden.wedstrijdID = tbl_doelpunten.wedstrijdID
WHERE datumwedstrijd > '2012-08-09'
AND datumwedstrijd < '2012-08-12'
GROUP BY tbl_wedstrijden.wedstrijdID, thuisclub_uitclub
FROM tbl_wedstrijden
LEFT JOIN tbl_doelpunten ON tbl_wedstrijden.wedstrijdID = tbl_doelpunten.wedstrijdID
WHERE datumwedstrijd > '2012-08-09'
AND datumwedstrijd < '2012-08-12'
GROUP BY tbl_wedstrijden.wedstrijdID, thuisclub_uitclub
en die geeft als resultaat:
Code (php)
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
wedstrijdID thuisclubID uitclubID thuisclub_uitclub COUNT( * )
00001 22 15 thuis 1
00001 22 15 uit 1
00002 16 2 thuis 3
00002 16 2 uit 2
00003 18 13 thuis 1
00003 18 13 uit 1
00004 14 21 NULL 1
00001 22 15 thuis 1
00001 22 15 uit 1
00002 16 2 thuis 3
00002 16 2 uit 2
00003 18 13 thuis 1
00003 18 13 uit 1
00004 14 21 NULL 1
Ziet er in principe goed uit, wat me alleen wel opvalt is het laatste regeltje van wedstrijd 00004. Daar zijn geen doelpunten ingevoerd. En dat kan, want een wedstrijd kan op 0-0 eindigen. ik zou dan als resultaat willen zien:
Code (php)
1
2
2
00004 14 21 thuis 0
00004 14 21 uit 0
00004 14 21 uit 0
Blijkbaar heb ik daar iets niet goed gedaan bij de inrichting?
Voordat de wedstrijdpunten (en virtuele kolommen) moeten worden aangemaakt is de volgende stap denk ik middels een query het volgende resultaat te krijgen:
Code (php)
1
wedstrijdID thuisclubID uitclubID #doelpunten thuisclub #doelpunten uitclub
Dus voor elke wedstrijd(ID) 1 regel waarzowel de count van de thuis als van de uitploeg in zit? Of is dit niet nodig en kan gelijk begonnen worden met het maken van de virtuele kolommen voor de wedstrijdpunten?
Toevoeging op 07/08/2013 16:56:26:
PS Erwin
Doe ik dat nu ook? Eerst de join maken en dan groeperen? En als ik het andersom doe, komt mijn query er dan als volgt uit te zien?
Code (php)
1
2
3
2
3
SELECT wedstrijdID, thuisclub_uitclub, COUNT( * )
FROM tbl_doelpunten
GROUP BY wedstrijdID, thuisclub_uitclub
FROM tbl_doelpunten
GROUP BY wedstrijdID, thuisclub_uitclub
En zaken als wie die uit en thuisclub zijn, en de datumafbakening doe je later? (moet dan wel later want de clubID's en datum staan niet in tabel doelpunten)
PS Jeroen
Wat is het verschil tussen de manier van counten die jij gebruikt "count(thuisclub_uitclub) as doelpunten" en "count ( * )"
En waarom moet ik nou precies 2 keer joinen?
Gewijzigd op 08/08/2013 11:49:26 door Jo Immanuel
Code (php)
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
SELECT
w.wedstrijdID,
w.thuisID,
w.uitID,
COUNT(IF(d.thuisclub_uitclub = 'thuis',1,NULL)) thuisscore,
COUNT(IF(d.thuisclub_uitclub = 'uit',1,NULL)) uitscore
FROM tbl_wedstrijden w
LEFT JOIN tbl_doelpunten d ON w.wedstrijdID = d.wedstrijdID
WHERE w.datumwedstrijd BETWEEN '2012-08-09' AND '2012-08-12'
GROUP BY w.wedstrijdID, w.thuisID, w.uitID
w.wedstrijdID,
w.thuisID,
w.uitID,
COUNT(IF(d.thuisclub_uitclub = 'thuis',1,NULL)) thuisscore,
COUNT(IF(d.thuisclub_uitclub = 'uit',1,NULL)) uitscore
FROM tbl_wedstrijden w
LEFT JOIN tbl_doelpunten d ON w.wedstrijdID = d.wedstrijdID
WHERE w.datumwedstrijd BETWEEN '2012-08-09' AND '2012-08-12'
GROUP BY w.wedstrijdID, w.thuisID, w.uitID
Overigens zie ik geen voordeel om thuis/uit te gebruiken ipv clubID, dit is geen dubbele data (het is immers een ID), en je haalt daarmee de (directe) relatie tussen de clubs tabel en de doelpunten tabel weg.
Als je nu bijvoorbeeld de topscorers wilt weergeven moet je dit via de tabel wedstrijden doen.
@Erwin
Een FK is in dit geval niet mogelijk, die kan wel over meerdere kolommen, maar in dit geval zou dit op wedstrijdID-thuisID of wedstrijdID-uitID moeten zijn en dat gaat niet.
Gewijzigd op 07/08/2013 18:41:13 door Ger van Steenderen
Code (php)
1
2
3
4
5
6
2
3
4
5
6
SELECT tbl_wedstrijden.wedstrijdID, thuisclubID, uitclubID, thuisclub_uitclub, COUNT( * )
FROM tbl_wedstrijden
LEFT JOIN tbl_doelpunten ON tbl_wedstrijden.wedstrijdID = tbl_doelpunten.wedstrijdID
WHERE datumwedstrijd > '2012-08-09'
AND datumwedstrijd < '2012-08-12'
GROUP BY tbl_wedstrijden.wedstrijdID, thuisclub_uitclub
FROM tbl_wedstrijden
LEFT JOIN tbl_doelpunten ON tbl_wedstrijden.wedstrijdID = tbl_doelpunten.wedstrijdID
WHERE datumwedstrijd > '2012-08-09'
AND datumwedstrijd < '2012-08-12'
GROUP BY tbl_wedstrijden.wedstrijdID, thuisclub_uitclub
Deze query geeft de volgende resultaten:
Code (php)
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
wedstrijdID thuisclubID uitclubID thuisclub_uitclub COUNT( * )
00001 22 15 thuis 1
00001 22 15 uit 1
00002 16 2 thuis 3
00002 16 2 uit 2
00003 18 13 thuis 1
00003 18 13 uit 1
00004 14 21 NULL 1
00001 22 15 thuis 1
00001 22 15 uit 1
00002 16 2 thuis 3
00002 16 2 uit 2
00003 18 13 thuis 1
00003 18 13 uit 1
00004 14 21 NULL 1
Dat is volgens mij stap 1 (A+B).
Nu stap 2: het selecteren van 2 extra virtuele kolommen waarin de punten staan voor de uit en thuisclub (0,1 of 3).
Doel na stap 2 is denk ik dat ik na mijn query de volgende uitkomst krijg:
Code (php)
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
wedstrijdID thuisclubID uitclubID thuisclub_uitclub COUNT( * ) !#punten!
00001 22 15 thuis 1 !1!
00001 22 15 uit 1 !1!
00002 16 2 thuis 3 !3!
00002 16 2 uit 2 !0!
00003 18 13 thuis 1 !1!
00003 18 13 uit 1 !1!
00004 14 21 thuis 0 !1!
00004 14 21 uit 0 !1!
00001 22 15 thuis 1 !1!
00001 22 15 uit 1 !1!
00002 16 2 thuis 3 !3!
00002 16 2 uit 2 !0!
00003 18 13 thuis 1 !1!
00003 18 13 uit 1 !1!
00004 14 21 thuis 0 !1!
00004 14 21 uit 0 !1!
Dan moet ik in de query denk ik zoiezo ergens iets aangeven zoals in Jeroen zn eerdere query:
CASE WHEN score.score_thuis > score.score_uit THEN 3 WHEN score.score_thuis < score.score_uit THEN 0 ELSE 1 END as punten
Alleen:
1.Ik gebruik in tegenstelling tot Jeroen geen score tabel. Daardoor kan ik niet verwijzen naar (de waardes van) een kolom van een bepaalde tabel. Dat zal dan ook wel de reden zijn dat ik twee extra virtuele kolommen moet selecteren. Ik heb alleen geen idee hoe ik dat moet doen.
2. Jeroen maakt volgens mij gebruik van een dubbele joint (voor zowel de uitclub en de thuisclub). Ik ga voor de enkele denk ik.
Kan iemand mij verder helpen?
PS: @Ger
Waarom zou je de topscorers mbv tabel wedstrijden moeten uitrekenen als je kiest voor "uitclub"/"thuisclub" ipv "clubID"? Ik zie niet in waarom je dat niet (vrij eenvoudig) uit tabel doelpunten kunt halen als je spelerID meegeeft bij het doelpunt en registreert wanneer iets een eigen doelpunt is. Mis ik iets?
Gewijzigd op 08/08/2013 11:51:35 door Jo Immanuel
Quote:
Ziet er in principe goed uit, wat me alleen wel opvalt is het laatste regeltje van wedstrijd 00004. Daar zijn geen doelpunten ingevoerd. En dat kan, want een wedstrijd kan op 0-0 eindigen. ik zou dan als resultaat willen zien:
dit kun je verhelpen door de functie COALESCE. Door COALESCE(COUNT(*), 0) krijg je het aantal doelpunten en wanneer deze niet gevonden zijn krijg je 0 als resultaat.
Quote:
1.Ik gebruik in tegenstelling tot Jeroen geen score tabel. Daardoor kan ik niet verwijzen naar (de waardes van) een kolom van een bepaalde tabel. Dat zal dan ook wel de reden zijn dat ik twee extra virtuele kolommen moet selecteren. Ik heb alleen geen idee hoe ik dat moet doen.
Ik denk dat je het beste verder kunt gaan met de sql van Ger. Daarbij krijg je in 1 regel zowel de thuis als uitscore en kun je wel het CASE-statement gebruiken.
Coalesce(count(*), 0) geeft hetzelfde resultaat.
Code (php)
1
2
3
4
5
6
2
3
4
5
6
SELECT tbl_wedstrijden.wedstrijdID, thuisclubID, uitclubID, thuisclub_uitclub, COALESCE( COUNT( * ) , 0 )
FROM tbl_wedstrijden
LEFT JOIN tbl_doelpunten ON tbl_wedstrijden.wedstrijdID = tbl_doelpunten.wedstrijdID
WHERE datumwedstrijd > '2012-08-09'
AND datumwedstrijd < '2012-08-12'
GROUP BY tbl_wedstrijden.wedstrijdID, thuisclub_uitclub
FROM tbl_wedstrijden
LEFT JOIN tbl_doelpunten ON tbl_wedstrijden.wedstrijdID = tbl_doelpunten.wedstrijdID
WHERE datumwedstrijd > '2012-08-09'
AND datumwedstrijd < '2012-08-12'
GROUP BY tbl_wedstrijden.wedstrijdID, thuisclub_uitclub
Namelijk voor wedstrijd 00004 1 regel (00004 14 21 NULL 1). Ik zou graag twee regels willen, voor zowel thuis als uit (beide met waarde '0' bij count). Alleen als er niet gescoord is komt zowel de waarde "thuis" als "uit" niet voor in de doelpuntentabel bij kolom "thuisclub_uitclub". Dus kan hij hem ook nooit 2x tellen lijkt me. Hij telt hem denk ik 1 keer omdat wanneer er niet gescoord is er maar 1 regel is in de wedstrijden tabel en dus kan hij ook maar 1 keer geteld worden. Zelfde denk ik dat zal gebeuren wanneer er maar 1 keer gescoord is in een wedstrijd (uitslag is 1-0 of 0-1). Dan is in de join van de tabellen wedstrijden en doelpunten ook maar 1 regel te tellen en zal er als resultaat maar 1 regel (voor de club die gescoord heeft) teruggegeven worden.
Maar misschien is het ook helemaal niet belangrijk om 2 regels (1 voor thuis en 1 voor uit) te krijgen. Het is immers een tussenstap?
Ik ga nu nog even proberen om te kijken of ik verder kom met de reactie van Ger. ALhoewel ik gisteren alleen maar errors kreeg toen ik die probeerde te gebruiken.
Toevoeging op 08/08/2013 09:21:44:
Ok, interessant. Ik heb nu de query van Ger (iets aangepast) gebruikt en krijg nu geen errors meer, en de thuis en uitscore in 1 regel bij de resultaten:
Code (php)
1
2
3
4
5
6
2
3
4
5
6
SELECT tbl_wedstrijden.wedstrijdID, thuisclubID, uitclubID, COUNT( IF( thuisclub_uitclub = 'thuis', 1, NULL ) ) thuisscore, COUNT( IF( thuisclub_uitclub = 'uit', 1, NULL ) ) uitscore
FROM tbl_wedstrijden
LEFT JOIN tbl_doelpunten ON tbl_wedstrijden.wedstrijdID = tbl_doelpunten.wedstrijdID
WHERE datumwedstrijd > '2012-08-09'
AND datumwedstrijd < '2012-08-12'
GROUP BY tbl_wedstrijden.wedstrijdID
FROM tbl_wedstrijden
LEFT JOIN tbl_doelpunten ON tbl_wedstrijden.wedstrijdID = tbl_doelpunten.wedstrijdID
WHERE datumwedstrijd > '2012-08-09'
AND datumwedstrijd < '2012-08-12'
GROUP BY tbl_wedstrijden.wedstrijdID
Geeft als resultaat:
Code (php)
1
2
3
4
5
2
3
4
5
wedstrijdID thuisclubID uitclubID thuisscore uitscore
00001 22 15 1 1
00002 16 2 3 2
00003 18 13 1 1
00004 14 21 0 0
00001 22 15 1 1
00002 16 2 3 2
00003 18 13 1 1
00004 14 21 0 0
Precies wat ik hebben wil volgens mij. Bovendien ben ik nu van het probleem bij wedstrijden waar niet of maar 1x gescoord is af. Super dus!
Nu is de volgende stap (stap 2) dus om de punten af te leiden uit de thuisscore en de uitscore met behulp van het CASE-statement.
Gewijzigd op 08/08/2013 11:51:02 door Jo Immanuel
En je moet ook even de kolombenoemingen nalopen.
De query zou dan moeten opleveren:
Code (php)
1
2
3
4
5
2
3
4
5
wedstrijdID, thuisclubID, uitclubID, thuisscore, uitscore
0001 22 15 1 1
0002 16 2 3 0
0003 18 13 1 1
0004 14 21 0 0
0001 22 15 1 1
0002 16 2 3 0
0003 18 13 1 1
0004 14 21 0 0
Toevoeging op 08/08/2013 09:35:52:
Ik had je toevoeging gemist ;-)
Ik had het net al in mijn eigen reactie aangepast (thuisclub_uitclub bij select weggehaald) en krijg nu inderdaad hetzelfde als jouw resultaat. Ben ik heel tevreden mee.
Even een vraagje (aangezien ik weinig over het selecteren van virtuele kolommen kan vinden op internet): zijn de kolommen "thuisscore" en "uitscore" van het resultaat hierboven nu de virtuele kolommen die geselecteerd moeten worden om de punten (0,1 of 3) toe te kennen?
Doel is denk ik dat ik uiteindelijk nog weer twee extra kolommen in het resultaat vanmijn query moet krijgen, als volgt:
Code (php)
1
2
3
4
5
2
3
4
5
wedstrijdID thuisclubID uitclubID thuisscore uitscore thuispunten uitpunten
00001 22 15 1 1 1 1
00002 16 2 3 2 3 0
00003 18 13 1 1 1 1
00004 14 21 0 0 1 1
00001 22 15 1 1 1 1
00002 16 2 3 2 3 0
00003 18 13 1 1 1 1
00004 14 21 0 0 1 1
Zoiets als dit werkt niet:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
2
3
4
5
6
7
8
9
10
11
12
SELECT tbl_wedstrijden.wedstrijdID,
thuisclubID,
uitclubID,
COUNT( IF( thuisclub_uitclub = 'thuis', 1, NULL ) ) thuisscore,
COUNT( IF( thuisclub_uitclub = 'uit', 1, NULL ) ) uitscore,
CASE WHEN thuisscore > uitscore THEN 3 WHEN thuisscore < uitscore THEN 0 ELSE 1 END as thuispunten,
CASE WHEN thuisscore < uitscore THEN 3 WHEN thuisscore > uitscore THEN 0 ELSE 1 END as uitpunten
FROM tbl_wedstrijden
LEFT JOIN tbl_doelpunten ON tbl_wedstrijden.wedstrijdID = tbl_doelpunten.wedstrijdID
WHERE datumwedstrijd > '2012-08-09'
AND datumwedstrijd < '2012-08-12'
GROUP BY tbl_wedstrijden.wedstrijdID
thuisclubID,
uitclubID,
COUNT( IF( thuisclub_uitclub = 'thuis', 1, NULL ) ) thuisscore,
COUNT( IF( thuisclub_uitclub = 'uit', 1, NULL ) ) uitscore,
CASE WHEN thuisscore > uitscore THEN 3 WHEN thuisscore < uitscore THEN 0 ELSE 1 END as thuispunten,
CASE WHEN thuisscore < uitscore THEN 3 WHEN thuisscore > uitscore THEN 0 ELSE 1 END as uitpunten
FROM tbl_wedstrijden
LEFT JOIN tbl_doelpunten ON tbl_wedstrijden.wedstrijdID = tbl_doelpunten.wedstrijdID
WHERE datumwedstrijd > '2012-08-09'
AND datumwedstrijd < '2012-08-12'
GROUP BY tbl_wedstrijden.wedstrijdID
Want dan krijg ik volgende error "#1054 - Unknown column 'thuisscore' in 'field list'".
Ik heb net dus twee virtuele(?) kolommen gemaakt ("thuisscore" en "uitscore") en (de waardes van) die twee virtuele kolommen die moet ik gebruiken om weer 2 nieuwe virtuele kolommen te maken ("thuispunten" en "uitpunten").
Hoe pak ik dat aan/Hoe verwijs ik naar de kolommen thuisscore en uitscore?
PS: hoe plak ik codes en resultaten wat netter (zoals jullie doen) in mijn posts hier op het phphulp.nl forum?
Gewijzigd op 08/08/2013 11:50:22 door Jo Immanuel
Het is beter om dat in de bovenliggende query te doen. Je kan namelijk de waarde van een virtuele kolom niet gebruiken in dezelfde select, je moet elke keer dat je die waarde nodig hebt hem opnieuw bepalen.
Het makkelijkste is om dat stukje query als subquery in te bouwen in de query van Jeroen
Gewijzigd op 08/08/2013 11:10:05 door Ger van Steenderen
Bouw ik de query dan als volgt op?
Code (php)
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
SELECT tbl_wedstrijden.wedstrijdID,
thuisclubID,
uitclubID,
COUNT( IF( thuisclub_uitclub = 'thuis', 1, NULL ) ) thuisscore,
COUNT( IF( thuisclub_uitclub = 'uit', 1, NULL ) ) uitscore,
FROM tbl_wedstrijden
LEFT JOIN tbl_doelpunten ON tbl_wedstrijden.wedstrijdID = tbl_doelpunten.wedstrijdID
WHERE datumwedstrijd > '2012-08-09'
AND datumwedstrijd < '2012-08-12'
GROUP BY tbl_wedstrijden.wedstrijdID
thuisclubID,
uitclubID,
COUNT( IF( thuisclub_uitclub = 'thuis', 1, NULL ) ) thuisscore,
COUNT( IF( thuisclub_uitclub = 'uit', 1, NULL ) ) uitscore,
FROM tbl_wedstrijden
LEFT JOIN tbl_doelpunten ON tbl_wedstrijden.wedstrijdID = tbl_doelpunten.wedstrijdID
WHERE datumwedstrijd > '2012-08-09'
AND datumwedstrijd < '2012-08-12'
GROUP BY tbl_wedstrijden.wedstrijdID
En waar moet ik dan precies onderstaande als subquery inbouwen? na WHERE en voor GROUP BY?
Code (php)
1
2
2
CASE WHEN thuisscore > uitscore THEN 3 WHEN thuisscore < uitscore THEN 0 ELSE 1 END as thuispunten,
CASE WHEN thuisscore < uitscore THEN 3 WHEN thuisscore > uitscore THEN 0 ELSE 1 END as uitpunten
CASE WHEN thuisscore < uitscore THEN 3 WHEN thuisscore > uitscore THEN 0 ELSE 1 END as uitpunten
Ik heb nog nooit een subquery gebruikt/gemaakt volgens mij.
Toevoeging op 09/08/2013 09:55:54:
Beste forumleden,
Ik ben er nog niet uit.
De reactie van Ger heeft me aan het twijfelen gebracht. Heb ik al het voorgaande nu voor niets gedaan en blijkt nu dat het toch niet op de manier kan waarmee ik begonnen was (alles in 1 query met gebruik van tabel doelpunten en zonder tabel score)?
Of kan het toch en kan iemand me op weg helpen met het bouwen van een subquery om van de thuisscore en uitscore (stap 1 en 2) de resulterende wedstrijdpunten voor de thuis en uitploeg (stap 3) te berekenen?
Gewijzigd op 08/08/2013 11:49:52 door Jo Immanuel
Code (php)
1
2
2
...
FROM score INNER JOIN ....
FROM score INNER JOIN ....
door:
Code (php)
1
2
3
4
5
2
3
4
5
....
FROM (
SELECT tbl_wedstrijden.wedstrijdID,
/* en de rest van de query)
) thuis
FROM (
SELECT tbl_wedstrijden.wedstrijdID,
/* en de rest van de query)
) thuis
Overigens is dat wel de makkelijkste manier, maar niet de snelste (qua prestatie).
Ik heb een soort gelijke opzet qua tabellen als Jeroen, alleen is het tellen van de punten wat eenvoudiger en daar een query zonder subqueries voor gemaakt:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT
t.team_id,
t.team_name,
SUM(IF(t.team_id = m.home_id, r.home_score, 0)) points_home,
SUM(IF(t.team_id = m.away_id, r.away_score, 0)) points_away,
COALESCE(SUM(IF(t.team_id = m.home_id, r.away_score, r.home_score)), 0) points_against,
COALESCE(SUM(IF(t.team_id = m.home_id, r.home_score, r.away_score)), 0) points_total,
COUNT(IF(t.team_id = m.home_id, 1, NULL)) played_home,
COUNT(IF(t.team_id = m.away_id, 1, NULL)) played_away,
COUNT(m.home_id) played_total
FROM
regio_teams t
JOIN
(regio_match_shedule m
JOIN
regio_match_results r
ON m.match_id = r.match_id
)
ON (t.team_id = m.home_id OR t.team_id = m.away_id)
GROUP BY t.team_id, t.team_name
ORDER BY points_total, played_total, point_against
t.team_id,
t.team_name,
SUM(IF(t.team_id = m.home_id, r.home_score, 0)) points_home,
SUM(IF(t.team_id = m.away_id, r.away_score, 0)) points_away,
COALESCE(SUM(IF(t.team_id = m.home_id, r.away_score, r.home_score)), 0) points_against,
COALESCE(SUM(IF(t.team_id = m.home_id, r.home_score, r.away_score)), 0) points_total,
COUNT(IF(t.team_id = m.home_id, 1, NULL)) played_home,
COUNT(IF(t.team_id = m.away_id, 1, NULL)) played_away,
COUNT(m.home_id) played_total
FROM
regio_teams t
JOIN
(regio_match_shedule m
JOIN
regio_match_results r
ON m.match_id = r.match_id
)
ON (t.team_id = m.home_id OR t.team_id = m.away_id)
GROUP BY t.team_id, t.team_name
ORDER BY points_total, played_total, point_against
Een explain op deze query geeft 3 records met onder rows resp 21, 111, 1
Omgebouwd naar de query van Jeroen geeft de explain 7 records 21, 222, 111, 1, 111, 1, NULL
De laatse NULL is van het Union resultaat en doet er niet toe.
Het totaal aantal rijen is het product van al die row records, maw op het eind van de competitie;
21 x 420 = 8.820 of 21 x 840 x 420 x 420 = 3.111.696.000!!!!
En daar zit het effect van de subquery voor de doelpunten niet bij, want dat gaat in de 16 cijfers lopen.
Dus zal ik vanavond bovenstaande query eens verder uitwerken op deze situatie.
Gewijzigd op 09/08/2013 10:22:22 door Ger van Steenderen
Ik ga vandaag eerst maar eens proberen om de query van Jeroen aan te passen naar mijn situatie. Al is het maar voor oefening.
Maar als ik je goed begrijp gaat die uiteindelijke query heel langzaam zijn en wil je vanavond even kijken of dat op mijn manier (dus via tabel doelpunten) sneller kan?
Dus dan gaat zo'n query een x aantal seconden duren, en dat wil je niet.
Maar dit kan sterk gereduceerd worden door de query uit mijn vorige reactie aan te passen naar jouw situatie (dus wel met de doelpunten tabel)
Het zou fantastisch zijn als je me er vanavond een beetje mee kon helpen. Want het lukt me al niet om de 'eenvoudige' manier (dus de query van jeroen aan te passen aan mijn situatie) zonder errors te draaien. Volgens mij is het berekenen van een stand ook geen beginnerstuff, want met de tutorials die ik gelezen heb kom ik er niet uit. En ook in de de boeken die ik heb houden ze het bij redelijk simpele sql-queries. Hier moet je alles combineren. Dat is andere koek...
Gewijzigd op 09/08/2013 13:00:38 door Jo Immanuel
Pagina: « vorige 1 2 3 volgende »