mysql - 计算 2 列的发生次数

Question

基本上，我需要一个查询来返回显示名称、击杀数量和死亡数量。

我有两个表需要从中提取。

两个表是

播放器

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 表的唯一标识符。

我希望能够计算玩家 ID 在 killer 和 victim 中的出现次数，以便查询返回:

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

其中，击杀数是 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`;

Answer 1

尝试

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