MySQL ranking automatisch sortieren

cl90

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

nman

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.

cl90

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

nman

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.

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.