Frage stellen

SQL Befehl vereinfachen

1.3k Aufrufe
Gefragt in Datenbanken von
Mahlzeit,

nicht von dem langen Post verschrecken lassen, die eigentliche Frage ist recht simpel. ;) Habe für eine Webanwendung eine SQL-Datenbank mit u.a. diesen beiden Tabellen.

tip1_teams
teamID teamName teamShort

tip1_games
gameID gameDay gameHomeTeamID gameAwayTeamID gameHomeTeamScore gameAwayTeamScore gameTime

Mithilfe dieser beiden Tabellen möchte ich jetzt eine Tabelle der Mannschaften erstellen. Im Grunde ja nix schwieriges, allerdings wird der Befehl da schnell extrem lang (s.u.). Im Grunde würde ich nur gerne wissen, welche Möglichkeiten es gibt, solche mehrfach abgeleiteten Abfragen etwas kompakter zu gestalten. Würde mich über jeden Tipp freuen.

Gruß
John


SELECT teamID, teamName, homegamenumber, awaygamenumber, NULL(homegamenumber,0)+IFNULL(awaygamenumber,0) AS gamenumber,
homegamewins, awaygamewins, IFNULL(homegamewins,0) + IFNULL(awaygamewins,0) AS gamewins,
homegamedraws, awaygamedraws, IFNULL(homegamedraws,0) + IFNULL(awaygamedraws,0) AS gamedraws,
homegamelost, awaygamelost, IFNULL(homegamelost,0) + IFNULL(awaygamelost,0) AS gamelost,
3*(IFNULL(homegamewins,0) + IFNULL(awaygamewins,0)) + IFNULL(homegamedraws,0) + IFNULL(awaygamedraws,0) AS points,
homegoalsscored, homegoalsconceded, awaygoalsscored, awaygoalsconceded,
IFNULL(homegoalsscored,0) + IFNULL(awaygoalsscored,0) AS goalsscored,
IFNULL(homegoalsconceded,0) + IFNULL(awaygoalsconceded,0) AS goalsconceded,
IFNULL(homegoalsscored,0) + IFNULL(awaygoalsscored,0) - IFNULL(homegoalsconceded,0) - IFNULL(awaygoalsconceded,0) AS goaldifference

FROM tip1_teams
LEFT JOIN (SELECT Count(*) AS homegamenumber, gameHomeTeamID FROM tip1_games GROUP BY gameHomeTeamID)A ON A.gameHomeTeamID = tip1_teams.teamID
LEFT JOIN (SELECT Count(*) AS awaygamenumber, gameAwayTeamID FROM tip1_games GROUP BY gameAwayTeamID)B ON B.gameAwayTeamID = tip1_teams.teamID
LEFT JOIN (SELECT Count(*) AS homegamewins, gameHomeTeamID, gameHomeTeamScore, gameAwayTeamScore FROM tip1_games WHERE gameHomeTeamScore > gameAwayTeamScore GROUP BY gameHomeTeamID)C ON C.gameHomeTeamID = tip1_teams.teamID
LEFT JOIN (SELECT Count(*) AS awaygamewins, gameAwayTeamID, gameHomeTeamScore, gameAwayTeamScore FROM tip1_games WHERE gameHomeTeamScore < gameAwayTeamScore GROUP BY gameAwayTeamID)D ON D.gameAwayTeamID = tip1_teams.teamID
LEFT JOIN (SELECT Count(*) AS homegamedraws, gameHomeTeamID, gameHomeTeamScore, gameAwayTeamScore FROM tip1_games WHERE gameHomeTeamScore = gameAwayTeamScore GROUP BY gameHomeTeamID)E ON E.gameHomeTeamID = tip1_teams.teamID
LEFT JOIN (SELECT Count(*) AS awaygamedraws, gameAwayTeamID, gameHomeTeamScore, gameAwayTeamScore FROM tip1_games WHERE gameHomeTeamScore = gameAwayTeamScore GROUP BY gameAwayTeamID)F ON F.gameAwayTeamID = tip1_teams.teamID
LEFT JOIN (SELECT Count(*) AS homegamelost, gameHomeTeamID, gameHomeTeamScore, gameAwayTeamScore FROM tip1_games WHERE gameHomeTeamScore < gameAwayTeamScore GROUP BY gameHomeTeamID)G ON G.gameHomeTeamID = tip1_teams.teamID
LEFT JOIN (SELECT Count(*) AS awaygamelost, gameAwayTeamID, gameHomeTeamScore, gameAwayTeamScore FROM tip1_games WHERE gameHomeTeamScore > gameAwayTeamScore GROUP BY gameAwayTeamID)H ON H.gameAwayTeamID = tip1_teams.teamID

LEFT JOIN (SELECT gameHomeTeamID, gameHomeTeamScore AS homegoalsscored FROM tip1_games GROUP BY gameHomeTeamScore)I ON I.gameHomeTeamID = tip1_teams.teamID
LEFT JOIN (SELECT gameHomeTeamID, gameAwayTeamScore AS homegoalsconceded FROM tip1_games GROUP BY gameAwayTeamScore)J ON J.gameHomeTeamID = tip1_teams.teamID
LEFT JOIN (SELECT gameAwayTeamID, gameAwayTeamScore AS awaygoalsscored FROM tip1_games GROUP BY gameAwayTeamScore)K ON K.gameAwayTeamID = tip1_teams.teamID
LEFT JOIN (SELECT gameAwayTeamID, gameHomeTeamScore AS awaygoalsconceded FROM tip1_games GROUP BY gameHomeTeamScore)L ON L.gameAwayTeamID = tip1_teams.teamID
GROUP BY teamID
ORDER BY points DESC, goaldifference DESC

3 Antworten

0 Punkte
Beantwortet von
solange die performance ausreichend ist spricht nichts gegen "lange" statements. würde das aber in eine stored procedure packen

LG
0 Punkte
Beantwortet von
Nun, die Performance ist da weniger das Problem, es werden in dem Fall ja auch nicht sonderlich viele Daten werden. Da das ganze aber ein kleines Tippspiel werden soll (ich weiß, gibt es alles schon, ist aber dennoch eine interessante Übung), ist das ja noch eine der einfacheren Abfragen. Umfangreicher wird es z.B., wenn man die Tabelle aller Teilnehmer ausgibt, da dann für jeden Spieler für jedes Spiel das Ergebnis mit seinem Tipp verglichen und entsprechend bewertet werden muss. Ich fürchte da kommt man dann schnell in Regionen, wo jegliche Übersicht verloren geht. Ich hatte gehofft, dass es da noch irgendeine Technik gibt, die mir bislang noch nicht bekannt war. Aber dann muss ich wohl in den sauren Apfel beißen.

Gruß
John
0 Punkte
Beantwortet von rahi Experte (1.5k Punkte)
Hallo John,

mit welchem SQL-Dialekt arbeitst du da? Ich habe mir den Spass gemacht und das Ganze mal in Access-SQL realisiert, allerdings mit einem anderen Verfahren, das auch dich interessieren könnte. Die letzten vier JOINs bilden doch die Summen oder habe ich das falsch verstanden? Falls das so ist, habe ich deine Summenbildung nicht verstanden. So etwas ähnliches kenne ich nur aus SAS. Also hier meine Version zum Ausprobieren und für konstruktive Kritik. Sorry für das "CLng", sonst bekomme ich eine Konkatenation statt einer Addition der Variablen ;-(


select * from
(
SELECT
teamID,
teamName,
homegamenumber,
awaygamenumber,
homegamenumber+awaygamenumber AS gamenumber,
homegamewins,
awaygamewins,
homegamewins + awaygamewins AS gamewins,
homegamedraws,
awaygamedraws,
homegamedraws + awaygamedraws AS gamedraws,
homegamelost,
awaygamelost,
homegamelost + awaygamelost AS gamelost,
3*(homegamewins + awaygamewins) + homegamedraws + awaygamedraws AS points,
homegoalsscored,
homegoalsconceded,
awaygoalsscored,
awaygoalsconceded,
homegoalsscored + awaygoalsscored AS goalsscored,
homegoalsconceded + awaygoalsconceded AS goalsconceded,
homegoalsscored + awaygoalsscored - homegoalsconceded - awaygoalsconceded AS goaldifference
FROM
(
select teamID,
teamName,
clng(nz((SELECT Count(*) FROM tip1_games where gameHomeTeamID = tip1_teams.teamID GROUP BY gameHomeTeamID),0)) AS homegamenumber,
clng(nz((SELECT Count(*) FROM tip1_games where gameAwayTeamID = tip1_teams.teamID GROUP BY gameAwayTeamID),0)) AS awaygamenumber,
clng(nz((SELECT Count(*) FROM tip1_games where gameHomeTeamScore > gameAwayTeamScore and gameHomeTeamID = tip1_teams.teamID GROUP BY gameHomeTeamID),0)) AS homegamewins,
clng(nz((SELECT Count(*) FROM tip1_games where gameHomeTeamScore < gameAwayTeamScore and gameAwayTeamID = tip1_teams.teamID GROUP BY gameAwayTeamID),0)) AS awaygamewins,
clng(nz((SELECT Count(*) FROM tip1_games where gameHomeTeamScore = gameAwayTeamScore and gameHomeTeamID = tip1_teams.teamID GROUP BY gameHomeTeamID),0)) AS homegamedraws,
clng(nz((SELECT Count(*) FROM tip1_games where gameHomeTeamScore = gameAwayTeamScore and gameAwayTeamID = tip1_teams.teamID GROUP BY gameAwayTeamID),0)) AS awaygamedraws,
clng(nz((SELECT Count(*) FROM tip1_games where gameHomeTeamScore < gameAwayTeamScore and gameHomeTeamID = tip1_teams.teamID GROUP BY gameHomeTeamID),0)) AS homegamelost,
clng(nz((SELECT Count(*) FROM tip1_games where gameHomeTeamScore > gameAwayTeamScore and gameAwayTeamID = tip1_teams.teamID GROUP BY gameAwayTeamID),0)) AS awaygamelost,
clng(nz((SELECT sum(gameHomeTeamScore) FROM tip1_games where gameHomeTeamID = tip1_teams.teamID ),0)) AS homegoalsscored,
clng(nz((SELECT sum(gameAwayTeamScore) FROM tip1_games where gameHomeTeamID = tip1_teams.teamID),0)) AS homegoalsconceded,
clng(nz((SELECT sum(gameAwayTeamScore) FROM tip1_games where gameAwayTeamID = tip1_teams.teamID ),0)) AS awaygoalsscored,
clng(nz((SELECT sum(gameHomeTeamScore) FROM tip1_games where gameAwayTeamID = tip1_teams.teamID ),0)) AS awaygoalsconceded
FROM tip1_teams
))
ORDER BY points DESC, goaldifference DESC


Wir könnten mal einen Laufzeittest machen ...

Gruß
Ralf
...