Door
G Jansma
op 01-12-2015 18:58
gewijzigd op 01-12-2015 19:00
6.614 views
Hallo,
Ik heb een vraag over Mysql. Ik wil op basis van uitslagen een stand opmaken, maar ik kom er niet uit. De puntentelling is enigszins gecompliceerd, en ik weet niet of ik deze het beste in een table kan stoppen of in de query zelf.
Even een korte uiteg. De stand wordt gebaseerd op de punten behaald in het afgelopen jaar, een beetje zoals in het tennis ook gebeurd. Er zijn twee categoriën A en B, en vier niveau's wedstrijden (N1,N2,N3,N4). Daarnaast is er nog een extra moeilijkheid, namelijk dat van de wedstrijden van niveau N3 en N4 alleen de beste 5 resultaten uit het afgelopen jaar tellen.
De code zou er - voor zover mijn kennis reikt - dus ongeveer zo moeten uitzien denk ik:
SELECT * FROM speler AS s
JOIN uitslagen AS u ON s.id = u.uitslag_naam
JOIN kalender AS k ON k.categorie = u.uitslag_categorie AND k.seizoen = u.uitslag_seizoen AND k.kalender_id = u.uitslag_id
....
GROUP BY uitslag_id
AND datum BETWEEN '".$datum."' - INTERVAL 1 YEAR AND '".$datum."'
ORDER BY datum DESC
'Datum' komt uit kalender, en $datum haal ik op uit de URL met de GET-functie. Omdat elke wedstrijd maar één keer meetelt moet Group BY uitslag_id ervoor zorgen dat er van elke wedstrijd maar één resultaat is, en door middel van de Order by datum de recentste editie. Al moet de stand uiteindelijk worden 'georderd' op basis van degene met de meeste punten. Ik heb al veel gezocht ivm het rekenwerk, maar ik weet niet hoe dat moet, dus dat heb ik maar even open gelaten.
N1 - 1 van 1
N2 - 3 van 3
N3 - 5 van X (alleen vijf beste resultaten tellen mee)
N4 - 5 van X (alleen vijf beste resultaten tellen mee)
Even uit de vrije hand dus zoiets:
IF uitslag_categorie = A AND uitslag_niveau = N1 AND uitslag_positie = 1 --> 20
Ik heb ook een table 'puntentelling' gemaakt bestaande uit de kolommen categorie - niveau - p1 - p2 - p3 etc. maar ik weet niet of dat de manier is waarop dat zou moeten.
Het is inderdaad zo dat het nummeren van de rijen op de manier zoals jij het deed niet werkt in combinatie met een join, het is niet eens gegarandeerd dat het zonder join wel zou werken.
Omdat het rijnummer hier alleen gebruikt wordt om op te filteren, kan je dit toekennen in de WHERE:
SELECT
r3.uitslag_naam, r3.punten
FROM
(
SELECT u3.uitslag_naam, u3.punten
FROM uitslagen u3
JOIN kalender k3
ON k3.kalender_id = u3.uitslag_id AND k3.categorie = u3.uitslag_categorie
AND k3.seizoen = u3.uitslag_seizoen AND k3.niveau='N3'
ORDER BY u3.uitslag_naam, u3.punten DESC
) r3
CROSS JOIN (SELECT @num := 0, @naam := '') v3
WHERE @num := IF(@naam = @naam := r3.uitslag_naam, @num + 1, 1) <= 5
Als alternatief voor de afzonderlijke SET query heb ik de user variabelen geset in een cross join
Ik denk dat we ons maar even moeten beperken tot het hoogst noodzakelijke. Het gaat natuurlijk vooral om het principe hoe we een X aantal resultaten kunnen krijgen. De Join, Order By, Group By etc kan er altijd nog in, maar voor het overzicht vind ik het (en jullie vast ook) nogal verwarrend.
@Jan: Ik krijg jouw code helaas niet werkend.
@Ger: Jouw code werkt op zich, maar geeft gewoon alle resultaten, niet alleen de beste 5 voor elke speler.
$sql = "SELECT * FROM
(
SELECT u3.uitslag_naam, u3.punten
FROM uitslagen u3
WHERE u3.uitslag_categorie = 'A'
ORDER BY u3.uitslag_naam, u3.punten DESC
) r3
CROSS JOIN (SELECT @num := 0, @naam := '') v3
WHERE @num := IF(@naam = @naam := r3.uitslag_naam, @num + 1, 1) <= 5";
Ik durf het bijna niet te vragen, maar zouden jullie misschien zelf misschien kunnen testen met onderstaande sql dump? Ik heb even wat rijen gemaakt met id, categorie en punten met voor elk id 5 resultaten. Alvast bedankt!
Tabelstructuur voor tabel `test`
--
CREATE TABLE `test` (
`id` int(11) NOT NULL,
`categorie` varchar(11) NOT NULL,
`punten` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Gegevens worden geëxporteerd voor tabel `test`
--
INSERT INTO `test` (`id`, `categorie`, `punten`) VALUES
(1, 'A', 20),
(1, 'A', 18),
(1, 'A', 16),
(1, 'A', 4),
(1, 'A', 2),
(2, 'A', 14),
(2, 'A', 8),
(2, 'A', 6),
(2, 'A', 10),
(2, 'A', 12),
(3, 'A', 18),
(3, 'A', 10),
(3, 'A', 4),
(3, 'A', 20),
(3, 'A', 2);
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
Die SQL-fiddle is erg hoopgevend, maar toch lukt het me niet om de juiste output te krijgen. Ik krijg namelijk gewoon alle 5 de resultaten, in plaats van 3. Misschien doe ik iets heel doms, maar ik neem aan dat ik toch gewoon {$row['punten']} kan aanroepen?
$sql2 = "
SELECT id, punten
FROM
(SELECT id, punten,
@num := IF(@id = id, @num +1, 1) rownum,
@id := id
FROM
(SELECT id, punten
FROM test
ORDER BY id, punten DESC) t1
CROSS JOIN (SELECT @num:= 0, @id = 0) v1
) t2
WHERE rownum <= 3";
$result = mysqli_query($con, $sql2);
while($row = mysqli_fetch_array($result))
{echo "{$row['id']} {$row['punten']}<br>";}
De waarde bij rownum <= 3 bepaalt hoeveel rijen (met punten) je krijgt voor elke id (de beste 3).
Ik neem aan dat Ger 3 gebruikt heeft omdat er maar 5 resultaten per id waren in jouw voorbeeld data.
Het resultaat van Ger ga je later JOINEN met de punten van N1, N2 en N4 en sommeren per id, zoals al eerder besproken.
De waarde op regel 3 bepaalt hoeveel rijen (met punten) je krijgt voor elke id (de beste 3).
Ik neem aan dat Ger 3 gebruikt heeft omdat er maar 5 resultaten per id waren in jouw voorbeeld data.
Het resultaat van Ger ga je later JOINEN met de punten van N1, N2 en N4 en sommeren per id, zoals al eerder besproken.
Dit is meer als test bedoeld. Maar als ik daarin niet de juiste output krijg dan werkt het in een join ook niet lijkt me. Bij die SQL Fiddle geeft hij namelijk 3 resultaten per ID, maar als ik dat in een php-pagina zet dan krijg ik alle 5 de resultaten per ID.
Ik heb het aan mij kant ook geprobeerd en krijg ook alle resultaten ipv 3 per id.
Het lijkt erop alsof rownum altijd 1 is.
Misschien is het beter om je aanpak te veranderen en een aantal sql's uit te voeren en de resultaten in een standen tabel te zetten.
Voor de N3 en N4 resultaten heb je wat code nodig om per id/naam alleen de beste 5 naar de standen tabel te schrijven.
Die tabel standen kun dan gebruiken om weer te geven.
