[SQL allgemein] Zusammfassen zweier Spalten anhand einer ID

[Gast]

Hi!
Ich glaube die Überschrift kann das Problem nicht ganz ausdrücken, es lässt sich aber auch nicht so einfach umschreiben.

Jedenfalls hier erst einmal die Voraussetzungen: Ich bin dabei für ein Spiel ein Statistiksystem zu erstellen, wie es ja bei den meisten modernen MP-Spielen vorhanden ist. Deswegen habe ich folgende Tabelle:

kills
id, match_id, killer_id, killed_id, n

id dient der Identifizierung des Datensatzes
match_id ist eine Zahl, die anzeigt, in welcher Spielrunde die Kills stattgefunden haben.
killer_id steht für den Spieler, der einen anderen abgeschossen hat und killed_id steht für den Abgeschossenen.
n bezeichnet, wie oft in diesem Match killer_id den Mitspieler killed_id abgeschossen hat.

Soweit so gut. Nun kommt das, was ich gern als Abfrageergebnis hätte.
Als Vorgabe habe ich die match_id. Nun möchte ich in meinem Abfrageergebnis eine Auflistung haben, wie oft jeder Spieler getroffen hat und wie oft er abgeschossen wurde. Das wird dann noch mit einer Namenstabelle gejoint usw, aber das soll hier nicht interessieren.
Das Problem ist, dass ich mir nicht sicher bin, ob das Überhaupt mit einer SQL-Abfrage machbar ist. Zumindest bin ich selbst zu keiner Lösung gekommen. Es wäre kein Problem das ganze in ein paar mehr Abfragen hinzubekommen, aber man soll ja immer optimieren und es interessiert mich einfach, ob so etwas möglich ist

Hier mal ein Beispiel:
id, match_id, killer_id, killed_id, n
..., 1, A, B, 4
..., 1, B, A, 7
..., 1, C, A, 12

(A hat B 4x abgeschossen, B hat A 7x abgeschossen, C hat A 12x abgeschossen)

Ergebnis:
player_id, kills, deaths
A, 4, 19
B, 7, 4
C, 12, 0

Ich dachte eigentlich, ich wäre recht fit in SQL, aber irgendwie ist mir die Sache zu hoch Man kann ja mit GROUP BY arbeiten, um z.B. alle Kills/Deaths eines Spielers abzufragen, aber halt immer nur entweder oder. Eine Lösung, wie ich beides in eine Tabelle bekomme, habe ich noch nicht gefunden.

Schonmal danke, wer sich die Zeit nimmt und sich mein Problem mal durchliest

[Gast]

Okay, irgendwie komm ich immer nachdem ich eine Frage poste auf die Antwort. Rückblickend ist es eigentlich gar nicht so kompliziert. Das mit den zwei GROUP BY Möglichkeiten ist mir während dem Schreiben eingefallen und dann war es nicht mehr allzu viel Denkarbeit, diese beiden halt einfach anhand der ID zusammenzufassen.

Deswegen hier das funktionierende Statement:

SELECT `kills`.`id` as `id`, `kills`.`n` as `kills`, `deaths`.`n` as `deaths` 
FROM 
   (SELECT `killer_id` as `id`, SUM(`n`) as `n` 
    FROM `kills` GROUP BY `killer_id`) as `kills`
LEFT OUTER JOIN 
   (SELECT `killed_id` as `id`, SUM(`n`) as `n` 
    FROM `kills` GROUP BY `killed_id`) as `deaths`
ON `kills`.`id` = `deaths`.`id`
GROUP BY `kills`.`id`

Sorry, dass ich hier unnötiger Weise einen Thread geöffnet habe

[highrise]

SELECT pid AS player_id, SUM( k ) AS kills, SUM( d ) AS deaths
FROM (

(
SELECT killer_id AS pid, SUM( n ) AS k, 0 AS d
FROM spiele
GROUP BY killer_id
)

UNION

(
SELECT killed_id AS pid, 0 AS k, SUM( n ) AS d
FROM spiele
GROUP BY killed_id
)

) AS ergebnisse
GROUP BY player_id


greetz, high


//edit: und da war ich definitiv mal wieder zu langsam... ;-)
Würde mich mal interessieren, welche query schneller ist...

[Gast]

Danke, denn meins hat nicht ganz einwandfrei funktioniert. Spieler mit 0 Kills wurden nicht zurückgegeben, durch das LEFT JOIN.
Man bräuchte ja bei meiner Variante eine Mischung aus LEFT JOIN und RIGHT JOIN. Soweit ich weiß, wäre das ja ein FULL OUTER JOIN, den MySQL aber (anscheinend?) nicht untnerstützt. Wie würde man das in MySQL hinkriegen?

PS: Wenn die Tabelle demnächst ordentlich gefüllt ist, kann ich ja durchaus mal testen, wie schnell die Queries sind.