nicht angemeldet
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