MySQL - Join mit Unterabfrage und Group by?

DonCamillo

DonCamillo

Grünschnabel
Hallo zusammen,

meine Datenbanktage im Studium sind schon einige Zeit her und dementsprechend nicht mehr vollständig parat.
Ich habe ein kleines Problemchen mit einem Join über 3 Tabellen.

- Tabelle spieler mit id (PK), nachname, vorname, alter, ...
- Tabelle entwicklung mit spielerId (FK), staerkeId (PK FK)
- Tabelle staerke mit staerkeId (PK), staerke, jahr
(In der Tabelle staerke sind pro Halbjahr alle Stärken von 1-10 aufgeführt)

Jeder Spieler entwickelt sich 2x pro Jahr (--> jahr = 201001, 201002, 201101 etc. als integer-Zahl). Entweder wird er besser, schlechter oder er behält seine Stärke. Jetzt will ich mehrere Fälle rausfinden:
1. Der Spieler wurde in der letzten Entwicklung besser. (z. B. von Stärke 4 auf 5)
2. Der Spieler wurde in der letzten Entwicklung schlechter. (z. B. von Stärke 5 auf 4)
3. Der Spieler blieb in der letzten Entwicklung gleich. (z. B. von Stärke 10 auf 10)
4. Der Spieler war in seinen früheren Tagen schon einmal stärker als aktuell. (z. B. Stärke zum Zeitpunkt 200501 10, danach immer 9)

Den Join habe ich soweit schon hinbekommen:
SQL: 
SELECT spieler.id, staerke.staerke, staerke.jahr
FROM (
spieler
INNER JOIN entwicklung ON spieler.id = entwicklung.spielerId
)
LEFT JOIN staerke ON entwicklung.staerkeId = staerke.staerkeId
WHERE spieler.alter between 20 and 25

Mit GROUP BY spieler.id kann ich noch bspw. die maximale und minimale Stärke pro Spieler ausgeben.

Allerdings habe ich keine Ahnung (2h schon rumprobiert), wie ich den Bezug der Stärke zum Jahr machen kann. Konkret also sollten nur die spieler.id rausgegeben werden, wo der Spieler seine staerke im jahr 201101 größer hatte als im jahr 201002 (Fall 1, s. o.).

Mein Ansatz wäre etwas à la
SQL: 
SELECT spieler.id, staerke.staerke, staerke.jahr
FROM (
spieler
INNER JOIN entwicklung ON spieler.id = entwicklung.spielerId
)
LEFT JOIN staerke ON entwicklung.staerkeId = staerke.staerkeId
WHERE spieler.alter between 20 and 25
GROUP BY spieler.id
HAVING MAX(staerke.staerke) <> (SELECT max(staerke) from ... where jahr <> 201101)
(Fall 4)

Kann mir irgendjemand evtl. einen Hinweis geben, wie ich den Bezug zwischen staerke und jahr hinbekomme, sortiert nach spieler.id?

Vielen Dank im Voraus!
 
Zuletzt bearbeitet von einem Moderator:
Hey hey,

kann mir niemand weiterhelfen? Oder braucht jemand noch Infos, um mein Problem nachvollziehen zu können?

ich will ja gar keine Komplettlösung, sondern ein Tip, welchen Ansatz ich verfolgen sollte, würde mir schon enorm weiterhelfen :)
 
Das kann relativ komplex werden. Vergleichst du bei 2) und 3) immer nur innerhalb des selben Jahres? Also 201001 mit 2001002 aber nie 201002 mit 201101.

Kannst du ev. ein dein TABLE-CREATE-Script und in Table-Dump mit etwa 10 Testzeilen erstellen? Es ist nicht gerade einfach, einfach so blind ein SCL hinzublättern.
 
Erst mal danke für die Antwort!

yaslaw hat gesagt.:
Vergleichst du bei 2) und 3) immer nur innerhalb des selben Jahres? Also 201001 mit 2001002 aber nie 201002 mit 201101.
Zum Vergrößern anklicken....
Leider nein. Sind immer die letzten beiden Einträge. Also aktuell wäre es 201101 mit 201002. In nem halben Jahr 201102 mit 201101.

Vielleicht noch etwas zum Hintergrund: Greife mit C# auf die DB zu. Dazu habe ich u. a. eine Funktion, die aus dem aktuellen Datum ein Format à la 201101 erstellt. Die obige Unterscheidung sollte man aber auch irgendwie hinkriegen können. Zum Vergleich dann vielleicht so was wie:
MAX(jahr) - MAX((MAX(jahr)-IF(MAX(jahr)%2 = 0, 1, 99))

IF gibts ja in MySQL?! Habs nur noch nie benutzt.

Table-Create-Script geht leider net ^^ hab das mit phpmyadmin zusammengeklickt.

Aber ich versuchs so (PK mit ==, FK mit ---):

Tabelle spieler:
Code: 
id  vereinId  nachname  vorname  position  alter  landId
==  --------                                      ------
135	88	      Tillo 	Hans	 Torwart   31	  22
136 89        Mueller   Peter    Stuermer  21     111

Tabelle staerke:
Code: 
staerkeId  staerke  jahr
=========
1          1        201002
2          2        201002
3          3        201002
4          4        201002
5          5        201002
6          6        201002
7          7        201002
8          8        201002
9          9        201002
10         10       201002
11         1        201101 
12         2        201101
13         3        201101
14         4        201101
15         5        201101
Die hab ich nur eingeführt, um nicht bei jedem Spieler mit Stärke 1 zum Jahr 201002 jeweils den Eintrag zu haben, sondern nur die Referenz (haben ja 1000 Spieler bspw. Stärke 1, 1000 Stärke 2 etc.). War im Nachhinein vielleicht nicht so klug, da Komplexität erhöht wurde.

Tabelle entwicklung:
Code: 
spielerId  staerkeId
=========  =========
---------  ---------
135        1
135        11
136        2
136        11

Konkret würde das bedeuten: Spieler 135 hatte im Jahr 201002 Stärke 1 und auch im Jahr 201101 Stärke 1 --> Entwicklung = 0 (Fall 3)
Spieler 136 hatte im Jahr 201002 Stärke 2 und im Jahr 201101 Stärke 1 --> Entwicklung = -1 (Fall 2)
 
Zuletzt bearbeitet:
Mir ist grade noch ne Idee gekommen:

SQL: 
SELECT s1.id
FROM (
spieler as s1
INNER JOIN entwicklung
ON s1.id = entwicklung.spielerId
)
LEFT JOIN staerke 
ON entwicklung.staerkeId = staerke.staerkeId
WHERE s1.alter = 18 and
(SELECT staerke.staerke
FROM (
spieler as s2
INNER JOIN entwicklung
ON s2.id = entwicklung.spielerId
)
LEFT JOIN staerke 
ON entwicklung.staerkeId = staerke.staerkeId
WHERE staerke.jahr = 201101
and s2.id = s1.id)
 >
(SELECT staerke.staerke
FROM (
spieler as s3
INNER JOIN entwicklung
ON s3.id = entwicklung.spielerId
)
LEFT JOIN staerke 
ON entwicklung.staerkeId = staerke.staerkeId
WHERE staerke.jahr = 201002
and s3.id = s1.id)

Das ginge wohl noch einfacher, wenn man die 3-fach gejointe Tabelle mit nem Alias versehen könnte. Scheint aber zu funktionieren.
Jemand Optimierungsmöglichkeiten? Das ist ja übel unperformant ^^

Dadurch ginge es ... mit > (1), < (2), = (3), < MAX (4).
 
Zuletzt bearbeitet von einem Moderator:
Solche SQL-Spielereien sind eine kleine Leidenschaft von mir. Hier, mein 2ter Ansatz ist eminer Meinung nach ganz gut gelungen
Du brauchst keine LEFT JOIN. Nur INNER JOINs
SQL: 
SELECT 
	s.*,
	-- Vergleich der letzten 2 Perioden (Aufgaben 1-3)
	(p1.staerke > p2.staerke) AS wurde_staerker,
	(p1.staerke < p2.staerke) AS wurde_schwaecher,
	(p1.staerke = p2.staerke) AS blieb_gleich,
	-- oder über ein einfacher vergleich: -1 heisst, er wurde schwächer, 0 er blieb gleich, 1 er wurde stärker
	STRCMP(p1.staerke, p2.staerke) AS compare,
	-- Vergleich zu allen alten Perioden (Aufgabe 4)
	(p1.staerke < allP.max_staerke) AS wurde_schwaecher_ueber_alles
FROM
	spieler AS s
	INNER JOIN (
	--Subselect mit den Daten der aktuelsten Periode
			SELECT
				e.spielerid,
				s.*
			FROM
				staerke s
				INNER JOIN entwicklung e
					ON s.staerkeid = e.staerkeid
				--Mit diesem Inner JOIN reduzieren wir dei Datenmenge auf die letzte Periode
				INNER JOIN (SELECT DISTINCT jahr FROM staerke ORDER BY jahr DESC LIMIT 0,1) AS periodes
					ON s.jahr = periodes.jahr
		) AS p1
		ON  s.id = p1.spielerid
	INNER JOIN (
	-- Subselect mit der Vorletzten Periode
			SELECT
				e.spielerid,
				s.*
			FROM
				staerke s
				INNER JOIN entwicklung e
					ON s.staerkeid = e.staerkeid
				--Mit diesem Inner JOIN reduzieren wir dei Datenmenge auf die zweit letzte Periode
				INNER JOIN (SELECT DISTINCT jahr FROM staerke ORDER BY jahr DESC LIMIT 1,1) AS periodes
					ON s.jahr = periodes.jahr
		) AS p2
		ON  s.id = p2.spielerid
	INNER JOIN (
	-- Subselect mit allen Perioden ausser der aktuellsten
			SELECT
				e.spielerid,
				MAX(s.staerke) as max_staerke
			FROM
				staerke s
				INNER JOIN entwicklung e
					ON s.staerkeid = e.staerkeid	
				-- zusätzlich noch unterbinden, dass die erste Periode mit drin ist
				LEFT JOIN (SELECT DISTINCT jahr FROM staerke ORDER BY jahr DESC LIMIT 0,1) AS periodes
					ON s.jahr = periodes.jahr
			WHERE
				periodes.jahr IS NULL
			GROUP BY
				e.spielerid
		) AS allP
		ON  s.id = allP.spielerid
 
Zuletzt bearbeitet von einem Moderator:
Wow. Vielen Dank für diese mächtige Lösung :)

Fühle mich erst mal erschlagen.

Also meine Methode habe ich grade implementiert, die funktioniert. Allerdings relativ langsam.

Sobald ich wieder dazukomme, werde ich mit deiner Lösung etwas herumspielen und dann die hoffentlich beste präsentieren können :)

Danke nochmals!
 
Um antworten zu können musst du eingeloggt sein.

Neue Beiträge

Zurück