✔ SELECT mit Positionsangabe in Ergebnismenge

[DiDiJo]

Hi Leute,

ich habe eine Highscore Tabelle:

ID | Team | Ergebnis | Vorname | Nachname

... die ich gerne verschieden Sortiert ausgeben möchte. In der Ausgabe soll man später nur folgende Felder sehen:

POSITION | Team | Ergebnis

Position ist eigentlich nicht in der DB vorhanden und möchte es dynamisch meiner Ergebnismenge hinzufügen. Man soll dann nach Teamname oder Position sortieren können. Optional soll man auch die Ergnisse nach Teamnamen filtern können (nur alle Teams anzeigen in denen eine bestimmte Zeichenkette vorkommt).

Kann mir da jemand vlt. mit dem Select helfen?

Meiner sieht momentan noch so aus allerdings ist der nicht korrekt (Position wird nicht richtig angegeben):

	if ($keyword != '') {
		$where = "WHERE team LIKE '%$keyword%' OR firma LIKE '%$keyword%'";
	} else {
		$where = 'WHERE 1';
	}	

	$sql = "
	SELECT 
	DISTINCT team, SUM(ergebnis) as erg,
	(SELECT COUNT(DISTINCT team) from highscore where ergebnis >= hs.ergebnis) as 'pos'  
	FROM `highscore` hs $where GROUP by 1 ORDER BY $sort_field $sorting";

Der Select ist nicht vollkommen falsch ... es kann kein großer Fehler sein

 

[Yaslaw]

Ich geh mal davon aus, dass du mit MySQL arbeitest.
Dort gibt es leider die Funtion ROWNUM nicht direkt. Dass kannst du aber mitSQL- Variablen selber machen.

SELECT
    @rownum:=@rownum+1 AS rownum,
    t.*
FROM
    (SELECT @rownum:=0) AS vars,
    mytable AS t
ORDER BY
    t.sortfield ASC;

Wenn du nun nach der Position (rownum) suchen willst, dann mach das mit einem Subquery

-- Beispiel um die Zeile 15 auszugeben
SELECT
    mydata.*
FROM
    (
        SELECT
            @rownum:=@rownum+1 AS rownum,
            t.*
        FROM
            (SELECT @rownum:=0) AS vars,
            mytable AS t
        ORDER BY
            t.sortfield ASC
    ) AS mydata
WHERE
    mydata.rownum = 15;

 

[DiDiJo]

Ok, wie ich mit normalen Selects umgehe weiß ich gerade noch so ... Allerdings ist mir deine Erklärung ein wenig unverständlich ... kannst du mir nicht eben eine Hilfestellung geben wie ich das auf meine Tabellenstruktur umbauen kann. Die Struktur ist ja oben gegeben (und die Tabelle heißt highscore)

 

[Yaslaw]

item: Als erstes formatierst du mal dein SQL, damit es lesbar ist.

SELECT DISTINCT 
	team, 
	SUM(ergebnis) as erg
	--irgendwas as pos  
FROM 
	`highscore` hs 
{$where} 
GROUP by 1 
ORDER BY 
	{$sort_field} {$sorting}

item: Beim Formatieren ist mir aufgefallen, dass du den Feldnamen pos als String gechriben hast und nicht als Feldname

irgendwas AS 'pos'
--müsste so heissen
irgnedwas AS pos
--oder
irgendwas AS `pos`

item: Und was soll das GROUP BY 1? Ich denke du willst per team gruppieren.

item: Der DISTINCT bringt nix, wenn du schon mit dem GROUP BY die Werte zusammenfasst.


So, dann trennen wir das ganze mal auf und bestimmen die pos ohne das WHERE. Dazu verwenden wir die Variable @pos (analog dem @rownum in meinem Beispiel). Damit wir @pos mal mit 0 initialisieren können ohne vorher noch ein SET-Stement abzuschicken, können wir dies in einem einfache Subselect machen.
Zu den Varibalen muss man das folgende wissen.
- sie beginnen mit einem @
- mit := kann man Werte zuweisen, mit = vergleichen
- sie behalten ihren Wert über die Ausgabenzeilen hinweg. So kann man sie in jeder Ausgabenzeile ansprechen und neu überschreiben.
- WICHTIG! Sie wird vor dem Sortieren berechnet. Also muss man als Source bereits mit einem sortierten Query kommen

(SELECT @pos:=0) AS vars

OK, fangen wir an. Ich verwende die Scrheibweise <<SQL-Block>> um verschachtelte SQLs ein wenig abstrakter darzustellen.
Die Daten sortiert und gefiltert:

-- <<my_data>>
SELECT
	hs.team,
	SUM(hs.ergebnis) as erg
FROM
	highscore AS hs
GROUP BY
	hs.team
ORDER BY
	erg ASC

Das SQL um den Rang (pos) zu ermitteln

-- <<rangliste>>
SELECT
	@pos:=@pos+1 AS pos,
	mydata.*
FROM
	(SELECT @pos:=0) AS vars,
	(<<my_data>>) AS mydata

Zusammengesetzt sieht dann das so aus

-- <<rangliste>>
SELECT
	@pos:=@pos+1 AS pos,
	mydata.*
FROM
	(SELECT @pos:=0) AS vars,
	(
		SELECT
			hs.team,
			SUM(hs.ergebnis) as erg
		FROM
			highscore AS hs
		GROUP BY
			hs.team
		ORDER BY
			hs.erg ASC
		) AS mydata

Wenn du nun auf dieses Resultat noch ein Filter setzten willst, so musst du das ganze von oben wieder als Subquery nehemn und darüber den Filter setzen

SELECT
	rl.*
FROM
	(<<rangliste>>) AS rl
WHERE
	rl.pos = 15

und zusammengesetzt ergibt sich das folgende

SELECT
	rl.*
FROM
	(
		SELECT
			@pos:=@pos+1 AS pos,
			mydata.*
		FROM
			(SELECT @pos:=0) AS vars,
			(
				SELECT
					hs.team,
					SUM(hs.ergebnis) as erg
				FROM
					highscore AS hs
				GROUP BY
					hs.team
				ORDER BY
					hs.erg ASC
				) AS mydata
	) AS rl
WHERE
	rl.pos = 15

So, ich hoffe du verstehst das so.

 

[DiDiJo]

soweit fast alles klar ... ich werde das ganze mal ausprobieren ...

Ich weiß gerade bloß nicht wo ich die WHERE Klausel für meinen Filter einbauen werden soll... oder wäre das in deinem letzten Beispiel die Zeile 23 wo ich anstatt auf eine Position eine LIKE Anweisung auf den Teamnamen machen würde?

 

[Yaslaw]

Jepp, genau dort. Das SQL muss ja zuerst die pos bestimmen und dann kommt der Filter. Für ide pos muss ja die Liste komplett aufgebaut werden. Wenn du dein Filter früher machst, dann stimmt die pos nicht.

 

[DiDiJo]

Also ansich klappt das nun fast ganz gut. Ich habe erst einen Fehler erhalten wegen dem innersten SELECT in deinem letzten Beispiel weil das 'hs' nicht bekannt war momentan sieht der code so aus:

	SELECT rl.*
	FROM
		(
			SELECT @pos:=@pos+1 AS pos,	mydata.*
			FROM
				(SELECT @pos:=0) AS vars,
				(
					SELECT team, SUM(ergebnis) AS erg
					FROM highscore 
					GROUP BY team
					ORDER BY erg DESC
				) AS mydata
		) AS rl
	WHERE 1

Wenn ich aber nun das "ORDER BY erg DESC" in ein "ORDER BY erg ASC" umwandele stimmt wieder die Position nicht

 

[Yaslaw]

Was heisst:

Wenn ich aber nun das "ORDER BY erg DESC" in ein "ORDER BY erg ASC" umwandele stimmt wieder die Position nicht

Wie ist in der DB, wie willst du es haben, wie kommt es vom SQL zurück. Mach Beispiele oder erkläre es genauer

 

[DiDiJo]

ich möchte auf einer Homepage die Tabelle auslesen lassen. Dort soll der User die Möglichkeit haben oben in der Tabelle auf Position oder Teamname zu drücken um die Tabelle entsprechend umzusortieren. Dafür müsste ich in dem Select Statement das "ORDER BY erg ASC" durch "ORDER BY erg DESC" ersetzen, damit das Ergebnis richtig sortiert wird. Das passiert auch ... allerdings bleibt die Reihenfolge in dem Feld POSITION gleich obwohl die sich ja auch mit umdrehen müsste.

 

[Yaslaw]

Versteh ich richtig. Auch wenn du im innersten SELECT den ORDER BY erg DESC hast, wir das Team mit den wenigsten Punkten mit der pos 1 angezeigt?
Mach doch Beispiele. 1 mal dein SQL mit ASC posten und das Resultat. Dann noch dasselbe mit dem DESC.