Klassement berekenen int 1 query

Door Koshi koshi op 19-01-2015 22:20 gewijzigd op 19-01-2015 22:46

2.279 views

Een tijdje geleden had ik een topic gevraagd hoe je bij de output van het klassement een ploeg in een andere kleur krijgt.
Ger van Steenderen zijn reactive hierop was :

Ik denk dat je beter eerst de vraag kunt stellen hoe je gewoon via één query de stand kan genereren vanuit de uitslagen.

Nu ga je een paar honderd queries uitvoeren, en dan een tabel legen en updaten. Wat als er gelijktijdige requests plaats vinden?

Is het mogelijk a.d.h.v. mijn tabellen een query te schrijven waarmee je in 1 query het klassement berekend i.p.v. enkele honderden.
Wie kan mij hiermee helpen?

TABLE `kalender` (
`Datum` text NOT NULL,
`Dag` varchar(10) NOT NULL default '',
`Uur` varchar(5) NOT NULL default '',
`Reeks` varchar(7) NOT NULL default '',
`Thuisploeg` varchar(25) NOT NULL default '',
`Bezoekers` varchar(25) NOT NULL default '',
`Wedstijd` varchar(5) NOT NULL default '',
`Dthuis` char(2) NOT NULL default '',
`Dbezoek` char(2) NOT NULL default '',
`ID` int(4) NOT NULL default '0'
) ENGINE=MyISAM;

TABLE `klassement` (
`Ploeg` varchar(30) NOT NULL default '',
`Gesp` int(11) NOT NULL default '0',
`Gew` int(11) NOT NULL default '0',
`Verl` int(11) NOT NULL default '0',
`Gel` int(11) NOT NULL default '0',
`Dvoor` int(11) NOT NULL default '0',
`Dtegen` int(11) NOT NULL default '0',
`Punten` int(11) NOT NULL default '0'
) ENGINE=MyISAM;


<?php

if(!isset($_POST["select"])){$_POST["select"] ="R1";}

mysql_select_db("$db",$connectie);

//klassement leegmaken
$leeg="delete from klassement";
mysql_query($leeg, $connectie);

//klassement opvullen met de verschillende thuisploegen uit de kalender
if(!isset($_POST["select"])){$_POST["select"] ="";}
$lezen="select distinct thuisploeg from kalender where Reeks like '" .$_POST["select"]. "'";
$rst=mysql_query($lezen, $connectie);
$aantal_lijnen=mysql_num_rows($rst);
for($i=0;$i<$aantal_lijnen;$i++) {
	$row=mysql_fetch_row($rst);
	$vullen="insert into klassement(ploeg) values ('".$row[0]."')";
	mysql_query($vullen,$connectie);
	}
	
//klassement berekenen
$sel="select * from kalender where Reeks like '" .$_POST["select"]. "'";
$selrst=mysql_query($sel, $connectie);
$sel_lijnen=mysql_num_rows($selrst);
for($i=0;$i<$sel_lijnen;$i++) {
$rij=mysql_fetch_row($selrst);
	//thuisploeg
	if (($rij[7]!="") and ($rij[8]!="")){
		$sql="update klassement set gesp=gesp+1, Dvoor=Dvoor + ".$rij[7].", Dtegen=Dtegen + ".$rij[8]." where ploeg='".$rij[4]."'";
		mysql_query($sql, $connectie);
		if ($rij[7]>$rij[8]){
		$sql2="update klassement set gew=gew+1, punten=punten+3 where ploeg='".$rij[4]."'";}
		else if ($rij[7]<$rij[8]){
		$sql2="update klassement set verl=verl+1 where ploeg='".$rij[4]."'";}
		else{
		$sql2="update klassement set gel=gel+1, punten=punten+1 where ploeg='".$rij[4]."'";}
		mysql_query($sql2, $connectie);
	}
	//bezoekers	
	if (($rij[7]!="") and ($rij[8]!="")){
		$sql="update klassement set gesp=gesp+1, Dvoor=Dvoor + ".$rij[8].", Dtegen=Dtegen + ".$rij[7]." where ploeg='".$rij[5]."'";
		mysql_query($sql, $connectie);
		if ($rij[8]>$rij[7]){
		$sql2="update klassement set gew=gew+1, punten=punten+3 where ploeg='".$rij[5]."'";}
		else if ($rij[7]>$rij[8]){
		$sql2="update klassement set verl=verl+1 where ploeg='".$rij[5]."'";}
		else{
		$sql2="update klassement set gel=gel+1, punten=punten+1 where ploeg='".$rij[5]."'";}
		mysql_query($sql2, $connectie);
	}
}

//klassement 
$klas="select * from klassement order by punten desc, gew desc, dvoor-dtegen desc, dvoor desc";
$klasrst=mysql_query($klas, $connectie);
$klas_lijnen=mysql_num_rows($klasrst);



print("<table border = 0 width=600 cellspacing='0' align='center' style='font-size=14;'><tr align='center' bgcolor='#CCCCCC'><td><font color='#000000'>Ploeg</td>");
print("<td width=60><font color='#000000'>Gesp</td>");
print("<td width=60><font color='#000000'>Gew</td>");
print("<td width=60><font color='#000000'>Gel</td>");
print("<td width=60><font color='#000000'>Verl</td>");
print("<td width=60><font color='#000000'>Dvoor</td>");
print("<td width=60><font color='#000000'>Dtegen</td>");
print("<td width=60><font color='#000000'>Punten</td></tr>");

for($i=0;$i<$klas_lijnen;$i++) {
	$row=mysql_fetch_row($klasrst);
	echo "<tr align='center'><td>$row[0]</td><td>$row[1]</td><td>$row[2]</td><td>$row[4]</td><td>$row[3]</td><td>$row[5]</td><td>$row[6]</td><td>$row[7]</td></tr>";
	}
print("</table>");
?>

Ivo P

19-01-2015 22:40

Doe eens een link naar je vorige topic voor we al het commentaar over onbegrijpelijke afko's en nummers gaan herhalen

R Robert Steegh

20-01-2015 18:21

Ik begrijp de tabellen in de database niet. Wat is de relatie tussen deze 2 tabellen? De oplossing zit denk ik in het maken van een subquery zoiets als dit:
UPDATE klassement set gesp=gesp+1, WHERE thuisploeg IN
(SELECT ploeg FROM kalender WHERE gesp = 1 and datum > dd.mm.jjjj)

Gespeeld = Gespeeld + 1 in klassement als in de tabel kalender een record gespeeld=1 aanwezig is voor deze ploeg vanaf een bepaalde datum (waarop de stand nog niet is bijgewerkt.

Op deze manier heb je 3 SQL's nodig. In het ergste geval moet je SQL script op de server maken.

Ger van Steenderen

21-01-2015 14:30

Ik had in TS zijn andere topic al een link geplaatst naar:

Ger van Steenderen op 10/08/2013 09:51:14

Beter laat dan nooit:


SELECT
	c.clubID,
	c.clubnaam,
	SUM(IF(c.clubID = u.thuisclubID,
			CASE
				WHEN u.thuisscore > u.uitscore THEN 3
				WHEN u.thuisscore = u.uitscore THEN 1
				ELSE 0 END
		, /* ELSE */
			CASE
				WHEN u.thuisscore < u.uitscore THEN 3
				WHEN u.thuisscore = u.uitscore THEN 1
				ELSE 0 END
		)) punt_totaal,
	COUNT(u.wedstrijdID) gespeeld,
	SUM(IF(c.clubID = u.thuisclubID,
			IF(u.thuisscore > u.uitscore, 1, 0),
			IF(u.thuisscore < u.uitscore, 1, 0)
		)) gewonnen,
	SUM(IF(c.clubID = u.thuisclubID,
			IF(u.thuisscore < u.uitscore, 1, 0),
			IF(u.thuisscore > u.uitscore, 1, 0)
		)) verloren,
	SUM(IF(u.thuisscore = u.uitscore, 1, 0)) gelijk,
	SUM(IF(c.clubID = u.thuisclubID, thuisscore, uitscore)) doelp_voor,
	SUM(IF(c.clubID = u.thuisclubID, uitscore, thuisscore)) doelp_tegen,
	SUM(IF(c.clubID = u.thuisclubID, thuisscore, uitscore)) -
		SUM(IF(c.clubID = u.thuisclubID, uitscore, thuisscore)) doelsaldo
FROM
	clubs c
LEFT JOIN
	(SELECT
		wedstrijdID,
		w.thuisclubID,
		w.uitclubID,
		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
		USING (wedstrijdID)
	WHERE w.datumwedstrijd BETWEEN '2012-08-09' AND '2012-08-12'
	GROUP BY wedstrijdID, w.thuisclubID, w.uitclubID
	) u
	ON c.clubID = u.thuisclubID OR c.clubID = u.uitclubID
GROUP BY c.clubID, c.clubnaam
ORDER BY punt_totaal DESC, gespeeld, doelsaldo DESC, doelp_voor DESC

Hij kan dan gewoon de gehele subquery vervangen door zijn tabel met wedstrijden, en de kolomnamen aanpassen.

K Koshi koshi

21-01-2015 18:12

Ger,

Zou het mogelijk zijn om jou tabel(len) en indeling door te geven, ik zie niet onmiddelijk hoe ik met mijn tabel de query kan laten werken.

Bedankt

Ger van Steenderen

21-01-2015 19:20 gewijzigd op 21-01-2015 19:26

Het zijn niet mijn tabellen (ik doe alles op zijn engels en met alleen kleine letters en _), maar denk aan zoiets:


CREATE TABLE clubs (
	clubID INT NOT NULL AUTO_INCREMENT,
	clubnaam VARCHAR(40),
	PRIMARY KEY (clubID)
)

CREATE TABLE wedstrijd_schema (
	wedstrijdID INT NOT NULL AUTO_INCREMENT,
	datumwedstrijd DATETIME NOT NULL,
	thuisclubID INT NOT NULL,
	thuisscore TINYINT,
	uitclubID INT NOT NULL,
	uitscore TINYINT,
	PRIMARY KEY (wedstrijdID)
)

Nogmaals, de subquery na de LEFT JOIN (regel 32 t/m 45) kan je vervangen door de wedstrijd_schema tabel

K Koshi koshi

23-01-2015 15:02

vanwaar komt de c., u., w., d., ...
dit lijkt mij niet duidelijk, ik snap het helemaal niet :(

Ger van Steenderen

23-01-2015 15:19

Dat zijn aliassen voor de tabelnamen zodat je niet steeds de gehele tabelnaam moet aangeven bij de kolommen.

K Koshi koshi

23-01-2015 23:49

Bedankt voor de moeite, mijn kennis van sql is nihil.
Ik zal maar verder doen met mijn huidige code!

Reageren

Inloggen om te reageren