MySQL ranking automatisch sortieren

cl90

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.

nman

cl90 schrieb:

Wie funktioniert denn dieses SQL Fiddle?

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

edit: URL wird nicht sauber geparsed.

cl90

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.

nman

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;

cl90

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?

nman

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.

cl90

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.

nman

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.

cl90

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?

nman

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.