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

57
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).

READ ALSO
Переменные в MySQL

Переменные в MySQL

Вопросы закомментированы в коде:

48
Как составить запрос json rpc?

Как составить запрос json rpc?

Пишу json rpc api , проблема такая: посылаю запрос на один адрес exampleru/api/jsonrpc

38
Защита COOKIE от подмены

Защита COOKIE от подмены

у меня есть авторизация на сайте через стимДалее человеку присваиваются его веб права на основе группы

57
Всплывающее окно по клику

Всплывающее окно по клику

У меня есть всплывающее окно, которое появляется по клику на кнопку, всплывающее окно содержит график

71