nicht angemeldet
MySQL-Problem: Query über 4 Tabellen mit mehreren Counts
Servus,
wäre spitzenmäßig wenn hier der Ein oder Andere Rat wüsste:
Wie die Überschrift verrät, komplexer Query über 4 Tabellen.
Eigentlich läuft die Sache auch schon, allerdings scheint mir das ein Wenig unschön, so wie es momentan umgesetzt ist, nämlich mit zahlreichen Subselects. Bin aber kein Profi, deshalb frage ich hier nochmal.
Weshalb ich überhaupt schreibe: Beim weiter verfeinern gibts jetzt ein Problem: Ich will den Rückgabewert eines meiner Subquerys mit 3 Multiplizieren. Lässt sich aber nicht als Spalte ansprechen
Kurz vorab, es geht um eine Software zur Sportturnierverwaltung. Der Query soll alle Teams mit Punkten (gewonnene Spiele x 3), Toren, Gegentoren, Spielen insgesamt, etc liefern:
SELECT
t.name AS team,
t.id AS team_id,
t.city AS city,
p1.name AS player1,
p2.name AS player2,
p3.name AS player3,
(
SELECT
count(*)
FROM
matches AS m
WHERE
(team1 = t.id
OR
team2 = t.id)
AND
status ='1'
AND
(
SELECT
count(*)
FROM
goals
WHERE(
((team_id = t.id AND regular = '1')
OR
(team_id != t.id AND regular = '0'))
AND
match_id = m.id
)
) > (
SELECT
count(*)
FROM
goals
WHERE(
((team_id != t.id AND regular = '1')
OR
(team_id = t.id AND regular = '0'))
AND
match_id = m.id
)
)
AND
bracket_id = '3'
) AS matches_won,
(
SELECT
count(*)
FROM
matches AS m
WHERE
(team1 = t.id
OR
team2 = t.id)
AND
status ='1'
AND
(
SELECT
count(*)
FROM
goals
WHERE(
((team_id = t.id AND regular = '1')
OR
(team_id != t.id AND regular = '0'))
AND
match_id = m.id
)
) < (
SELECT
count(*)
FROM
goals
WHERE(
((team_id != t.id AND regular = '1')
OR
(team_id = t.id AND regular = '0'))
AND
match_id = m.id
)
)
AND
bracket_id = '3'
) AS matches_lost,
(
SELECT
count(*)
FROM
matches AS m
WHERE
(team1 = t.id
OR
team2 = t.id)
AND
status ='1'
AND
(SELECT count(*) FROM goals WHERE team_id = t.id AND match_id = m.id) = (SELECT count(*) FROM goals WHERE team_id != t.id AND match_id = m.id)
AND
bracket_id = '3'
) AS matches_draw,
(
SELECT
count(*)
FROM
goals
WHERE
((team_id = t.id AND regular = '1')
OR
(team_id != t.id AND regular = '0'))
AND
match_id IN (SELECT id FROM matches WHERE (team1=t.id OR team2=t.id) AND bracket_id = '3' AND status ='1')
) AS goals,
(
SELECT
COUNT(*)
FROM
goals
WHERE
((team_id != t.id AND regular = '1')
OR
(team_id = t.id AND regular = '0'))
AND
match_id IN (SELECT id FROM matches WHERE (team1=t.id OR team2=t.id) AND bracket_id = '3' AND status ='1')
) AS goals_against,
(
SELECT
COUNT(*)
FROM
matches
WHERE
status >= '1'
AND
(team1 = t.id
OR
team2 = t.id)
AND
status ='1'
AND
bracket_id = '3'
) AS games_played
FROM
teams AS t
INNER JOIN players AS p1 ON t.player1 = p1.id
INNER JOIN players AS p2 ON t.player2 = p2.id
INNER JOIN players AS p3 ON t.player3 = p3.id
WHERE
t.id IN (
SELECT
team1
FROM
matches
WHERE
bracket_id='3'
)
OR
t.id IN (
SELECT
team2
FROM
matches
WHERE
bracket_id='3'
)
ORDER BY matches_won DESC, goals DESC, goals_against ASC
ein "(matches_won*3 + matches_draw) AS points" in die field list einzufügen gibt jetzt einen Fehler (Spalte nicht gefunden...)
Datenbankstruktur (das Wichtigste):
CREATE TABLE goals (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
team\_id int(5) unsigned DEFAULT NULL,
player\_id int(10) unsigned DEFAULT NULL,
match\_id int(10) unsigned DEFAULT NULL,
g\_minute int(10) unsigned DEFAULT NULL,
regular tinyint(3) unsigned DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=55 DEFAULT CHARSET=latin1;
CREATE TABLE matches (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
bracket\_id int(5) unsigned DEFAULT NULL,
court\_id int(5) unsigned DEFAULT NULL,
team1 int(5) unsigned DEFAULT NULL,
team2 int(5) unsigned DEFAULT NULL,
datetime datetime DEFAULT NULL,
identifier tinyint(3) unsigned DEFAULT NULL,
status tinyint(1) unsigned DEFAULT NULL,
PRIMARY KEY (id),
KEY id\_2 (id),
KEY id (id)
) ENGINE=InnoDB AUTO_INCREMENT=361 DEFAULT CHARSET=latin1;
CREATE TABLE teams (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
name varchar(50) DEFAULT NULL,
city varchar(50) DEFAULT NULL,
player1 int(5) unsigned DEFAULT NULL,
player2 int(5) unsigned DEFAULT NULL,
player3 int(5) unsigned DEFAULT NULL,
logo varchar(50) DEFAULT NULL,
created\_by int(5) unsigned DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=latin1;
Also, wie gesagt: Das eigentliche Problem ist, dass ich noch eine Spalte points brauche, die aus gewonnenen und unentschiedenen Spielen berechnet wird. Wenn mir Jemand sagt wie ich in dem Aufwasch gleich noch die ganze Abfrage verbessern kann wärs noch besser.
Besten Dank!!
MfG,
Max
-
Hallo,
geht es um eine bestimmte Sportart?
Davon abgesehen, finde ich deine Umsetzung unnötig komplex. Statt alles erst bei der Auswertung (Anzeige) berechnen zu lassen, solltest du evt. beim Eintragen von Ergebnissen Berechnungen (über Trigger) auslösen, welche in bestimmten Tabellen Updates auslösen. Damit sollte die Query, welche dir den "Liga" Stand ausgibt ein Kinderspiel werden.
Ich habe vor 4 Jahren mal ein (firmeninternes und nicht-reguliertes) Wettspiel für die Fussball WM gebaut, welches abgesehen von den Spielergebnissen natürlich noch die Wetten und Wettergebnisse mit berücksichtigen musste. Stand damals in etwa vor den selben Problemen, zu komplexe und unperformante Queries. Da hat selbst ein besseres Datenbanksystem als mysql nichts genutzt.
Warum hast du in "matches" 3x dieselbe Spalte als Key definiert. Einmal Primary Key hätte doch genügt?
"matches_draw" und "matches_won" sind ja Tabellen und keine Spalten. Vielleicht solltest du dem COUNT(*) darin noch einen Aliasnamen verpassen ... und dann:
matches_won.Anzahl * 3 + matches_draw.Anzahl
CIao, Frank
-
Hi,
vielen Dank für deine Antwort!geht es um eine bestimmte Sportart?
Mannschaftssportarten generell. In meinem Fall Streethockey.
Davon abgesehen, finde ich deine Umsetzung unnötig komplex. Statt alles erst bei der Auswertung (Anzeige) berechnen zu lassen, solltest du evt. beim Eintragen von Ergebnissen Berechnungen (über Trigger) auslösen, welche in bestimmten Tabellen Updates auslösen. Damit sollte die Query, welche dir den "Liga" Stand ausgibt ein Kinderspiel werden.
Ich habe die Tore absichtlich in einer separaten Quelle abgelegt und nicht schon pro Spiel aufsummiert (siehe anderer Post)
Ich habe vor 4 Jahren mal ein (firmeninternes und nicht-reguliertes) Wettspiel für die Fussball WM gebaut, welches abgesehen von den Spielergebnissen natürlich noch die Wetten und Wettergebnisse mit berücksichtigen musste. Stand damals in etwa vor den selben Problemen, zu komplexe und unperformante Queries. Da hat selbst ein besseres Datenbanksystem als mysql nichts genutzt.
Habe eigentlich keine Probleme mit der Performance, habe nur das Problem das ich jetzt habe zum Anlass genommen mal die grundsätzliche Architektur des Query in Frage zu stellen, schien mir subjektiv nicht so sauber.
Warum hast du in "matches" 3x dieselbe Spalte als Key definiert. Einmal Primary Key hätte doch genügt?
Gar nicht bemerkt, ist korrigiert, danke.
"matches_draw" und "matches_won" sind ja Tabellen und keine Spalten. Vielleicht solltest du dem COUNT(*) darin noch einen Aliasnamen verpassen ... und dann:
matches_won.Anzahl * 3 + matches_draw.Anzahl
Schon probiert. Der Subquery tut in meinem Fall ja das Gleiche wie ein INNER JOIN (wenn ich mich nicht irre), demnach ist z.B. matches_won schon eine Spalte. Mehr als eine Spalte darf der Subquery auch garnicht zurückgeben, sonst gibts Den hier: "Error: Operand should contain 1 column(s)"
-
-
moin,
Kurz vorab, es geht um eine Software zur Sportturnierverwaltung. Der Query soll alle Teams mit Punkten (gewonnene Spiele x 3), Toren, Gegentoren, Spielen insgesamt, etc liefern:
reden wir über fussballspiele ?
Datenbankstruktur (das Wichtigste):
wo ist den die tabelle players und die daten-struktur scheint auf den ersten blick ungünstig gewählt zu sein. warum gibt es in der tabelle teams dreimal einen fremdschlüssel von der tabelle players ?
gib mal bitte ein wenig mehr fachliche infos bezüglich dem, was du abbilden willst.
und du kannst dir in der zwischenzeit ja mal einen alten beitrag anschauen, der eine ähnliche problematik behandelt:
http://forum.de.selfhtml.org/archiv/2004/9/t89605/#m536283
Ilja
-
Morgen,
danke für deine Antwort.
reden wir über fussballspiele ?
Mannschaftssportarten generell. In meinem Fall Streethockey.
wo ist den die tabelle players und die daten-struktur scheint auf den ersten blick ungünstig gewählt zu sein. warum gibt es in der tabelle teams dreimal einen fremdschlüssel von der tabelle players ?
Die Tabelle habe ich rausgelassen, da sie mit dem eigentlichen Problem nichts zu tun hat. Drei Fremdschlüssel gibt es weil die Spieler nicht eindeutig einem Team zugeordnet sein sollen, sondern Mitglied mehrerer Teams sein können.
gib mal bitte ein wenig mehr fachliche infos bezüglich dem, was du abbilden willst.
Verstehe nicht?!
und du kannst dir in der zwischenzeit ja mal einen alten beitrag anschauen, der eine ähnliche problematik behandelt:
http://forum.de.selfhtml.org/archiv/2004/9/t89605/#m536283
Den kenn' ich schon. Ich habe allerdings die Tore in einer separaten Quelle abgelegt und nicht schon pro Spiel aufsummiert. Ich möchte die Zuordnung Tor - Torschütze eindeutiger erhalten.
Da die Software für den laufenden Spielbetrieb gedacht ist, schien es mir eher unsicher die Tore doppelt abzulegen und die Abfragen zu triggern. Es gibt zu viele Eventualitäten.Wie gesagt, die Sache läuft, ich bekomme genau mein gewünschtes Ergebnis und die Rechenzeit ist OK. Mit dem Punkte zählen direkt in der Abfrage stoße ich jetzt allerdings auf Probleme.
MfG,
Max-
moin,
Die Tabelle habe ich rausgelassen, da sie mit dem eigentlichen Problem nichts zu tun hat. Drei Fremdschlüssel gibt es weil die Spieler nicht eindeutig einem Team zugeordnet sein sollen, sondern Mitglied mehrerer Teams sein können.
auch dann macht es meiner meinung nach keinen sinn, dort drei fremdschlüssel rein zu tun, sondern eine m:n beziehungstabelle zwischen spieler und mannschaften wäre der normale weg, dies umzusetzen, es sei den es gibt gute gründe dafür es nicht zu tun.
gib mal bitte ein wenig mehr fachliche infos bezüglich dem, was du abbilden willst.
Verstehe nicht?!
wenn es um abfragen geht, ist es uns immer hilfreich zu wissen, was eigentlich genau umgesretzt werden soll, also beschreibung deiner umgebung, die du abbildest und das mit worten und nicht mit tabellen und inhalten, bzw. einer sql abfrage.
Den kenn' ich schon. Ich habe allerdings die Tore in einer separaten Quelle abgelegt und nicht schon pro Spiel aufsummiert. Ich möchte die Zuordnung Tor - Torschütze eindeutiger erhalten.
mag sein, der ansatz ist aber der gleiche, musst um die tore zu bekommen, über die spieler gehen.
Ilja
-
-