Stand maken met Mysql
Pagina: « vorige 1 2 3 volgende »
Dat van de laatste editie is ook niet direct prioriteit.
Ik zal het nog een keer proberen: Ik wil alle punten van een speler, bv van Jan, optellen. Bestaande uit alle N1 en N2, en de beste vijf resultaten van N3 en N4. En dat alles uit het afgelopen jaar. Mocht binnen dat jaar een wedstrijd vaker dan 1x hebben plaatsgevonden dan telt alleen die laatste editie mee bij de punten van Jan (indien N1 of N2, of bij de beste resultaten van N3 en N4). De punten van een wedstrijd vervallen dus na 365 dagen, of nadat de wedstrijd weer is verreden.
Maar dat is eigenlijk voor later zorg. Ik zal eerst eens proberen om dat met die Union en N3 en N4 te fixen.
Gewijzigd op 03/12/2015 18:41:42 door G Jansma
Hoe kan ik aan jouw data zien dat wedstrijden dezelfde wedstrijden zijn, maar andere andere edities?
Gewijzigd op 03/12/2015 18:55:15 door G Jansma
En dan werken met alle wedstrijden die actief = J hebben.
Code (php)
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
$sql =
"SELECT uitslag_naam, SUM(punten) AS punt FROM
(SELECT * FROM uitslagen
WHERE uitslag_positie IN ('1','2','3')) AS u2
GROUP BY uitslag_naam
ORDER BY SUM(punten) DESC"
"SELECT uitslag_naam, SUM(punten) AS punt FROM
(SELECT * FROM uitslagen
WHERE uitslag_positie IN ('1','2','3')) AS u2
GROUP BY uitslag_naam
ORDER BY SUM(punten) DESC"
Ik bouw het even op in stapjes.
Dit is je basis query:
Code (php)
1
SELECT uitslag_naam, punten FROM uitslagen WHERE uitslag_positie IN ('1','2','3')
Deze query wil je 2 keer uitvoeren en samenvoegen:
Code (php)
1
2
3
2
3
SELECT uitslag_naam, punten FROM uitslagen WHERE uitslag_positie IN ('1','2','3')
UNION ALL
SELECT uitslag_naam, punten FROM uitslagen WHERE uitslag_positie IN ('4','5','6')
UNION ALL
SELECT uitslag_naam, punten FROM uitslagen WHERE uitslag_positie IN ('4','5','6')
En het resultaat daarvan wil je groeperen, sorteren en sommeren:
Code (php)
1
2
3
4
2
3
4
SELECT uitslag_naam, SUM(punten) AS punt FROM
(hier de subquery)
GROUP BY uitslag_naam
ORDER BY SUM(punten) DESC
(hier de subquery)
GROUP BY uitslag_naam
ORDER BY SUM(punten) DESC
Dat wordt dan dus:
Code (php)
1
2
3
4
5
6
7
2
3
4
5
6
7
SELECT t.uitslag_naam, SUM(t.punten) AS punt FROM
(SELECT uitslag_naam, punten FROM uitslagen WHERE uitslag_positie IN ('1','2','3')
UNION ALL
SELECT uitslag_naam, punten FROM uitslagen WHERE uitslag_positie IN ('4','5','6')
) as t
GROUP BY t.uitslag_naam
ORDER BY SUM(t.punten) DESC
(SELECT uitslag_naam, punten FROM uitslagen WHERE uitslag_positie IN ('1','2','3')
UNION ALL
SELECT uitslag_naam, punten FROM uitslagen WHERE uitslag_positie IN ('4','5','6')
) as t
GROUP BY t.uitslag_naam
ORDER BY SUM(t.punten) DESC
Niet getest, dus misschien moet er hier een daar nog een alias bij (AS xxxx).
Ik denk dat ik ook een oplossing heb voor je N3, N4 probleem. Zie hieronder voor het hele verhaal.
Toevoeging op 04/12/2015 09:13:28:
Het liet me niet los, maar het is niet eenvoudig.
Ik zal stap voor stap proberen uit te leggen wat ik doe.
We hebben alleen interesse in uitslagen van "actieve" wedstrijden (of je dit met een datum of een actief vlag aghandelt maakt niet uit, ik gebruik een actief vlag)
Voorbeeld voor de uitslagen van de N1 wedstrijden
Code (php)
1
2
3
2
3
SELECT u1.uitslag_spelerid, u1.uitslag_punten FROM uitslagen AS u1
JOIN kalender AS k1 ON k1.categorie = u1.uitslag_categorie AND k1.niveau = u1.uitslag_niveau AND k1.seizoen = u1.uitslag_seizoen
AND k1.categorie="N1" AND k1.actief = "J" AND k1.datum BETWEEN '".$datum."' - INTERVAL 1 YEAR AND '".$datum."
JOIN kalender AS k1 ON k1.categorie = u1.uitslag_categorie AND k1.niveau = u1.uitslag_niveau AND k1.seizoen = u1.uitslag_seizoen
AND k1.categorie="N1" AND k1.actief = "J" AND k1.datum BETWEEN '".$datum."' - INTERVAL 1 YEAR AND '".$datum."
Hier blijkt al dat het beter was geweest om niet categorie, niveau en seizoen te repliceren in uitslage, maar een link (kalenderid) van kalender naar de wedstrijd op te nemen. Dan zou het er zo uitzien:
Code (php)
1
2
3
2
3
SELECT u1.uitslag_spelerid, u1.uitslag_punten FROM uitslagen AS u1
JOIN kalender AS k1 ON k1.id = u1.kalenderid
AND k1.categorie="N1" AND k1.actief = "J" AND k1.datum BETWEEN '".$datum."' - INTERVAL 1 YEAR AND '".$datum."
JOIN kalender AS k1 ON k1.id = u1.kalenderid
AND k1.categorie="N1" AND k1.actief = "J" AND k1.datum BETWEEN '".$datum."' - INTERVAL 1 YEAR AND '".$datum."
Ditzelfde kunnen we doen voor de N2 wedstrijden.
Voor de N3 en N4 is het lastiger, want dan hebben we per speler de beste 5 resultaten nodig
We moeten eerst per speler de 5 uitslagen vinden met de hoogste punten:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
2
3
4
5
6
7
8
9
10
11
set @num := 0, @type := '';
SELECT u3.uitslag_id FROM (
SELECT uitslagen.uitslag_id, uitslagen.uitslag_spelerid, uitslagen.uitslag_punten,
@num := if(@type = TeamID, @num + 1, 1) as row_number,
@type := TeamId as dummy
FROM uitslagen
JOIN kalender AS k3 ON k3.id = uitslagen.kalender_id
AND k3.categorie="N3" AND k3.actief = "J" AND k3.datum BETWEEN '".$datum."' - INTERVAL 1 YEAR AND '".$datum."
ORDER BY uitslagen.uitslag_spelerid, uitslagen.uitslag_punten
) as u3
WHERE u3.row_number<=5
SELECT u3.uitslag_id FROM (
SELECT uitslagen.uitslag_id, uitslagen.uitslag_spelerid, uitslagen.uitslag_punten,
@num := if(@type = TeamID, @num + 1, 1) as row_number,
@type := TeamId as dummy
FROM uitslagen
JOIN kalender AS k3 ON k3.id = uitslagen.kalender_id
AND k3.categorie="N3" AND k3.actief = "J" AND k3.datum BETWEEN '".$datum."' - INTERVAL 1 YEAR AND '".$datum."
ORDER BY uitslagen.uitslag_spelerid, uitslagen.uitslag_punten
) as u3
WHERE u3.row_number<=5
In de binnen SELECT .. FROM .. JOIN .. ORDER BY voeg je velden toe om de plaats te bepalen van de punten per speler. In de buiten SELECT neem je dan alleen de rijen met row_number kleiner gelijk 5.
Dit resultaat (met de unieke uitslag_id per wedstrijd kunnen we gebruiken om net als bij N1 en N2 de uitslag_spelerid, uitslag_punten op te halen voor de uitslagen uit het resultaat.
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
set @num := 0, @type := '';
SELECT r3.uitslag_spelerid, r3,uitslag_punten FROM uitslagen AS r3
WHERE r3.uitslag_id in (
SELECT u3.uitslag_id FROM (
SELECT uitslagen.uitslag_id, uitslagen.uitslag_spelerid, uitslagen.uitslag_punten,
@num := if(@type = TeamID, @num + 1, 1) as row_number,
@type := TeamId as dummy
FROM uitslagen
JOIN kalender AS k3 ON k3.id = uitslagen.kalender_id
AND k3.categorie="N3" AND k3.actief = "J" AND k3.datum BETWEEN '".$datum."' - INTERVAL 1 YEAR AND '".$datum."
ORDER BY uitslagen.uitslag_spelerid, uitslagen.uitslag_punten
) as u3
WHERE u3.row_number<=5
)
SELECT r3.uitslag_spelerid, r3,uitslag_punten FROM uitslagen AS r3
WHERE r3.uitslag_id in (
SELECT u3.uitslag_id FROM (
SELECT uitslagen.uitslag_id, uitslagen.uitslag_spelerid, uitslagen.uitslag_punten,
@num := if(@type = TeamID, @num + 1, 1) as row_number,
@type := TeamId as dummy
FROM uitslagen
JOIN kalender AS k3 ON k3.id = uitslagen.kalender_id
AND k3.categorie="N3" AND k3.actief = "J" AND k3.datum BETWEEN '".$datum."' - INTERVAL 1 YEAR AND '".$datum."
ORDER BY uitslagen.uitslag_spelerid, uitslagen.uitslag_punten
) as u3
WHERE u3.row_number<=5
)
Dit zelfde doen we voor N4.
En dan voegen we alles samen.
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
28
29
30
31
32
33
34
35
36
37
38
39
40
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
28
29
30
31
32
33
34
35
36
37
38
39
40
set @num := 0, @type := '';
-- N1
SELECT u1.uitslag_spelerid, u1.uitslag_punten FROM uitslagen AS u1
JOIN kalender AS k1 ON k1.id = u1.kalenderid
AND k1.categorie="N1" AND k1.actief = "J" AND k1.datum BETWEEN '".$datum."' - INTERVAL 1 YEAR AND '".$datum."
-- N2
UNION ALL
SELECT u2.uitslag_spelerid, u2.uitslag_punten FROM uitslagen AS u2
JOIN kalender AS k2 ON k2.id = u2.kalenderid
AND k2.categorie="N2" AND k2.actief = "J" AND k2.datum BETWEEN '".$datum."' - INTERVAL 1 YEAR AND '".$datum."
-- N3
UNION ALL
SELECT r3.uitslag_spelerid, r3,uitslag_punten FROM uitslagen AS r3
WHERE r3.uitslag_id in (
SELECT u3.uitslag_id FROM (
SELECT uitslagen.uitslag_id, uitslagen.uitslag_spelerid, uitslagen.uitslag_punten,
@num := if(@type = TeamID, @num + 1, 1) as row_number,
@type := TeamId as dummy
FROM uitslagen
JOIN kalender AS k3 ON k3.id = uitslagen.kalender_id
AND k3.categorie="N3" AND k3.actief = "J" AND k3.datum BETWEEN '".$datum."' - INTERVAL 1 YEAR AND '".$datum."
ORDER BY uitslagen.uitslag_spelerid, uitslagen.uitslag_punten
) as u3
WHERE u3.row_number<=5
)
-- N3
UNION ALL
SELECT r4.uitslag_spelerid, r4,uitslag_punten FROM uitslagen AS r4
WHERE r4.uitslag_id in (
SELECT u4.uitslag_id FROM (
SELECT uitslagen.uitslag_id, uitslagen.uitslag_spelerid, uitslagen.uitslag_punten,
@num := if(@type = TeamID, @num + 1, 1) as row_number,
@type := TeamId as dummy
FROM uitslagen
JOIN kalender AS k4 ON k4.id = uitslagen.kalender_id
AND k4.categorie="N4" AND k4.actief = "J" AND k4.datum BETWEEN '".$datum."' - INTERVAL 1 YEAR AND '".$datum."
ORDER BY uitslagen.uitslag_spelerid, uitslagen.uitslag_punten
) as u4
WHERE u4.row_number<=5
)
-- N1
SELECT u1.uitslag_spelerid, u1.uitslag_punten FROM uitslagen AS u1
JOIN kalender AS k1 ON k1.id = u1.kalenderid
AND k1.categorie="N1" AND k1.actief = "J" AND k1.datum BETWEEN '".$datum."' - INTERVAL 1 YEAR AND '".$datum."
-- N2
UNION ALL
SELECT u2.uitslag_spelerid, u2.uitslag_punten FROM uitslagen AS u2
JOIN kalender AS k2 ON k2.id = u2.kalenderid
AND k2.categorie="N2" AND k2.actief = "J" AND k2.datum BETWEEN '".$datum."' - INTERVAL 1 YEAR AND '".$datum."
-- N3
UNION ALL
SELECT r3.uitslag_spelerid, r3,uitslag_punten FROM uitslagen AS r3
WHERE r3.uitslag_id in (
SELECT u3.uitslag_id FROM (
SELECT uitslagen.uitslag_id, uitslagen.uitslag_spelerid, uitslagen.uitslag_punten,
@num := if(@type = TeamID, @num + 1, 1) as row_number,
@type := TeamId as dummy
FROM uitslagen
JOIN kalender AS k3 ON k3.id = uitslagen.kalender_id
AND k3.categorie="N3" AND k3.actief = "J" AND k3.datum BETWEEN '".$datum."' - INTERVAL 1 YEAR AND '".$datum."
ORDER BY uitslagen.uitslag_spelerid, uitslagen.uitslag_punten
) as u3
WHERE u3.row_number<=5
)
-- N3
UNION ALL
SELECT r4.uitslag_spelerid, r4,uitslag_punten FROM uitslagen AS r4
WHERE r4.uitslag_id in (
SELECT u4.uitslag_id FROM (
SELECT uitslagen.uitslag_id, uitslagen.uitslag_spelerid, uitslagen.uitslag_punten,
@num := if(@type = TeamID, @num + 1, 1) as row_number,
@type := TeamId as dummy
FROM uitslagen
JOIN kalender AS k4 ON k4.id = uitslagen.kalender_id
AND k4.categorie="N4" AND k4.actief = "J" AND k4.datum BETWEEN '".$datum."' - INTERVAL 1 YEAR AND '".$datum."
ORDER BY uitslagen.uitslag_spelerid, uitslagen.uitslag_punten
) as u4
WHERE u4.row_number<=5
)
En over dit alles gooien we dan de SUM, GROUP BY en ORDER BY, Tevens halen we de spelersnaam op als deze niet in uitslagen zit maar in
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
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
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
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
set @num := 0, @type := '';
SELECT s.naam, SUM(t.punten) AS punt FROM
(
-- N1
SELECT u1.uitslag_spelerid, u1.uitslag_punten FROM uitslagen AS u1
JOIN kalender AS k1 ON k1.id = u1.kalenderid
AND k1.categorie="N1" AND k1.actief = "J" AND k1.datum BETWEEN '".$datum."' - INTERVAL 1 YEAR AND '".$datum."
-- N2
UNION ALL
SELECT u2.uitslag_spelerid, u2.uitslag_punten FROM uitslagen AS u2
JOIN kalender AS k2 ON k2.id = u2.kalenderid
AND k2.categorie="N2" AND k2.actief = "J" AND k2.datum BETWEEN '".$datum."' - INTERVAL 1 YEAR AND '".$datum."
-- N3
UNION ALL
SELECT r3.uitslag_spelerid, r3,uitslag_punten FROM uitslagen AS r3
WHERE r3.uitslag_id in (
SELECT u3.uitslag_id FROM (
SELECT uitslagen.uitslag_id, uitslagen.uitslag_spelerid, uitslagen.uitslag_punten,
@num := if(@type = TeamID, @num + 1, 1) as row_number,
@type := TeamId as dummy
FROM uitslagen
JOIN kalender AS k3 ON k3.id = uitslagen.kalender_id
AND k3.categorie="N3" AND k3.actief = "J" AND k3.datum BETWEEN '".$datum."' - INTERVAL 1 YEAR AND '".$datum."
ORDER BY uitslagen.uitslag_spelerid, uitslagen.uitslag_punten
) as u3
WHERE u3.row_number<=5
)
-- N4
UNION ALL
SELECT r4.uitslag_spelerid, r4,uitslag_punten FROM uitslagen AS r4
WHERE r4.uitslag_id in (
SELECT u4.uitslag_id FROM (
SELECT uitslagen.uitslag_id, uitslagen.uitslag_spelerid, uitslagen.uitslag_punten,
@num := if(@type = TeamID, @num + 1, 1) as row_number,
@type := TeamId as dummy
FROM uitslagen
JOIN kalender AS k4 ON k4.id = uitslagen.kalender_id
AND k4.categorie="N4" AND k4.actief = "J" AND k4.datum BETWEEN '".$datum."' - INTERVAL 1 YEAR AND '".$datum."
ORDER BY uitslagen.uitslag_spelerid, uitslagen.uitslag_punten
) as u4
WHERE u4.row_number<=5
) as t
)
JOIN spelers AS s ON s.id=t.spelerid
GROUP BY s.naam
ORDER BY SUM(t.punten) DESC
SELECT s.naam, SUM(t.punten) AS punt FROM
(
-- N1
SELECT u1.uitslag_spelerid, u1.uitslag_punten FROM uitslagen AS u1
JOIN kalender AS k1 ON k1.id = u1.kalenderid
AND k1.categorie="N1" AND k1.actief = "J" AND k1.datum BETWEEN '".$datum."' - INTERVAL 1 YEAR AND '".$datum."
-- N2
UNION ALL
SELECT u2.uitslag_spelerid, u2.uitslag_punten FROM uitslagen AS u2
JOIN kalender AS k2 ON k2.id = u2.kalenderid
AND k2.categorie="N2" AND k2.actief = "J" AND k2.datum BETWEEN '".$datum."' - INTERVAL 1 YEAR AND '".$datum."
-- N3
UNION ALL
SELECT r3.uitslag_spelerid, r3,uitslag_punten FROM uitslagen AS r3
WHERE r3.uitslag_id in (
SELECT u3.uitslag_id FROM (
SELECT uitslagen.uitslag_id, uitslagen.uitslag_spelerid, uitslagen.uitslag_punten,
@num := if(@type = TeamID, @num + 1, 1) as row_number,
@type := TeamId as dummy
FROM uitslagen
JOIN kalender AS k3 ON k3.id = uitslagen.kalender_id
AND k3.categorie="N3" AND k3.actief = "J" AND k3.datum BETWEEN '".$datum."' - INTERVAL 1 YEAR AND '".$datum."
ORDER BY uitslagen.uitslag_spelerid, uitslagen.uitslag_punten
) as u3
WHERE u3.row_number<=5
)
-- N4
UNION ALL
SELECT r4.uitslag_spelerid, r4,uitslag_punten FROM uitslagen AS r4
WHERE r4.uitslag_id in (
SELECT u4.uitslag_id FROM (
SELECT uitslagen.uitslag_id, uitslagen.uitslag_spelerid, uitslagen.uitslag_punten,
@num := if(@type = TeamID, @num + 1, 1) as row_number,
@type := TeamId as dummy
FROM uitslagen
JOIN kalender AS k4 ON k4.id = uitslagen.kalender_id
AND k4.categorie="N4" AND k4.actief = "J" AND k4.datum BETWEEN '".$datum."' - INTERVAL 1 YEAR AND '".$datum."
ORDER BY uitslagen.uitslag_spelerid, uitslagen.uitslag_punten
) as u4
WHERE u4.row_number<=5
) as t
)
JOIN spelers AS s ON s.id=t.spelerid
GROUP BY s.naam
ORDER BY SUM(t.punten) DESC
Dit overziend denk ik dat het makkelijker en overzichtelijker is om dit niet zo te doen, maar in het script waar je ook de punten bepaald. Dan bouw je het in stukjes op. Dat zou ik zelf tenminste doen.
Ik hoop dat het een beetje klopt. Uit de losse pols ingetikt en dus zitten er misschien hier en daar nog foutjes in. In ieder geval in de namen van tabellen en velden.
Test het uit in stukjes zoals ik het beschrijf en bouw het zo langzaam verder op.
Gewijzigd op 04/12/2015 09:19:16 door Jan de Laet
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
"SELECT t.uitslag_naam, SUM(t.punten) AS punt FROM
(SELECT uitslag_naam, punten FROM uitslagen AS u
JOIN kalender AS k ON k.kalender_id = u.uitslag_id AND k.categorie = u.uitslag_categorie AND k.seizoen = u.uitslag_seizoen
WHERE uitslag_categorie = 'A' AND niveau IN ('N1')
UNION ALL
SELECT uitslag_naam, punten FROM uitslagen AS u
JOIN kalender AS k ON k.kalender_id = u.uitslag_id AND k.categorie = u.uitslag_categorie AND k.seizoen = u.uitslag_seizoen
WHERE uitslag_categorie = 'A' AND niveau IN ('N2')
)
as t
WHERE punten > 0
GROUP BY t.uitslag_naam
ORDER BY SUM(t.punten) DESC";
(SELECT uitslag_naam, punten FROM uitslagen AS u
JOIN kalender AS k ON k.kalender_id = u.uitslag_id AND k.categorie = u.uitslag_categorie AND k.seizoen = u.uitslag_seizoen
WHERE uitslag_categorie = 'A' AND niveau IN ('N1')
UNION ALL
SELECT uitslag_naam, punten FROM uitslagen AS u
JOIN kalender AS k ON k.kalender_id = u.uitslag_id AND k.categorie = u.uitslag_categorie AND k.seizoen = u.uitslag_seizoen
WHERE uitslag_categorie = 'A' AND niveau IN ('N2')
)
as t
WHERE punten > 0
GROUP BY t.uitslag_naam
ORDER BY SUM(t.punten) DESC";
Alleen die N3 en N4 zijn dus nog niet gelukt. Ik heb even wat namen gewijzigd, want die waren niet allemaal goed. (er stond ook een , achter r3.punten). Het zou dan dit worden, ik heb Join maar even overgenomen en in jouw stijl gezet. Hoef je uitslagen niet 'aan te kondigen' dmv AS uitslagen, omdat je uitslagen. gebruikt?
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13
SELECT r3.uitslag_naam, r3.punten FROM uitslagen AS r3
WHERE r3.uitslag_id in (
SELECT u3.uitslag_id FROM (
SELECT uitslagen.uitslag_id, uitslagen.uitslag_naam, uitslagen.punten,
@num := if(@type = TeamID, @num + 1, 1) as row_number,
@type := TeamId as dummy
FROM uitslagen
JOIN kalender AS k3 ON k3.kalender_id = uitslagen.uitslag_id AND k3.categorie = uitslagen.uitslag_categorie AND k3.seizoen = uitslagen.uitslag_seizoen
AND k3.niveau='N3'
ORDER BY uitslagen.uitslag_naam, uitslagen.punten
) as u3
WHERE u3.row_number<=5
)
WHERE r3.uitslag_id in (
SELECT u3.uitslag_id FROM (
SELECT uitslagen.uitslag_id, uitslagen.uitslag_naam, uitslagen.punten,
@num := if(@type = TeamID, @num + 1, 1) as row_number,
@type := TeamId as dummy
FROM uitslagen
JOIN kalender AS k3 ON k3.kalender_id = uitslagen.uitslag_id AND k3.categorie = uitslagen.uitslag_categorie AND k3.seizoen = uitslagen.uitslag_seizoen
AND k3.niveau='N3'
ORDER BY uitslagen.uitslag_naam, uitslagen.punten
) as u3
WHERE u3.row_number<=5
)
En ik had nog een vraagje over:
Code (php)
1
set @num := 0, @type := '';
Ik begrijp dat dat vooraan moet staan in de query? Ik heb het als test vooraan gezet in de huidige query met de Union (met N1 en N2), maar dan werkt het al niet meer. Is dat logisch, of zit daar misschien iets in fout?
Ik denk dat het het beste is om eerst alleen de 5 beste resultaten van N3 werkend te krijgen en vervolgens in de Union te verwerken?
Ik denk dat de WHERE punten>0 niet hoeft want je toont alleen de SUM(punten). Of moet zijn dat je de namen met 0 punten in totaal niet wilt zien, maar dan moet je tussen GROUP BY en ORDER BY, dit plaatsen HAVING SUM(punten)>0. De HAVING is een soort WHERE maar dan op het groepsresultaat.
VWb 5 beste wedstrijden: Probeer het eens in stapjes (zie opmerking onderaan over gebruik van SET.
1. Lukt het om de uitslagen op volgorde van beste uitslag te krijgen? Ik zie nu dat er na uitslagen.punten nog DESC moet komen
Code (php)
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
SET @num := 0, @type := '';
SELECT uitslagen.uitslag_id, uitslagen.uitslag_naam, uitslagen.punten,
@num := if(@type = uitslag_naam, @num + 1, 1) as row_number,
@type := uitslag_naam as dummy
FROM uitslagen
JOIN kalender AS k3 ON k3.kalender_id = uitslagen.uitslag_id AND k3.categorie = uitslagen.uitslag_categorie
AND k3.seizoen = uitslagen.uitslag_seizoen AND k3.niveau='N3'
ORDER BY uitslagen.uitslag_naam, uitslagen.punten DESC;
SELECT uitslagen.uitslag_id, uitslagen.uitslag_naam, uitslagen.punten,
@num := if(@type = uitslag_naam, @num + 1, 1) as row_number,
@type := uitslag_naam as dummy
FROM uitslagen
JOIN kalender AS k3 ON k3.kalender_id = uitslagen.uitslag_id AND k3.categorie = uitslagen.uitslag_categorie
AND k3.seizoen = uitslagen.uitslag_seizoen AND k3.niveau='N3'
ORDER BY uitslagen.uitslag_naam, uitslagen.punten DESC;
2. Lukt het om van de beste 5, alleen de uitslag_id te krijgen
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
SET @num := 0, @type := '';
SELECT u3.uitslag_id FROM (
SELECT uitslagen.uitslag_id, uitslagen.uitslag_naam, uitslagen.punten,
@num := if(@type = uitslag_naam, @num + 1, 1) as row_number,
@type := uitslag_naam as dummy
FROM uitslagen
JOIN kalender AS k3 ON k3.kalender_id = uitslagen.uitslag_id AND k3.categorie = uitslagen.uitslag_categorie
AND k3.seizoen = uitslagen.uitslag_seizoen AND k3.niveau='N3'
ORDER BY uitslagen.uitslag_naam, uitslagen.punten DESC
) as u3
WHERE u3.row_number<=5;
SELECT u3.uitslag_id FROM (
SELECT uitslagen.uitslag_id, uitslagen.uitslag_naam, uitslagen.punten,
@num := if(@type = uitslag_naam, @num + 1, 1) as row_number,
@type := uitslag_naam as dummy
FROM uitslagen
JOIN kalender AS k3 ON k3.kalender_id = uitslagen.uitslag_id AND k3.categorie = uitslagen.uitslag_categorie
AND k3.seizoen = uitslagen.uitslag_seizoen AND k3.niveau='N3'
ORDER BY uitslagen.uitslag_naam, uitslagen.punten DESC
) as u3
WHERE u3.row_number<=5;
3. Lukt het van de beste 5 de benodigde gegevens uit uitslagen te halen
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SET @num := 0, @type := '';
SELECT r3.uitslag_naam, r3.punten FROM uitslagen AS r3
WHERE r3.uitslag_id in (
SELECT u3.uitslag_id FROM (
SELECT uitslagen.uitslag_id, uitslagen.uitslag_naam, uitslagen.punten,
@num := if(@type = uitslag_naam, @num + 1, 1) as row_number,
@type := uitslag_naam as dummy
FROM uitslagen
JOIN kalender AS k3 ON k3.kalender_id = uitslagen.uitslag_id AND k3.categorie = uitslagen.uitslag_categorie
AND k3.seizoen = uitslagen.uitslag_seizoen AND k3.niveau='N3'
ORDER BY uitslagen.uitslag_naam, uitslagen.punten DESC
) as u3
WHERE u3.row_number<=5
);
SELECT r3.uitslag_naam, r3.punten FROM uitslagen AS r3
WHERE r3.uitslag_id in (
SELECT u3.uitslag_id FROM (
SELECT uitslagen.uitslag_id, uitslagen.uitslag_naam, uitslagen.punten,
@num := if(@type = uitslag_naam, @num + 1, 1) as row_number,
@type := uitslag_naam as dummy
FROM uitslagen
JOIN kalender AS k3 ON k3.kalender_id = uitslagen.uitslag_id AND k3.categorie = uitslagen.uitslag_categorie
AND k3.seizoen = uitslagen.uitslag_seizoen AND k3.niveau='N3'
ORDER BY uitslagen.uitslag_naam, uitslagen.punten DESC
) as u3
WHERE u3.row_number<=5
);
Misschien kan het zelfs wat korter, probeer dit eens:
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
SET @num := 0, @type := '';
SELECT u3.uitslag_naam, u3.punten FROM (
SELECT uitslagen.uitslag_naam, uitslagen.punten,
@num := if(@type = uitslag_naam, @num + 1, 1) as row_number,
@type := uitslag_naam as dummy
FROM uitslagen
JOIN kalender AS k3 ON k3.kalender_id = uitslagen.uitslag_id AND k3.categorie = uitslagen.uitslag_categorie
AND k3.seizoen = uitslagen.uitslag_seizoen AND k3.niveau='N3'
ORDER BY uitslagen.uitslag_naam, uitslagen.punten DESC
) as u3
WHERE u3.row_number<=5
SELECT u3.uitslag_naam, u3.punten FROM (
SELECT uitslagen.uitslag_naam, uitslagen.punten,
@num := if(@type = uitslag_naam, @num + 1, 1) as row_number,
@type := uitslag_naam as dummy
FROM uitslagen
JOIN kalender AS k3 ON k3.kalender_id = uitslagen.uitslag_id AND k3.categorie = uitslagen.uitslag_categorie
AND k3.seizoen = uitslagen.uitslag_seizoen AND k3.niveau='N3'
ORDER BY uitslagen.uitslag_naam, uitslagen.punten DESC
) as u3
WHERE u3.row_number<=5
Vwb gebruik SET.
Ik heb zelf nog nooit gewerkt met de set. Ik heb gezocht op internet en het lijkt dat je niet 2 statements tegelijk kunt uitvoeren. Dus probeer te splitsen, eerst query met SET @num := 0, @type := ''; en dan query met de SELECT ...
Als iets niet werkt hoor ik graag de foutmelding en de sql zoals je die gebruikte.
Toevoeging op 05/12/2015 16:10:59:
Nog een vraag: in de JOIN gebruik je
JOIN kalender AS k3 ON k3.kalender_id = uitslagen.uitslag_id AND k3.categorie = uitslagen.uitslag_categorie
AND k3.seizoen = uitslagen.uitslag_seizoen AND k3.niveau='N3'
Als je al op k3.kalender_id = uitslagen.uitslag_id kun selecteren, zijn de andere velden (categorie, seizoen en niveau) dan nog wel nodig?
Gewijzigd op 05/12/2015 19:35:50 door Jan de Laet
Error description: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT * FROM uitslagen' at line 3
Code (php)
1
2
3
4
2
3
4
$sql =
"set @num := 0, @type := '';
SELECT * FROM uitslagen";
"set @num := 0, @type := '';
SELECT * FROM uitslagen";
Hoe zou dat dan eruit moeten zien met meerdere queries?
dus:
Code (php)
1
2
3
4
2
3
4
<?php
$sql1 = "set @num := 0, @type := '';"
$sql2 = "SELECT * FROM uitslagen";
?>
$sql1 = "set @num := 0, @type := '';"
$sql2 = "SELECT * FROM uitslagen";
?>
en die allebei uitvoeren met $sql1 en $sql2
NB ik heb in de post van 13:31 TeamID nog vervangen door uitslag_naam. TeamID was van mijn eigen test tabel.
Toevoeging op 06/12/2015 13:14:57:
Vergeet (bijna) alles was ik hierboven heb gezegd over N3 en N4 uitslagen. Het blijkt dat @num niet werkt als er ook een JOIN gebruikt wordt.
Dit werkt wel voor N3 en N4. Ik heb geprobeerd jouw tabellen en data na te bouwen en dan geeft dit het goede resultaat om in de UNIONs te gebruiken:
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
28
29
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
28
29
<?php
$sql = "SET @num := 0, @type := ''";
$query = $this->db->prepare($sql);
$query->execute();
$sql = "
SELECT uitslag_naam, punten FROM (
SELECT (SELECT COUNT(cu.uitslag_naam)
FROM uitslagen AS cu
JOIN kalender AS ck ON ck.kalender_id = cu.uitslag_id
AND ck.categorie = cu.uitslag_categorie
AND ck.seizoen = cu.uitslag_seizoen
AND ck.niveau = 'N3'
WHERE cu.uitslag_naam = r3.uitslag_naam AND cu.punten >= r3.punten) AS rank,
id,
uitslag_naam,
punten
FROM uitslagen AS r3
JOIN kalender AS k3 ON k3.kalender_id = r3.uitslag_id
AND k3.categorie = r3.uitslag_categorie
AND k3.seizoen = r3.uitslag_seizoen
AND k3.niveau = 'N3'
ORDER BY uitslag_naam, rank
) as u3
WHERE u3.rank<=5
ORDER BY uitslag_naam, rank";
$query = $this->db->prepare($sql);
$query->execute();
?>
$sql = "SET @num := 0, @type := ''";
$query = $this->db->prepare($sql);
$query->execute();
$sql = "
SELECT uitslag_naam, punten FROM (
SELECT (SELECT COUNT(cu.uitslag_naam)
FROM uitslagen AS cu
JOIN kalender AS ck ON ck.kalender_id = cu.uitslag_id
AND ck.categorie = cu.uitslag_categorie
AND ck.seizoen = cu.uitslag_seizoen
AND ck.niveau = 'N3'
WHERE cu.uitslag_naam = r3.uitslag_naam AND cu.punten >= r3.punten) AS rank,
id,
uitslag_naam,
punten
FROM uitslagen AS r3
JOIN kalender AS k3 ON k3.kalender_id = r3.uitslag_id
AND k3.categorie = r3.uitslag_categorie
AND k3.seizoen = r3.uitslag_seizoen
AND k3.niveau = 'N3'
ORDER BY uitslag_naam, rank
) as u3
WHERE u3.rank<=5
ORDER BY uitslag_naam, rank";
$query = $this->db->prepare($sql);
$query->execute();
?>
En kijk nog eens goed hoe je uitslagen met kalender JOINed. Volgens mij is uitslag_id voldoende en heb je de andere velden dan niet nodig. maar ik ken dus jouw data niet precies.
Gewijzigd op 05/12/2015 20:00:36 door Jan de Laet
Omdat het rijnummer hier alleen gebruikt wordt om op te filteren, kan je dit toekennen in de WHERE:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13
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
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
Gewijzigd op 06/12/2015 17:36:00 door Ger van Steenderen
@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.
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13
$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";
(
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!
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
28
29
30
31
32
33
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
28
29
30
31
32
33
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 gexporteerd 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 */;
--
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 gexporteerd 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 */;
Gewijzigd op 06/12/2015 20:51:12 door G Jansma
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT n3.uitslag_naam, n3.punten
FROM
(
SELECT
r3.uitslag_naam, r3.punten,
@num := IF(@naam = r3.uitslag_naam, @num + 1, 1) rownum,
@naam := r3.uitslag_naam
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
) n3
WHERE rownum <= 5
FROM
(
SELECT
r3.uitslag_naam, r3.punten,
@num := IF(@naam = r3.uitslag_naam, @num + 1, 1) rownum,
@naam := r3.uitslag_naam
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
) n3
WHERE rownum <= 5
Met jou data een SQL Fiddle gemaakt.
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
$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>";}
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>";}
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.
Gewijzigd op 07/12/2015 13:59:37 door Jan de Laet
Jan de Laet op 07/12/2015 13:43:48:
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.
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.
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.
Het moet namelijk zijn @id := 0
Gewijzigd op 07/12/2015 17:00:04 door Jan de Laet
Pagina: « vorige 1 2 3 volgende »