mysql - 计算2列以进行发生

所以基本上，我需要一个查询，它将返回显示名称、死亡数量和死亡数量。
我有两张桌子要搬。
这两张桌子是
player

id      | name
2334324 | user
4353454 | user2

其中，id是它们的唯一标识符，name是它们的显示名称。
第二张桌子是：
player_kill

id | killer  | victim  |
1  | 2334324 | 4353454 |
2  | 2334324 | 4353454 |
3  | 4353454 | 2334324 |

其中killer/victim列包含player表的唯一标识符。
我希望能够统计killer和victim中玩家i d的出现次数，以便查询返回：

name | kills | deaths
user | 2     | 1
user2| 1     | 2

其中kills下的数字是playerid在killer列中出现的次数，与死亡的次数相同
希望我能提供足够的信息。
到目前为止我所拥有的：

SELECT `player`.`name`, COUNT(DISTINCT `player_kill`.`id`) as `kills`, COUNT(DISTINCT `player_kill`.`id`) as `deaths`
FROM `player`
LEFT JOIN `player_kill` ON `player`.`id`=`player_kill`.`killer`
LEFT JOIN `player_kill` ON `player`.`id`=`player_kill`.`victim`
WHERE `player`.`id` = `player_kill`.`killer` AND `player`.`id` = `player_kill`.`victim`
GROUP BY `player`.`id`;

最佳答案

尝试

SELECT
    p.name,
    count(distinct pk1.id) as kills,
    count(distinct pk2.id) as deaths
FROM player p
LEFT JOIN player_kill pk1 ON pk1.killer = p.id
LEFT JOIN player_kill pk2 ON pk2.victim = p.id
group by p.name

http://sqlfiddle.com/#!9/649504/15/0