1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies. Weitere Informationen
  2. Willkommen im Forum für alle Datenbanken! Registriere Dich kostenlos und diskutiere über DBs wie Mysql, Oracle, Sql-Server, Postgres, Access uvm
    Information ausblenden

ermitteln des Server besten aus zwei Tabellen?

Dieses Thema im Forum "MySQL" wurde erstellt von xpkiller, 14 Februar 2012.

  1. xpkiller

    xpkiller Benutzer

    Hallo hier im Forum,
    sql ist noch ziemlich neu für mich und ich tue mich da etwas schwer. Ich habe schon einiges realisieren können, doch nun komme ich nicht weiter. Ich habe eine SQL - Datenbank mit 7 Tabellen (challenges, transaction, players, players_cache, records, teams, und votes). Ich möchte nun mit php den Server besten aus zwei Tabellen (players und records) ermitteln. Die Tabelle players hat 5 Spalten ( Id, Login, NickName, UpdatedAt, Wins, TimePlayed, TeamId) die Tabelle records hat 6 spalten (Id, challengeId, PlayerId, Score, CheckPoints, Date).
    Ich habe folgendes gemacht:
    PHP:
     
         
    //  $db = Datenbankverbindung ist hergestellt
     
     
          
    $SQL_Aabfrage $db->query("SELECT T1.*, T2.* FROM players T1, records T2
                                      ORDER BY T2.PlayerId DESC LIMIT 1"
    );
          
    $SQL_Aabfrage->setFetchMode(PDO::FETCH_ASSOC);
     
          
    $Ergebnis $SQL_Aabfrage->fetchAll();
     
          if(
    count($Ergebnis) > 0) {
          foreach(
    $Ergebnis as $Daten) {
              
    $Anzeige1 $Daten['NickName'];
              
    $Anzeige2 $Daten['Wins'];
              
    $Anzeige3 $Daten['PlayerId'];
            }
     
            }
     
        
    // die Ergebnisse werden dann mit $Anzeige1, $Anzeige2 und $Anzeige3 ausgegeben.
     
    Doch irgend wie stimmt das alles nicht so ganz, es wird der NickName, Wins und PlayerId von records angezeigt, aber die Ergebnisse stimmen nicht. Irgendwas mache ich doch da falsch?
    Kann mir da jemand weiter helfen?
    Gruß xpkiller
     
  2. ukulele

    ukulele Datenbank-Guru

    Wie würde sich denn der "Server Beste" ermitteln? Derzeit sortierst du ja nach PlayerID, was irgendwie sinnfrei wirkt. Ich denke mal jeder Player hat mehrere Records von denen einige oder alle in die Bewertung einfließen. Wie das aber von statten gehen soll fehlt in deinem Code. Es gibt lediglich die Spalte Wins die aber bereits in Players steht, ist dort schon der ermittelte Wert drin?
     
  3. xpkiller

    xpkiller Benutzer

    Ja, ich weiß, das ist nicht so wie es sein soll. Ich habe mich aber auch vielleicht etwas falsch ausgedrückt. ich möchte den Server besten ermitteln, anhand der gefahrenen, bzw. gewonnenen Rennen (Wins), den Namen (NickName) und der gefahrenen Rekorde in der Tabelle "records", anhand der PlayerId für den "NickName aus der Tabelle players"
    Leider weiß ich nicht, wie ich das realisieren kann, ich komme da nicht weiter. Müssen die (Score) und (challengeId) aus der Tabelle "records" auch irgendwie mit eingeschlossen werden?
    Code:
    TABELLE `players` (
      `Id` mediumint(9) unsigned NOT NULL AUTO_INCREMENT,
      `Login` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
      `NickName` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
      `UpdatedAt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
      `Wins` mediumint(9) unsigned NOT NULL DEFAULT '0',
      `TimePlayed` mediumint(9) unsigned NOT NULL DEFAULT '0',
      `TeamId` mediumint(9) unsigned DEFAULT NULL,
      PRIMARY KEY (`Id`),
      UNIQUE KEY `Login` (`Login`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
    
    Code:
    TABELLE `records` (
      `Id` mediumint(9) unsigned NOT NULL AUTO_INCREMENT,
      `ChallengeId` mediumint(9) unsigned NOT NULL DEFAULT '0',
      `PlayerId` mediumint(9) unsigned NOT NULL DEFAULT '0',
      `Score` mediumint(9) unsigned NOT NULL DEFAULT '0',
      `CheckPoints` text COLLATE utf8_unicode_ci,
      `Date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
      PRIMARY KEY (`Id`),
      UNIQUE KEY `ChallengeId` (`ChallengeId`,`PlayerId`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
    
    Vielleicht hilft das ein bisschen weiter?
    Gruß xpkiller
     
  4. ukulele

    ukulele Datenbank-Guru

    Ok ich verstehe dich besser. Ich kann dir mal den Code für die Ermittlung des Besten nach "Wins" geben, der ist einfach.
    Code:
    SELECT    p.NickName,
            p.Wins,
            p.PlayerID
    FROM    players p,
            records r
    WHERE    p.PlayerID = r.PlayerID
    ORDER BY p.Wins DESC
    LIMIT 1
    In der Records Tabelle bin ich mir nicht sicher, wie die Daten aussehen. Ich vermute mal für jede ChallangeID gibt es mehrere Einträge und der höchste ist der Rekord. Ohne Testdaten kann ich das nur so versuchen, eventuell musst du noch das ein oder andere ändern:
    Code:
    SELECT    p.NickName,
            max(r.Score) AS Score,
            r.ChalangeID,
            p.PlayerID
    FROM    players p,
            records r
    WHERE    p.PlayerID = r.PlayerID
    GROUP BY r.ChalangeID,p.PlayerID,p.NickName
    HAVING max(r.Score)
     
  5. ukulele

    ukulele Datenbank-Guru

    Für Wins musst du natürlich nur die Players Tabelle abfragen, der Join auf Records ist eigentlich unnötig. Später kann man das aber mit Score kombinieren, wenn der Code funktioniert.
     
  6. xpkiller

    xpkiller Benutzer

    Vielen Dank ukulele, das hat mir sehr geholfen. Ich habs jetzt hinbekommen,
    Code:
    "SELECT a.Score, b.NickName, b.Wins,
    (SELECT COUNT(Id) FROM records WHERE PlayerId = a.Id)+0 AS Records
    FROM records AS a
    LEFT JOIN players AS b
    ON a.PlayerId = b.Id
    WHERE a.ChallengeId = b.Id
    ORDER BY a.Score , a.Date, a.PlayerId
    LIMIT 0,10"
    
    Auf Join konnte ich aber nicht verzichten, so funktioniert es bei mir einwandfrei.
    Gruß xpkiller

    EDIT am 06.03.2012:
    Ich habe mit den Einstellungen noch etwas experimentiert und habe nun den Code nochmals geändert,so wird es jetzt richtig angezeigt:
    PHP:
          $SQL_Aabfrage $db->query("SELECT p.Wins, p.NickName, max(r.Score) AS Score, r.ChallengeId,
                                      (SELECT COUNT(Id) FROM _records r WHERE PlayerId = p.Id)+0 AS Records
                                      FROM _players p, _records r
                                      WHERE p.Id = r.PlayerId
                                      GROUP BY r.ChallengeId,p.Id,p.NickName
                                      HAVING max(r.Score)"
    );
                             
         
     
     
     
          
    $SQL_Aabfrage->setFetchMode(PDO::FETCH_ASSOC);
     
          
    $Ergebnis $SQL_Aabfrage->fetchAll();
     
          if(
    count($Ergebnis) > 0) {
          foreach(
    $Ergebnis as $Daten) {
              
    $Anzeige1 $Daten['NickName'];
              
    $Anzeige2 $Daten['Wins'];
              
    $Anzeige3 $Daten['PlayerId'];
              
    $Anzeige4 $Daten['Records'];
            }
     
            }
     
  7. xpkiller

    xpkiller Benutzer

    Nun habe ich noch ein kleines Problem, nach dem es gelungenen ist den Server Besten jetzt richtig an zu zeigen, wollte ich schlau sein und mal eben die beste Challenge auf dem Server mit den meisten Bewertungen und Stimmen anzeigen.
    Ich habe wieder zwei Tabellen, aus der gelesen wird (_challenges und _votes). Die Tabellen sehen wie folgt aus:
    _challenges` (
    `Id` mediumint(9) unsigned NOT NULL AUTO_INCREMENT,
    `Uid` varchar(27) COLLATE utf8_unicode_ci NOT NULL,
    `Name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
    `Author` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
    `Environment` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
    `GoodRating` mediumint(9) unsigned DEFAULT '0',
    `BadRating` mediumint(9) unsigned DEFAULT '0',
    PRIMARY KEY (`Id`),
    UNIQUE KEY `Uid` (`Uid`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

    _votes` (
    `Id` mediumint(9) unsigned NOT NULL AUTO_INCREMENT,
    `PlayerId` mediumint(9) unsigned NOT NULL,
    `ChallengeId` mediumint(9) unsigned NOT NULL,
    `Score` tinyint(1) NOT NULL DEFAULT '0',
    PRIMARY KEY (`Id`),
    UNIQUE KEY `Vote` (`PlayerId`,`ChallengeId`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;


    Dazu habe ich diesen Code eingegeben, es wird auch etwas angezeigt, nur entspricht es nicht der Wahrheit?
    PHP:
          $SQL_Aabfrage $db->query("SELECT p.Environment, p.Name, SUM(r.Score) AS Score, COUNT(r.Score) AS Count, r.ChallengeId, p.Uid
                                      FROM _challenges p, _votes r
                                      WHERE p.Uid = r.PlayerId
                                      GROUP BY r.PlayerId, r.Id, p.Uid, p.Name
                                      HAVING SUM(r.Score)"
    );
     
     
     
     
          
    $SQL_Aabfrage->setFetchMode(PDO::FETCH_ASSOC);
     
          
    $Ergebnis $SQL_Aabfrage->fetchAll();
     
          if(
    count($Ergebnis) > 0) {
          foreach(
    $Ergebnis as $Daten) {
              
    $Anzeige1 $Daten['Name'];
              
    $Anzeige2 $Daten['Count'];
              
    $Anzeige3 $Daten['Environment'];
              
    $Anzeige4 $Daten['Score'];
            }
     
            }
    Irgend wo habe ich da wohl einen Denkfehler?
    Ich habe auch schon mit den Daten gespielt, aber ich komme nicht drauf?
    Es soll die Challenge (p.Name) mit den meisten Bewertungen (r.PlayerId) und erhaltenen Stimmen (r.Score) angezeigt werden.

    Gruß xpkiller
     
  8. ukulele

    ukulele Datenbank-Guru

    Also ohne mich jetzt mit der Struktur befasst zu haben spuckt mir mein SQL Editor schonmal Fehler aus bei deinem Code:

    HAVING sum(r.score) ist kein Boolean Wert, max(r.score) wie im Beispiel davor möglicherweise schon weil es sich vermutlich auf das Maximum der Tabelle bezieht. Du musst irgendetwas angeben das wahr oder falsch ist.

    Alternativ kann es noch an Spaltennamen wie "Count" liegen, die eventuell als Funktion interpretiert werden. Abhilfe schafft das Klammern solcher Namen als [Count].

    Häufige Ursache von zunächst blödsinnig wirkenden Ergebnissen sind dann noch die NULL Werte, wenn z.B. r.PlayerID oder Uid irgendwo mal NULL sind.
     
  9. xpkiller

    xpkiller Benutzer

    Ich habe noch mal einiges probiert, aber ich bringe es nur zusammen, das jetzt etwas angezeigt wird was zusammen passt. Doch es ist keineswegs die Challenge mit den meisten Bewertungen. Hier noch mal mein jetziger Code:
    PHP:
    SELECT p.Uidp.Idp.Environmentp.NameMAX(r.Score) AS ScoreMAX(r.PlayerId) AS Count
                                      FROM _challenges 
    AS p_votes AS r
                                      WHERE r
    .ChallengeId p.Id
                                      GROUP BY r
    .ChallengeIdr.PlayerIdp.Idp.Uidp.Name
                                      HAVING MAX
    (r.Score)
    Irgend wie ist doch da der Wurm drin?
    Wie ermittle ich nur die Challenge mit der höchsten Bewertung ?
    Gruß xpkiller
     
  10. ukulele

    ukulele Datenbank-Guru

    Wie genau ist das denn aufgebaut, du ermittelst immer nur einen Spieler oder die Top 10? Es bietet sich ja an dort mit LIMIT und ORDER BY zu arbeiten aber ich blicke nicht durch die Tabellenstruktur.

    Code:
    SELECT    p.[Uid],
            p.Id,
            p.Environment,
            p.Name,
            sum(r.Score) AS Score
    FROM    challenges p,
            _votes r
    WHERE    r.ChallengeId = p.Id
    GROUP BY r.ChallengeId, r.PlayerId, p.Id, p.[Uid], p.Name, p.Environment
    ORDER BY sum(r.Score)
    LIMIT 1
    .. das kann aber auch Blödsinn sein. Ein ERD mit allen Tabellen und Relationen ist manchmal hilfreich :)
     
  11. xpkiller

    xpkiller Benutzer

    Ich will mal versuchen,das zu erklären. In der Tabelle _votes sind folgendeSpalten;
    1: Id= Vortlaufende Nummerierung,
    2: PlayerId = Hier sind nur die NR, der Player(aus der Tabelle _players) aufgelistet, die eine Bewertung abgegeben haben,
    3: ChallengeId = Hier wir die ChallengeId = Nummerierung der Challenges aus der Tabelle _Challenges
    4: Score = Hier werden die abgegebenen Stimmen der Spieler abgelegt(1-5)

    Ich habe jetzt in der Tabelle _votes noch eine Spalte hinzugefügt:
    5: ChallengeName = Hier wird nun der Name der Challenge abgelegt die bewertet wurde
    Für jeden Spieler, der eine Bewertung abgegeben hat ist nun eine Spalte mit:
    Id, PlayerID, ChallengeID, ChallengeName; Score vorhanden, dann brauch nur aus einer Tabelle gelesen werden.
    Nun möchte ich die am besten Bewertete(PlayerId) und mit den meisten Stimmen(Score) Challenge mit dem ChallengeNamen anzeigen lassen. Sie muss natürlich auch ,wenn sich etwas an der Bewertung ändert, die aktuell beste dann anzeigen.
    Das soll dann zum Beispiel so aussehen:

    Server beste Challenge:
    Challenge: ChallengeName
    Bewertungen: 3(von 3Spielern wurde diese Challenge Bewertet) mit 8 Stimmen(Player1=2, Player2=5, Player3=1)= (Summe der Punke von Score für diese Challenge)
    Environment:

    Ich hoffe das ist so etwas verständlicher?
    Gruß xpkiller
     
  12. ukulele

    ukulele Datenbank-Guru

    Code:
    SELECT    c.ChallangeName,
            p.Name,
            count(*) AS [Anzahl Bewertungen],
            sum(v.Score) AS [Gesamtpunkte],
            avg(v.Score) AS [Durchschnitt]
    FROM    players p,
            challenges c,
            _votes v
    WHERE    v.PlayerId = p.Id
    AND        v.ChallangeId = c.Id
    GROUP BY c.ChallangeName,p.Name
    ORDER BY sum(v.Score) DESC
    LIMIT 1
     
  13. xpkiller

    xpkiller Benutzer

    Hallo ukulele und vielen Dank, für deine Bemühung, es wird mit diesem Code zwar auch eine Challenge mit Bewertung und Punkte angezeigt, aber das ist auch nicht die am besten bewertete?
    Code:
    SELECT p.NickName, count(*) AS Count,
                                      sum(v.Score) AS Score,
                                      avg(v.Score) AS Score1,
                                      c.Name As ChallengeName
                                      FROM _players p,
                                            _challenges c,
                                            _votes v
                                      WHERE v.PlayerId = p.Id
                                      AND  v.ChallengeId = c.Id
                                      GROUP BY c.Name,p.NickName
                                      ORDER BY sum(v.Score) DESC
                                      LIMIT 1  
    Ich habe auch schon mit den Werten gespielt, aber ich bringe es auch nicht dazu, die beste anzuzeigen?
    Vielleicht muss ich an meiner Tabellenstruktur was ändern?
     
  14. ukulele

    ukulele Datenbank-Guru

    Dann vieleicht ORDER BY avg(v.Score) oder was ist für dich "die beste" Bewertung? Teste doch mal die Ausgabe ohne LIMIT und verändere die Sortierung.

    Was willst du an der Tabellenstruktur ändern und warum?
     
  15. xpkiller

    xpkiller Benutzer

    Hi ukulele, ich habe noch mals einige Dateibeispiele durchgeführt und ich glaube ich habs jetzt hin bekommen. :)
    Ich habe mit dem Code von dir ein bischen rumgespielt und nun wird die beste Challenge des Servers angezeigt:
    Code:
    SELECT v.ChallengeId, count(*) AS Count,
                                      sum(v.Score) AS Score,
                                      avg(v.Score) AS Score1,
                                      c.Name As ChallengeName,
                                      c.Environment
                                      FROM _players p,
                                            _challenges c,
                                            _votes v
                                      WHERE v.PlayerId = p.Id
                                      AND  v.ChallengeId = c.Id
                                      GROUP BY c.Name,v.ChallengeId
                                      ORDER BY sum(v.Score) DESC
                                      LIMIT 1  
    Mann oh Mann, das hat aber Geduld gekostet. Ich danke dir für deine Hilfe;)
    Gruß xpkiller
     
Die Seite wird geladen...

Diese Seite empfehlen