Doppelte Sortierung einer mysql abfrage über die ganze tabelle

[Atmog]

Hi,
als erstes: Ja, ich habe die SUFU benutzt, aber bin leider auf nichts gestoßen, was ich direkt zur Lösung meines Problems anwenden könnte. Ich hoffe ihr könnt mir helfen. Ich schilder mal das Problem:

Ich habe eine Mysqltabelle mit dem namen "ergebnisse" und diese hat folgende spalten "datum,hans,karl,dieter,jörg". jetzt stehen in den spalten bowlingergebnisse. pro datum macht das je 3 zahlen pro person

Datum-|-Hans-|-Karl-|-Dieter ....
1.1.10--|-120--|-110-|-105...
1.1.10--|-130-|-103--|-....
1.1.10--|-120-|-...
6.1.10--|...
6.1.10...
...

jetzt möchte ich als Ergebnis meiner Abfrage eine Rangfolge haben, die mir die höchsten 10 Ergebnisse aus der gesamten Tabelle liefert. Weiterer Knackpunkt ist der Fall, wenn Ergebnisse unter den Top10 gleich sind. Diese sollen dann nach Datum sortiert werden. Hierbei soll gelten, was älter ist kommt als erstes.

PHP-Code:

    $sql = "SELECT
                *
            FROM
                ".$project_ergebnisliste."
            ORDER BY

                Datum DESC
            LIMIT
                10";

    $result = mysql_query($sql) OR die(mysql_error());
    if (mysql_num_rows($result)){
    while ($row = mysql_fetch_assoc($result)) { 


...
ich bin mir nicht sicher wie ich bei order by, nach der größe sortiere und wie ich als feld die komplette tabelle angeben kann. ich hoffe ihr könnt mir helfen.

gruß atmog

[thomas_w]

Deine bisherige Tabelle ist leider nicht "normalisiert". Dann tut sich SQL ziemlich schwer, das gewünschte Ergebnis zu liefern. Zudem wird es schwierig, wenn einer der Mitspieler ausscheidet bzw. neue hinzu kommen.

Datum-|-Hans-|-Karl-|-Dieter ....
1.1.10--|-120--|-110-|-105...
1.1.10--|-130-|-103--|-....
1.1.10--|-120-|-...
6.1.10--|...
6.1.10...
...

Besser wäre es, die bisherigen Daten in zwei Tabellen aufzuteilen z.B. so:

CREATE TABLE person (
 id INT NOT NULL,
 name VARCHAR(50),
 PRIMARY KEY (id)
);

INSERT INTO person VALUES
( 1, 'Hans'),
( 2, 'Karl'),
( 3, 'Dieter');

CREATE TABLE ergebnis (
 id INT NOT NULL,
 datum DATE NOT NULL,
 person_id INT NOT NULL,
 ergebnis INT NOT NULL,
 PRIMARY KEY (id)
);

INSERT INTO ergebnis VALUES
( 1, '2010-01-01', 1, 120),
( 2, '2010-01-01', 2, 110),
( 3, '2010-01-01', 3, 105);

Mit diesen Tabellen sind die gewünschten Ergebnisse "relativ" leicht ermittelbar.

Grüße
Thomas

[Atmog]

Hi,
danke für deine schnelle Hilfe. Die Normalisierung hat mich schon vorran gebracht, auch wenn die Tabelle jetzt wahrscheinlich 600 Zeilen am Ende des Jahres haben wird. Hoffe das geht noch mit der Verarbeitungsgeschwindigkeit. Eventuell melde ich mich nochmal, wenn ich beim weiteren Vorgehen auf "scheinbar unlösbare" Probleme stoße ;)

Also nochmal vielen Dank!

Gruß Atmog

[Atmog]

Hi,
ich bin doch noch auf ein Problem gestoßen. Die Rangliste mit ner Abfrage zu generieren klappt jetzt mit der neuen Tabellenstruktur wunderbar, jetzt möchte ich aber noch nen Gesamtschnitt der Spieler haben und diese der Größe nach sortieren.

120,4 Spieler1
110,2 Spieler 2
96,4 Spieler 3

soll dabei rauskommen. Ich würde ungern noch ne weitere Tabelle machen, daher muss die Lösung evtl. in der Abfrage+Verarbeitung liegen? Bin nicht so fit in PHP, aber man kann glaub ich auch iwie Arrays sortieren? Bin aber nicht sicher wie ich das Ergebnis der Abfrage vll. in nem Array abspeichern kann. Vll. wisst ihr ja ein Lösung.

Gruß Atmog

[thomas_w]

Was verstehst Du unter "Gesamtschnitt" ?

so was..? Hier wird das durchschnittliche Ergebnis alle Spieler berechnet und absteigend sortiert (Der höchste Durchschnitt steht oben).

SELECT p.name, AVG(ergebnis) AS durchschnitt
  FROM person p
  JOIN ergebnis e
    ON e.person_id = p.id
GROUP BY p.name
ORDER BY durchschnitt DESC;

+--------+--------------+
| name   | durchschnitt |
+--------+--------------+
| Hans   |     120.0000 |
| Karl   |     110.0000 |
| Dieter |     105.0000 |
+--------+--------------+
3 rows in set (0.00 sec)

mysql>

Grüße
Thomas

[Atmog]

Hi,
war gar nicht so einfach da durchzusteigen, kannte den Join Befehl nicht, aber ein Thread weiter oben mit nem Bild hats mir dann quasi erklärt. Es funktioniert. Vielen Dank dafür. Und ja mit Gesamtschnitt war der Durchschnitt gemeint.
Eine letzte Frage hätte ich noch. ( ja letzte ;) )
Ich habe jetzt die Struktur der Tabellen wie oben und möchte noch eine letzte Abfrage machen. Ich möchte die besten z.b. 6 Tagesdurchschnitte (Limit 6) der Größe nach mit zugehörigem Namen ausgeben. Unter Tagesdurchschnitt verstehe ich wenn jemand am 2.1.10 130,120,110 bowlt dann 120 für den spieler an dem tag. so gibt es für jeden spieler an jedem tag einen durchschnitt und aus diesem pool sollen die 6 größten raussortiert werden und die namen zugeordnet werden. die ergebnisse sollen dabei erst nach größe, dann nach datum(alt zuerst,ASC) und dann nach id sortiert sein.
Ich denke ich kann das mit ner zusätzlichen Tabelle realisieren, wo ich die durchschnitte eintrage, weil dann hätte ich ja in etwa die gleiche fragestellung wie oben. Aber 2 Tabellen wo eigentl. die gleichen Daten drin sind, kann ja nicht optimal (oder normalisiert? ;) ) sein. Vll. weisst du da ja ne bessere Lösung.

Gruß Atmog

[thomas_w]

Die sechs größten "Durchschnitte" pro Name oder über alle Namen?

SELECT p.name, e.datum, AVG(e.ergebnis) AS durchschnitt
  FROM person p
  JOIN ergebnis e
    ON e.person_id = p.id
GROUP BY p.name, e.datum
ORDER BY p.name, e.datum, durchschnitt DESC
LIMIT 6;

+--------+------------+--------------+
| name   | datum      | durchschnitt |
+--------+------------+--------------+
| Dieter | 2010-01-01 |     105.0000 |
| Hans   | 2010-01-01 |     120.0000 |
| Karl   | 2010-01-01 |     110.0000 |
+--------+------------+--------------+
3 rows in set (0.03 sec)

mysql>

Grüße
Thomas

[urvater]

Zitat von Atmog

... Die Normalisierung hat mich schon vorran gebracht, auch wenn die Tabelle jetzt wahrscheinlich 600 Zeilen am Ende des Jahres haben wird. Hoffe das geht noch mit der Verarbeitungsgeschwindigkeit.....

Also bei dem was du zur Zeit hier an Abfragen hast wirst du bei 600 Einträgen pro Jahr nicht so schnell Performanceprobleme bekommen. ;)