Подсчитайте 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, чтобы запрос возвращал:

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`;

mysql sql innodb
person Jacob    schedule 02.07.2015    source источник
comment
И вы пытались подготовить запрос?   -  person Nima Derakhshanjan    schedule 02.07.2015
comment
Самое дальнее, что я получаю, это имя | убийств в отдельном запросе, у меня тоже проблемы с ассоциацией смертей   -  person Jacob    schedule 02.07.2015
comment
хорошо, но я имею в виду, поделитесь своим кодом, пожалуйста, потому что SO не готовит код для вас, но решит проблему в вашем коде.   -  person Nima Derakhshanjan    schedule 02.07.2015

Ответы (2)


arrow_upward
3
arrow_downward

Пытаться

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

person Dewanta    schedule 02.07.2015
comment
Не уверен, как это работает, но это именно то, что я хочу/нужно, спасибо :) - person Jacob; 02.07.2015

arrow_upward
3
arrow_downward

Посмотрите, работает ли это:

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

Если нет, то нам может понадобиться преобразовать COUNT в подзапросы.

person Rick James    schedule 02.07.2015
comment
это тоже работает :) все еще не уверен, что делают эти запросы, спасибо за вашу помощь: D - person Jacob; 02.07.2015
comment
@Dewanta была быстрее в розыгрыше. Два решения эквивалентны, если player.id и name равны 1:1. Мы оба добавили псевдонимы, чтобы избежать двусмысленности наличия двух ссылок player_kill. - person Rick James; 02.07.2015