Еще много практики

Я всегда говорил, что практика необходима и является очень важной в нашей жизни. Вспомните математику в школе, когда мы учили какие-то формулы – сначала учитель показывает формулу, а потом целый урок решаются различные задачи, чтобы ученики набрались практики использования формулы.

Мы узнали формулы, теперь нужно много практики.

Сколько человек играет в каждой команде? У нас есть таблица команд и есть таблица игроков. В обеих таблицах есть колонка teamid, так что задача банальна – нужно связать две таблицы и найти игроков, которые принадлежат определенным командам. Это явно inner join:

select * 
from team t
	join person p on t.teamid = p.teamid

Результат:

+--------+-------------------+----------+--------+------------+--------------+------------+
| teamid | name              | personid | teamid | firstname  | lastname     | positionid |
+--------+-------------------+----------+--------+------------+--------------+------------+
|      1 | Manchester United |        1 |      1 | Ole Gunnar | Solskjær     |          1 |
|      1 | Manchester United |        2 |      1 | Michael    | Carrick      |          1 |
|      1 | Manchester United |        3 |      1 | Kieran     | McKenna      |          1 |
|      1 | Manchester United |        4 |      1 | David      | de Gea       |          2 |

Я показал только первые четыре строки, а полный вывод – все команды и все игроки. Отлично, возможно это то, что вам и нужно и вы вручную посчитаете, сколько реально человек в каждой команде, но если мы говорим про подсчет чего-то, обычно это group by. Нам нужно количество по командам, а значит группируем по названию команды:

select t.name, count(*)
from team t
	join person p on t.teamid = p.teamid
group by t.name

Так как я здесь группирую по имени команды, то нельзя выводить все колонки, можно только те, которые входят в group by и агрегатные функции, поэтому в select звездочка поменялась на конкретную колонку и count(*).

+-------------------+----------+
| name              | count(*) |
+-------------------+----------+
| Arsenal           |        6 |
| Chelsea           |        4 |
| Everton           |        3 |
| Liverpool         |        6 |
| Manchester City   |        7 |
| Manchester United |       10 |
+-------------------+----------+
6 rows in set (0.00 sec)

Отлично, то что нужно. В MU больше всего игроков, потому что я начал писать SQL для заполнения этой таблицы первой, понял, что мне так мнго для примеров и не нужно, поэтому в остальные команды добавлял меньше людей.

Подождите, но ведь этот запрос возвращает количество человек, а мы же хотели подсчитать только игроков, нам нужно исключить тренеров.

В таблице person есть positioned, которая связана на таблицу позиций:

Напоминаю, как выглядит эта таблица:

select * from position;

+------------+------------+
| positionid | name       |
+------------+------------+
|          1 | Coach      |
|          2 | Goalkeeper |
|          3 | Defender   |
|          4 | Midfielder |
|          5 | Forward    |
+------------+------------+

Получается, что нам нужно исключить тех, у кого positionid = 1. Исключить это !=

select t.name, count(*)
from team t
	join person p on t.teamid = p.teamid
where p.positionid != 1
group by t.name

А что, если мы хотим узнать, сколько в каждой команде игроков по типу. Команда связана с людьми, люди связаны с типами. Так что нам нужно два join:

select t.name as teamname, ptn.name as positionname, count(*)
from team t
    join person p on t.teamid = p.teamid
    join position ptn on p.positionid = ptn.positionid
group by t.name, ptn.name

Группируем по имени команды и имени позиции, потому что если мы сгруппируем только по позиции, то получим общее количество вратарей для всех команд. А нам нужно для каждой команды в отдельности.

Результат:

+-------------------+--------------+----------+
| teamname          | positionname | count(*) |
+-------------------+--------------+----------+
| Arsenal           | Coach        |        1 |
| Arsenal           | Defender     |        1 |
| Arsenal           | Forward      |        1 |
| Arsenal           | Goalkeeper   |        3 |
| Chelsea           | Coach        |        1 |
| Chelsea           | Defender     |        1 |
| Chelsea           | Goalkeeper   |        1 |
| Chelsea           | Midfielder   |        1 |
| Everton           | Coach        |        1 |
| Everton           | Goalkeeper   |        1 |
| Everton           | Midfielder   |        1 |
| Liverpool         | Coach        |        1 |
| Liverpool         | Defender     |        1 |
| Liverpool         | Forward      |        1 |
| Liverpool         | Goalkeeper   |        1 |
| Liverpool         | Midfielder   |        2 |
| Manchester City   | Coach        |        1 |
| Manchester City   | Defender     |        2 |
| Manchester City   | Forward      |        3 |
| Manchester City   | Goalkeeper   |        1 |
| Manchester United | Coach        |        3 |
| Manchester United | Defender     |        3 |
| Manchester United | Forward      |        2 |
| Manchester United | Goalkeeper   |        1 |
| Manchester United | Midfielder   |        1 |
+-------------------+--------------+----------+

Обратите внимание, что для некоторых команд тут 5 записей, а есть команды как Everton, которая появляется только три раза, потому что у этой команды почему-то не оказалось нападающих и защитников и они еще и на что-то претендуют.