MySQL ranking automatisch sortieren



  • Hallo.

    Kann mir jemand sagen was an dieser Query nicht richtig stimmt?

    SELECT teamID FROM teamlist 
    WHERE tournID = "2" 
    ORDER BY COUNT(winnerID) ASC 
    FROM teambattlelist, teambattlelist 
    WHERE teamlist.teamID = teambattlelist.winnerID
    

    Der Fehler scheint hinter ASC zu liegen. Aber das habe ich so woanders schon gesehen..

    Die Querry soll mir die teamIDs die am Turnier "2" teilnehmen, sortiert anhand ihrer Siege geben.

    Die Tabellen dazu sehen so aus:

    Userlist:
    UserID     Username
    
    Teamlist:(TournID = TournamentID)
    teamID     TournID       Teamname     
    
    Memberslist:
    teamID     UserID
    
    TeamBattlelist:(WinID = Gewinner TeamID)
    winnerID      LooseID      TournID     Score    Game    Map   Mode  Date
    

    Gruß,
    Chris



  • Du hast zwei FROMs, tournID ist vmtl. kein String, du verwendest eine Aggregatfunktion, gruppierst aber nicht, …

    Kannst du mal auf sqlfiddle ein Minimalbeispiel deines Schemas mit ein paar Daten einstellen? Dann ist es leichter, dir zu helfen und du kannst auch selbst ein bisschen herumexperimentieren.



  • Also liegt es daran das ich quasi nach einer neuen Tabelle sortieren möchte.
    Aber wie macht man das dann?

    ich könnte dir vermutich einfacher direkt zugriff auf meine richtige datenbank geben, denn ich habe tabellen eigentlich immer über mysql Admin erstellt.
    kann man in diese SQL FIddle eine bestehende datenbank einbinden? dann wärs ja recht einfach.

    wenn du willst kann ich dir aber auch direkt zugriff auf meine db geben.



  • Außerdem kannst du nur nach dem sortieren, was du auch selektiert hast! Die Count-Anweisung muss also auch in die Select-Anweisung.
    Gruppieren ist nicht zwingend notwendig, aber sinnvoll.

    L. G.,
    IBV



  • cl90 schrieb:

    kann man in diese SQL FIddle eine bestehende datenbank einbinden? dann wärs ja recht einfach.

    Du kannst dir bei PHPMyAdmin die passenden DDL-SQL-Queries ausgeben lassen. Irgendwo bei den Export-Optionen kannst du dir einen Dump anzeigen lassen.

    wenn du willst kann ich dir aber auch direkt zugriff auf meine db geben.

    Nein danke, lieber auf sqlfiddle.

    Btw, ein passender Query könnte zB. so aussehen:

    select mnr, count(kennzahl) as anz_kennzahlen
      from kennzahlen
      group by mnr
      order by anz_kennzahlen;
    

    Tabellenstruktur:

    CREATE TABLE `kennzahlen` (
      `mnr` mediumint(7) unsigned zerofill NOT NULL,
      `kennzahl` varchar(16) COLLATE utf8_unicode_ci NOT NULL,
      KEY `mnr` (`mnr`),
      KEY `kennzahl` (`kennzahl`),
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
    

    Die DDL-SQL-Queries kannst du dir übrigens auch mit

    show create table deine_tabelle;
    

    anzeigen lassen.

    IBV schrieb:

    Außerdem kannst du nur nach dem sortieren, was du auch selektiert hast!

    Das stimmt nicht.



  • Wie funktioniert denn dieses SQL Fiddle?
    Ich hab jetzt die 3 Tabellen als SQL exportiert, aber irgendwie kriege ich die nicht in hochgeladen.

    CREATE TABLE IF NOT EXISTS `teambattlelist` (
    `teambattleID` int(11) NOT NULL,
      `winnerID` int(11) NOT NULL,
      `loserID` int(11) NOT NULL,
      `tournID` int(11) NOT NULL,
      `score` int(11) NOT NULL,
      `game` text NOT NULL,
      `map` text NOT NULL,
      `mode` text NOT NULL,
      `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;
    
    INSERT INTO `teambattlelist` (`teambattleID`, `winnerID`, `loserID`, `tournID`, `score`, `game`, `map`, `mode`, `date`) VALUES
    (1, 3, 2, 0, 1, 'CoD4', 'crossfire', 'Team Deathmatch', '2014-11-14 18:46:53'),
    (2, 9, 8, 2, 5, 'CoD4', 'crossfire', 'Team Deathmatch', '2014-11-15 12:17:24'),
    (3, 9, 10, 2, 6, 'CoD4', 'crash', 'Team Deathmatch', '2014-11-15 12:17:49'),
    (4, 10, 8, 2, 1, 'CoD4', 'crossfire', 'Team Deathmatch', '2014-11-15 12:18:17'),
    (5, 9, 11, 2, 4, 'CoD4', 'crash', 'Team Deathmatch', '2014-11-15 12:18:35'),
    (6, 8, 11, 2, 7, 'CoD4', 'crossfire', 'Team Deathmatch', '2014-11-15 12:19:10'),
    (7, 11, 10, 2, 5, 'CoD4', 'crash', 'Team Deathmatch', '2014-11-15 12:19:39');
    
    CREATE TABLE IF NOT EXISTS `teamlist` (
    `teamID` int(11) NOT NULL,
      `teamName` text NOT NULL,
      `tournID` int(11) NOT NULL,
      `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ;
    
    INSERT INTO `teamlist` (`teamID`, `teamName`, `tournID`, `date`) VALUES
    (2, 'homos', 2, '2014-11-14 13:36:13'),
    (3, 'das', 2, '2014-11-14 13:43:02'),
    (4, 'blaaaas', 2, '2014-11-14 13:43:22'),
    (5, 'keks', 1, '2014-11-14 13:47:18'),
    (6, 'hug', 1, '2014-11-14 16:18:27'),
    (7, 'Weasel', 2, '2014-11-14 19:03:07'),
    (8, 'Milfyway', 3, '2014-11-15 12:11:16'),
    (9, 'Gurkenrudel', 3, '2014-11-15 12:11:23'),
    (10, 'Pornys', 3, '2014-11-15 12:11:33'),
    (11, 'Gundalfs Elfen', 3, '2014-11-15 12:11:43');
    
    CREATE TABLE IF NOT EXISTS `memberlist` (
      `teamID` int(11) NOT NULL,
      `userID` int(11) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    
    INSERT INTO `memberlist` (`teamID`, `userID`) VALUES
    (2, 2),
    (3, 1),
    (3, 2),
    (3, 3),
    (4, 1),
    (4, 3),
    (7, 1),
    (8, 4),
    (8, 5),
    (9, 1),
    (9, 6),
    (11, 9),
    (11, 10),
    (10, 7),
    (10, 8);
    

    Die Namen sind jetzt sehr stumpf^^ aber so sehen meine random testwerte in den Tabellen immer aus 😃

    edit: ok habs herrausgefunden wie es geht.
    Ist dann aber keine hilfe für mich, sondern für die die mir helfen wollen 🙂



  • nman schrieb:

    IBV schrieb:

    Außerdem kannst du nur nach dem sortieren, was du auch selektiert hast!

    Das stimmt nicht.

    Hab das mit Gruppieren verwechselt. 🙂



  • cl90 schrieb:

    Wie funktioniert denn dieses SQL Fiddle?

    So: http://sqlfiddle.com/#!2/4654e5

    edit: URL wird nicht sauber geparsed.



  • ja aber das bringt mich nicht weiter. Ich kann auch SQL befehle bei phpMyAdmin eingeben und ausführen lassen.
    Jetzt kann ich nur woanders hunderte Versuche eintippen die alle nicht funktionieren.

    Also:

    SELECT teamID, count(winnerID) as wins FROM 
    teamlist, teambattlelist GROUP by teamID
    

    Das gibt jetzt eine liste aller TeamIDs und 7 aus. (weil einfach nur die 7 winner einträge gezählt wurden...)

    SELECT teamID, count(winnerID) as wins 
    FROM teamlist, teambattlelist 
    GROUP by teamID WHERE teamID = winnerID
    

    ist falsche syntax. Das WHERE darf da nicht stehen...

    Edit:
    rangetastet. So funktionierts:

    SELECT teamID, count(winnerID) as wins 
    FROM teamlist, teambattlelist 
    Where teamID = winnerID AND teambattlelist.tournID = "3" 
    GROUP by teamID 
    ORDER BY wins DESC
    

    Danke für die Hilfe

    Edit3:
    Wenn ich vorhabe noch mehr informationen zu erhalten, muss ich dann neue Querys machen?
    Ich würde z.b. jetzt auch gerne die verlohrenen Spiele zählen lassen.



  • 3 ist kein String, also brauchst du nicht auf "3" checken und der Join ist überflüssig, den brauchst du hier gar nicht:

    SELECT winnerID, count(winnerID) as wins
    FROM teambattlelist
    WHERE tournID = 2
    GROUP by winnerID
    ORDER BY wins DESC;
    


  • Ok.

    Wie gesagt, wenn ich jetzt weitere elemente haben möchte, muss ich dann eine neue Query machen? Oder wie könnte ich jetzt z.b. Zusätzlich nach verlohrenen Spielen suchen?

    mir fällt auch gerade auf, dass ohne einen Joint immer nur die Teams gelistet werden die mindestens einmal gewonnen werden. Wie bekomme ich denn jetzt
    eine vollständige liste?



  • cl90 schrieb:

    Wie gesagt, wenn ich jetzt weitere elemente haben möchte, muss ich dann eine neue Query machen?

    Die allermeisten Sachen kannst du vmtl. im selben Query machen.

    mir fällt auch gerade auf, dass ohne einen Joint immer nur die Teams gelistet werden die mindestens einmal gewonnen werden. Wie bekomme ich denn jetzt
    eine vollständige liste?

    Die Dinger heißen Joins; Joints helfen hier vmtl. nicht sehr viel weiter.

    Wenn du für jedes Team die Counts haben möchtest (auch für Teams mit 0 Siegen), brauchst du einen Join, ja.



  • Aber wie könnte ich die beiden Tabellen joinen?
    Ich habe ja das Problem das die TeamID aus der "teamlist" entweder in der winnerID oder in der loserID stehen kann. Meine Join tabellen haben meistens deutlich mehr Einträge als die ursprungstabellen selbst.

    Edit:
    Ich sehe aktuell keinen weg das mit einer einmaligen Query zu lösen. Liege ich damit falsch?

    Macht in diesem fall ein View sinn? Ich könnte eine view table erstellen die die teamIDs und eine weitere noch leere spalte beinhaltet, welche ich dann in einer zweiten updatequery mit den win counts fülle.



  • Views können die eigentlichen Querys einfacher halten, insofern auch als Anfänger keine schlechte Idee.

    Damit könntest du die Abfrage zB. so irgendwie zusammenbasteln:

    create view wonGames as
      select teamID, count(winnerID) as wonGames
      from teamlist t left outer join teambattlelist b on (t.teamID = b.winnerID)
      group by teamID;
    
    create view lostGames as
      select teamID, count(winnerID) as lostGames
      from teamlist t left outer join teambattlelist b on (t.teamID = b.loserID)
      group by teamID;
    
    select *
    from teamlist natural join wonGames natural join lostGames;
    

    Btw, teamlist und teambattlelist uä. sind ziemlich schlechte Namen.

    Ich würde die Tabellen einfach teams , battles uä. nennen. Die IDs jeweils einfach nur id und wenn du in battles die ID von teams referenzierst, nennst du die dortige Spalte team_id oä.

    Ich würde auch noch Foreign Key Constraints und ein paar Indizes dazupacken.



  • Ok.

    Wie lange bleibt ein View wenn er erstmal erstellt ist?
    Und habe ich das richtig gelesen, dass ein erstelltes View sich vor jeder SELECT abfrage dieses Views neu aktualisiert?
    Das würde bedeuten das ich das Create View nur einmal ausführen muss, bzw. teste ob es da ist, und sonst neu anlege?



  • cl90 schrieb:

    Wie lange bleibt ein View wenn er erstmal erstellt ist?

    Bis du die View wieder löscht.

    Und habe ich das richtig gelesen, dass ein erstelltes View sich vor jeder SELECT abfrage dieses Views neu aktualisiert?

    Eine View ist im Grunde nichts anderes als ein abgespeichertes Select.

    Der Query da oben ist übrigens hässlich; habe gerade gesehen, dass es viel schöner wäre, in den beiden Views keine Joins zu verwenden und den Left Outer Join dann im Select zu machen (NULLs durch 0 ersetzen). Damit sparst du dir zwei unnötige Joins.


Anmelden zum Antworten