Kreuztabelle ähnlich wie in Access

[Stefan-Maverick]

Hallo,

ich habe eine eine Access Datenbank. Damit ich die Seiten ins Internet stellen kann, wandle ich die gerade in Mysql um. Klappt soweit auch ganz gut, bis auf ein Problem mit einer Kreuztabelle.

tabErgebnisse (NummerID_F, WettkampfID_F, Aklasse, Jahr, Ergebnis, Rang)(alles Integer)
tabStammdaten (NummerID, Nachname, Vorname)
tabWettkampf (WettkampfID,Wettkampf)

NummerID ist mit NummerID_F zu verknüpfen, WettkampfID mit WettkampfID_F.
Sortieren nach Ergebnis1,2,3,4 jeweils absteigend
Gruppieren nach ?

In Access sah das so aus:

TRANSFORM First(tabErgebnisse.Ergebnis) AS ErsterWertvonErgebnis
SELECT tabStammdaten.AKlasse, tabStammdaten.Nachname, tabStammdaten.Vorname
FROM tabErgebnisse INNER JOIN tabStammdaten ON tabErgebnisse.NummerID_F = tabStammdaten.NummerID
GROUP BY tabStammdaten.AKlasse, tabStammdaten.Nachname, tabStammdaten.Vorname
PIVOT "Ergebnis: " & [Rang] In ("Ergebnis: 1","Ergebnis: 2","Ergebnis: 3","Ergebnis: 4");

Der Hintergrund ist, in der Tabelle Ergebnisse stehen maximal 4 Ergebnisse mit der gleichen NummerID_F.
In der Spalte Rang steht die Zahl 1 bis maximal 4. Dadurch kann ich das höchste, zweithöchste ... Ergebnis auslesen. Über die Kreuztabelle bekomme ich dann zu jedem Teilnehmer die 4 vorhandenen Ergebnisse und kann die dann sortieren.

Beispiel:

NummerID_F Aklasse Ergebnis Rang
1_________1_______101010___1
1_________1________91010___2
1_________1________81010___3
2_________1_______101010___1
2_________1_______101010___2
2_________1________91010___3


soll zu folgender Zeile werden:
NummerID_F Aklasse Ergebnis1 Ergebnis2 Ergebnis3 Ergebnis4
2__________1_______101010___101010___91010
1__________1_______101010____91010___81010

WettkampfID_F (2) und Jahr (z.B. 2010) werden in einer anderen php Seite festgelgt und per get übertragen. Aklasse kann entweder 0 oder 1 sein, stellt aber kein Problem dar, da ich die Abfrage auch zweimal machen kann und die Ausgabe entsprechend anpassen kann. Ergebnis4 dient nur bei absoluter Gleichheit um die Plätze richtig zu belegen.

Ich hoffe mal ihr könnt mir weiterhelfen.
Auf jeden Fall schon mal im voraus.

Mfg
Stefan
Der Aufwand ist nötig, da ich in zwei Richtungen sortieren muss und dabei noch einen ganzen Haufen gleicher Ergebnisse habe. D.h. die Ergebnisse der einzelnen Teilnehmer müssen in der korrekten Reihenfolge sortiert sein. Außerdem muß ich dann nach Ergebnis1, 2, 3, 4 absteigend sortieren.

[cortex]

Zitat von Stefan-Maverick

In Access sah das so aus [...]

mysql kennt weder TRANSFORM, noch PIVOT. siehe auch handbuch: MySQL 5.1 Referenzhandbuch.

denke, du musst dir einen workaround einfallen lassen.

cx

[Stefan-Maverick]

Das war mir eigentlich schon klar, dass das nicht so ganz einfach wird.
Habe bis jetzt alle Eigenheiten der JetSQL irgendwie in Mysql verbauen können.

Nur hier hackt es gewaltig.
Das Problem ist eben, dass ich zum einen die Ergebnisse der Teilnehmer absteigend sortieren muss und dann noch die gesamten Ergebnisse absteigend sortieren muss.

Eine Variante wäre noch die Zahlen auf 6 stellig auffüllen und mit group_concat zusammenhängen.
Dann könnte ich zumindest mal die Gesamtsortierung hinbekommen.
Wie ich dann noch die einzelnen Ergebnisse zu den sortierten ID's bekomme steht allerdings noch in den Sternen.

Mein letzter Ansatz:

$sql = "select e.Jahr, e.wettkampfID_F, s.NummerID, s.Nachname, s. Vorname, e.NummerID_F as Nummern,
 group_concat(distinct e.Ergebnis order  by e.Ergebnis desc separator ';') as Ergebnisse
from tabErgebnisse e, tabStammdaten s where s.NummerID = e.NummerID_F
 and e.wettkampfID_F = 2 and e.Jahr = 2010 group by e.NummerID_F";

$ergebnis1 = mysql_query($sql) or die(mysql_error().'
	Query: '.$sql);
	echo "<div id=\"tab1\"><table width=100%><tr>";
	while($row = mysql_fetch_object($ergebnis1))
	   {
	echo "<tr><td>$row->Nummern</td><td>$row->Nachname</td><td>$row->Vorname</td><td>$row->Ergebnisse</td></tr>";
	   }
	echo "</table></div>";?>

Als Ergebnis kommt dann:
Nummer Nachname Vorname Ergebnis;Ergebnis;Ergebnis;Ergebnis

Wenn ich jetzt noch den Strichpunkt raus nehme und die Ergebnisse mit Nullen (vorne!) auffülle sollte eine 24 stellige Zahl raus kommen. (Ergebnis4 kommt nur sehr selten vor, wird aber trotzdem benötigt)

Vielleicht fällt jemand von euch da noch was ein.

Mfg
Stefan

[Stefan-Maverick]

Hab das nochmal durchdacht, der Ansatz könnte funktionieren.

Drei offene Fragen habe ich noch:

1. Wie bekomme ich es hin, dass die vorhandenen Ergebnisse 6 stellig werden
aus 8710 soll 008710 werden.
2. Es gibt maximal 4 Ergebnisse, es kann aber auch nur eines sein. Wie bekomme ich es hin, daß für die nicht vorhandenen Ergebnisse 000000 verwendet wird.
3. Wie kann ich in einer Abfrage nur die ersten, bzw. zweiten oder dritten 6 Ziffern einer Zahl ausgeben?
Natürlich dann ohne die vorher eingefügten Nullen.

Sollte dann so aussehen:
378 6810 999 wird mit Group_concat zu 006810000999000378000000
9910 91010 wird mit Group_concat zu 091010009910000000000000 (wenn die Nullen vorne wegfallen ist kein Problem)

Somit hätte ich beide Sortierungen drin.

Hat schon mal jemand hier sowas gemacht?
Mfg
Stefan

[Stefan-Maverick]

Hab einen Weg gefunden, falls jemand anderst auch noch so ein Problem hat:

$sql = "select e.Jahr, e.wettkampfID_F, s.NummerID, s.Nachname, s. Vorname, e.NummerID_F as Nummern, group_concat((1000000 + e.Ergebnis)  order  by e.Ergebnis desc separator '') as Ergebnisse
from tabErgebnisse e, tabStammdaten s where s.NummerID = e.NummerID_F and e.wettkampfID_F = 2 and e.Jahr = 2010 and Aklasse = 1 group by NummerID_F order by Ergebnisse DESC, Korrektur DESC";

$ergebnis1 = mysql_query($sql) or die(mysql_error().'
	Query: '.$sql);
	echo "<div id=\"tab1\"><table width=100%><tr>";
	while($row = mysql_fetch_object($ergebnis1))
	   {
	echo "<tr><td>$row->Nummern</td><td>$row->Nachname</td><td>$row->Vorname</td><td>$row->Ergebnisse</td></tr>";
	   }
	echo "</table></div>";?>

Ich habe in Access die Ergebnisse so präpariert, dass immer 3 Ergebnisse vorhanden sind. Die fehlenden Ergebnisse haben den Wert 0. Falls alle 3 Ergebnisse gleich sind, kann ich in Access von Hand in der Spalte Korrektur nachhelfen.
Die Abfrage spuckt dann eine absteigend sortierte Zahl aus mit 21 Stellen.
Mit Hilfe von if Abfragen und ein bißchen Berechnungen kann man die Zahl wieder auseinandernehmen.

Problem ist somit gelöst

Mfg
Stefan

[Stefan-Maverick]

Der vollständigkeit halber, der Versuch mit der Berechnung scheitert an ungenauen Berechnungen.
Habe es anderst gelöst:

$Ergebnis1 = ltrim(substr($row->Ergebnisse, -20, 6), '0');
$Ergebnis2 = ltrim(substr($row->Ergebnisse, -13, 6), '0');
$Ergebnis3 = ltrim(substr($row->Ergebnisse, -6, 6), '0');

Dadurch wird die 21stellige Zahl in 3 6 stellige Zahlen zerlegt. Mit ltrim fallen noch die führenden Nullen weg.


Mfg
Stefan