MySQL как составить SQL запрос?

209

04 февраля 2020, 14:20

База данных MySQL. Имеется таблица battles и players. Лишнее убрано, нужное сокращено для простоты.

Структура таблицы battles

battle_id         - уникальный идентификатор битвы

Структура таблицы players

battle_id         - внешний ключ
name              - имя игрока
player_key        - уникальный ключ игрока в битве (для каждой битвы разный, т.е. в рамках одной битвы совпадений быть не может, но в самой таблице не обладает уникальностью)
killer_player_key - ключ игрока который "Вас" уничтожил
player_type       - тип игрока

Пример данных:

| battle_id  | 
| 125        | 
| 126        |

players

| battle_id | name    | player_key | killer_player_key | player_type |
| 125       | player1 | 203        | null              | A           |
| 125       | player2 | 204        | 203               | B           |
| 125       | player3 | 205        | 203               | C           |
| 125       | player4 | 206        | 204               | B           |
| 126       | player1 | 403        | 406               | A           |
| 126       | player2 | 404        | 403               | B           |
| 126       | player3 | 405        | 406               | A           |
| 126       | player4 | 406        | null              | A           |

Как получить такой результат?

| name    | COUNT(A) | COUNT(B) | COUNT(C) |
| player1 | 0        | 2        | 1        |
| player2 | 0        | 1        | 0        |
| player3 | 0        | 0        | 0        |
| player4 | 2        | 0        | 0        |

Т.е. посчитать сколько типов противников уничтожил каждый игрок за все проведенные битвы?

P.S. В SQL не силен. Примерно прикинул алгоритм, а как реализовать не могу разобраться.

1) Взять из таблицы players все значения name без повторов(DISTINCT)

2) Для каждого name посчитать сколько было уничтожено "типов" врагов

2.1) Выбрать все battles в которых участвовал name 2.2) В выбранных battles игрок может не уничтожить ни одного из игроков или уничтожить 1 и более.

Answer 1

SELECT t1.name,
       COUNT(CASE WHEN t2.player_type = 'A' THEN 1 END) count_a,
       COUNT(CASE WHEN t2.player_type = 'B' THEN 1 END) count_b,
       COUNT(CASE WHEN t2.player_type = 'C' THEN 1 END) count_c
FROM players t1
LEFT JOIN players t2
  ON  t1.battle_id = t2.battle_id
  AND t1.player_key = t2.killer_player_key
GROUP BY t1.name;

Вместо COUNT(CASE WHEN t2.player_type = 'Х' THEN 1 END) можно использовать COALESCE(SUM(t2.player_type = 'Х'), 0).