Практика - ищем победителей

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

Таблица игр

Если посмотреть на таблицу Games, то результат не очень наглядный и скучный, там сплошные цифры:

select * from game

На много интереснее было бы вывести вместе со счетом и названия команд.

+-------------------+---------------+----------------+-------------------+
| hometeam          | hometeamgoals | guestteamgoals | guestteam         |
+-------------------+---------------+----------------+-------------------+
| Liverpool         |             2 |              2 | Manchester United |

Такой результат уже на много интереснее.

В таблице game есть два поля, которые означают ID команды и наша задача вместо числового ID показать имя команды.

Связывать две разные таблицы – относительно простая задача, а тут у нас сразу две колонки, которые имеют ссылку на таблицу команд. Напрашивается вариант:

select *
from game g 
   join team gt on g.guestteamid = gt.teamid and g.hometeamid = qt.teamid;

Мы же ищем в таблице по guestteamid и по hometeamid, но это неверный вариант, потому что результатом связи не может быть сразу две разные строки.

Вот взять, например, следующую строку из таблицы Game:

+--------+------------+-------------+---------------+----------------+
| gameid | hometeamid | guestteamid | hometeamgoals | guestteamgoals |
+--------+------------+-------------+---------------+----------------+
|     47 |          2 |           1 |             2 |              2 |

Сервер возьмет эту строку и будет пытаться связать ее с team согласно нашему требованию g.guestteamid = gt.teamid and g.hometeamid = qt.teamid. Наша связь говорит, что нужно найти в Team строку в которой teamid = 2 (hometeamid) и 1 (guestteamid) одновременно, потому что между двумя условиями стоит AND. Ну невозможно иметь строку, в которой сразу и 2 и 1.

|      1 | Manchester United |
|      2 | Liverpool         |

Результатом должна быть одна длинная строка, в которой будет как:

| gameid | hometeamid | guestteamid | hometeamgoals | guestteamgoals | teamid | name              | teamid | name              |
|     47 |          2 |           1 |             2 |              2 |      1 | Manchester United |     2 | Liverpool         |

Нам нужно найти команду название команды сначала для гостевого guestteamid:

select *
from game g 
   join team gt on g.guestteamid = gt.teamid

И теперь нужно найти запись для домашней команды и для этого нужен еще один join:

select ht.name as hometeam, hometeamgoals, guestteamgoals, gt.name as guestteam 
from game g 
   join team gt on gt.teamid = g.guestteamid 
   join team ht on ht.teamid = g.hometeamid;

Чтобы не отображать все колонки я в SELECT выбираю только два названия команд (домашней и гостевой) и счет.

CASE – узнаем кто победил

В программировании достаточно часто приходится писать различную логику. При работе с SQL тоже иногда приходиться делать логику, но прямо во время работы с самим запросом, когда пишем SELECT и это возможно с помощью оператора CASE. В общем виде он выглядит следующим образом:

сase 
  when колонка = значение then что-то
end

Таким when может быть несколько. Если колонка равна одному значению, то выводить один результат. Если другое значение, то другой результат:

сase 
  when колонка = значение1 then результат1
  when колонка = значение2 then результат2
end

Можно проверять разные колонки и использовать любые условия – больше, меньше. Можно сравнивать больше одной колонки в условии. Можно проверить на пару значений, а потом сказать – а во всех остальных случаях выводить что-то. Для того, чтобы указать «все остальные случаи» нужно добавить else:

сase 
  when колонка = значение1 then результат1
  when колонка = значение2 then результат2
  else результат3
end

Попробуйте написать следующий запрос сами, и тут лучше не торопиться и не проматывать страницу быстро, чтобы не увидеть мой запрос. Понемногу прокручивайте, чтобы посмотреть требование и потом напишите запрос.

Отлично, теперь мы можем написать запрос, который будет выводить колонки – названия команд, счет, и в виде текста выводить – победила домашняя команда или нет. Если домашняя команда забила больше гостевой, то выводим слово “победила”, если команды забили одинаково, то выводим "ничья". Если домашняя команда забила меньше, то "проиграла". Остальных случаев просто математически не может быть, но просто для прикола выведем – "Фигня какая-то".

В прошлом разделе мы уже написали почти все необходимое, остается только добавить текстовое описание результата.

Мой запрос:

select ht.name as hometeam, hometeamgoals, guestteamgoals, gt.name as guestteam,
    case 	
    when hometeamgoals > guestteamgoals then 'Победила'
    when hometeamgoals < guestteamgoals then 'Проиграла'
    when hometeamgoals = guestteamgoals then 'Ничья'
    else 'Фигня какая-то'
    end as HomeResult
from game g 
   join team gt on gt.teamid = g.guestteamid 
   join team ht on ht.teamid = g.hometeamid;

Да, мы можем сравнивать две колонки и я тут сравниваю hometeamgoals и guestteamgoals.

ISNULL и IFNULL

Колонки могут иметь значения по умолчанию, но в этом случае это значение будет сохранено в колонку при создании записи (строки). Чаще всего это нормально, но в некоторых случаях это может быть не самым лучшим решением, если значение по умолчанию может измениться. Сегодня мы хотим отображать одно значение в случае отсутствия данных в колонке, а завтра может быть другое. Что делать?

Можно придумать разные варианты решения, но на мой взгляд достаточно простым является сохранение в базе данных реально NULL значения, если это возможно. Например, давайте сделаем так, чтобы имя в таблице Person разрешало нулевые значения:

alter table person modify lastname varchar(30) null

Теперь добавим в первую команду игрока с нулевым значением в качестве имени:

insert into person (firstname)
values ('Ronaldo')

Итак, если теперь

select firstname, lastname, teamid
from person 
where firstname='Ronaldo'

Вторая и третья строки будут нулевыми:

+-----------+----------+--------+
| firstname | lastname | teamid |
+-----------+----------+--------+
| Ronaldo   | NULL     |   NULL |
+-----------+----------+--------+

Если мы пишем программу на каком-то более высокоуровневом языке программирования мы теперь можем взять и заменить NULL на что-то другое. Например, для фамилии можем подменять на XXX, а в случае команды – показывать -1. А можно то же самое сделать с помощью IFNULL в MySQL или ISNULL в MS SQL Server. Обе функции работают одинаково, но названы по-разному в разных базах данных. Так что если вы работаете с MS SQL, то просто заменяйте в следующих примерах IFNULL на ISNULL. В MySQL есть ISNULL, но он работает немного по-другому.

IFNULL(колонка, значение)

Если колонка равна NULL, то вместо этого будет отображаться значение, которое указано после запятой (это второй параметр функции IFNULL). В следующем запросе я прошу вместо нулевых lastname отображать XXX, а вместо нулевой команды -1:

select firstname, IFNULL(lastname, 'XXX'), IFNULL(teamid, -1)
from person 
where firstname='Ronaldo'

Результат:

+-----------+-------------------------+--------------------+
| firstname | IFNULL(lastname, 'XXX') | IFNULL(teamid, -1) |
+-----------+-------------------------+--------------------+
| Ronaldo   | XXX                     |                 -1 |
+-----------+-------------------------+--------------------+

В качестве результата может быть не какое-то конкретное значение, а другая колонка. Например, если нет фамилии, то можно отобразить имя:

select firstname, IFNULL(lastname, firstname), IFNULL(teamid, -1)
from person 
where firstname='Ronaldo'

В результате вместо фамилии будет имя, то есть Ronaldo отобразиться и в имени, и в фамилии.

Для проверки на NULL есть еще один вариант – COALESCE и этот вариант прописан в стандарте языка, а значит будет присутствовать во всех базах данных, которые обещают поддержку стандарта и везде будет работать одинаково.

ISNULL(колонка, значение) – это функция. На счет MySQL не знаю, а в MS SQL Server она выполняется раз и в качестве типа данных результата использует первую колонку. То есть если вы укажите ISNULL(колонка1, колонка2) и в обеих колонках будет разный тип, то качестве финального будет выбран первый. COALESCE проверяет несколько значений и берет тип данных по старшенству.

Итак, в общем виде COALESCE выглядит так:

COALESCE(column1, column2, column3...)

Если колонка1 равна NULL, то будет взята колонка2, если колонка 2 равна нулю, то будет взята третья и так далее, пока сервер не дойдет до конца и не получит результат. Теоретически, COALESCE – это то же самое, что записать CASE выражение:

CASE
  WHEN column1 IS NOT NULL THEN column1
  WHEN column2 IS NOT NULL THEN column2
  WHEN column3 IS NOT NULL THEN column3
   . . . 
   . . . 
END

Функции ISNULL и COALESCE удобны и эффективны не только в SELECT, но и в WHERE. Давайте добавим в таблицу еще и Ronaldinio, надеюсь я написал его имя или что это:

insert into person (firstname, lastname) values ('Ronaldinio', '')

Разницу в том, что у Рональдо фамилия равна NULL, а у Рональдиньо пустая строка. Если делать сравнение на пустую строку, то мы получим Рональдьньо:

select * from person where lastname = '';
+----------+--------+------------+----------+------------+
| personid | teamid | firstname  | lastname | positionid |
+----------+--------+------------+----------+------------+
|       39 |   NULL | Ronaldinio |          |       NULL |
+----------+--------+------------+----------+------------+

А если сравнивать is null, то получим Рональдо:

select * from person where lastname is null;
+----------+--------+-----------+----------+------------+
| personid | teamid | firstname | lastname | positionid |
+----------+--------+-----------+----------+------------+
|       38 |   NULL | Ronaldo   | NULL     |       NULL |
+----------+--------+-----------+----------+------------+

Чтобы найти и то и другое можно использовать OR и проверять и то и другое:

select * from person where lastname = '' or lastname is null

Или можно все это объединить с помощью isnull/ifnull:

select * from person where ifnull(lastname, '') = '';
+----------+--------+------------+----------+------------+
| personid | teamid | firstname  | lastname | positionid |
+----------+--------+------------+----------+------------+
|       38 |   NULL | Ronaldo    | NULL     |       NULL |
|       39 |   NULL | Ronaldinio |          |       NULL |
+----------+--------+------------+----------+------------+

Здесь мы говорим ifnull(lastname, ''), т.е. в тех случаях, где фамилия равна NULL, мы должны воспринимать это как пустую строку и в результате нам нужно проверить только на пустую строку.

Ищем победителя в лоб

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

Попробуйте сами написать запрос, который будет возвращать имя команды, количество побед и количество ничьих в виде такой таблицы:

+-------------------+------+------+
| name              | wins | ties |
+-------------------+------+------+
| Manchester United |    2 |    3 |
| Liverpool         |    5 |    2 |
| Arsenal           |    5 |    2 |
| Everton           |    3 |    4 |
| Chelsea           |    3 |    5 |
| Manchester City   |    4 | NULL |
+-------------------+------+------+

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

Такую таблицу можно построить несколькими способами, и я покажу пару из них и начнем мы с самого простого, но не самого эффективного.

Начнем по шагам, сначала получим имя команды.

select t.teamid, t.name
from team t

Здесь я вывожу ID команды и имя. ID будет просто для наглядности, потому что по этой колонке мы будем связывать.

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

select count(*) 
from game 
where hometeamgoals > guestteamgoals and hometeamid = 1

Мы проверяем, если домашняя команда забила больше голов и домашняя команды – это команда 1, то считаем количество записей.

Теперь то же самое нужно сделать и для гостевых матчей – когда команда в гостях забила больше голов, чем домашняя команда:

select count(*) 
from game 
where hometeamgoals < guestteamgoals and guestteamid = 1

То есть если сложить оба результата, то мы получим общее количество побед. Отлично, давайте будем и использовать это в нашем SELECT запросе, сначала начнем с домашних побед, добавим их в SELECT:

select t.name,
    (select count(*) from game g
          where g.hometeamgoals > g.guestteamgoals and g.hometeamid = t.teamid) as wins
from team t

Да, запросы можно вставлять прямо на место колонок. Кажется, я это уже разок делал.

Что означает этот запрос. Когда мы пишем запрос прямо в SELECT на месте колонки, то для каждой записи в team таблице база данных будет выполнять наш подзапрос

select count(*) from game g
where g.hometeamgoals > g.guestteamgoals and g.hometeamid = t.teamid

База берет первую команду с teamid = 1 и выполняет этот запрос, подставляя вместо t.teamid число 1 и выводит результат. Получается

| Manchester United |    0 |

Манчестер дома ни разу не выиграл.

Потом берется вторая команда и то же самое выполняется для нее и выводиться вторая строка:

| Liverpool         |    2 |

Ливерпуль дома выиграл дважды.

И так будет выполняться все до самого конца.

Но нам же нужно не только домашние победы. Без проблем, мы можем прибавить и гостевые победы:

select t.name,
    (select count(*) from game 
          where hometeamgoals > guestteamgoals and hometeamid = t.teamid) + 
    (select count(*) from game 
          where hometeamgoals < guestteamgoals and guestteamid = t.teamid)
from team t

Вот теперь мы получим нужный нам результат.

Посмотрите на запросы, которые мы выполняем в SELECT, оба запроса можно объединить в один. Секция SELECT и FROM у них одинакова, а значит нужно просто записать секцию WHERE через OR:

select count(*) from game where 
    (hometeamgoals > guestteamgoals and hometeamid = 1) or
    (hometeamgoals < guestteamgoals and guestteamid = 1)

Отлично, этот запрос мы можем использовать в SELECT и решить задачу одним шагом:

select t.name,
    (select count(*) from game where 
    (hometeamgoals > guestteamgoals and hometeamid = t.teamid) or
    (hometeamgoals < guestteamgoals and guestteamid = t.teamid)) as wins
from team t

Оба варианта решают одну задачу. Какой выбрать – два SELECT и плюс между ними или один SELECT с OR? Это вопрос не только простоты чтения, это еще и производительность. Из личного опыта могу сказать, что MS SQL Server может тупить, когда выполняется один запрос с OR в секции WHERE и два запроса с плюсом может выполняться быстрее.

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

Запросы в секции SELECT обычно медленнее и по возможности нужно писать запросы в JOIN.

Ищем победителя через порочные связи

Для этого варианта нам нужно написать запрос, который будет возвращать ID победителя игры. Если домашняя команда забила больше голов, то мы должны вернуть ID домашней команды. Если гостевая забила больше, то выводим гостевую. Оставшийся случай – ничья, нас пока не касается.

Для решения задачи с ЕСЛИ в SQL есть CASE, который мы недавно рассматривали.

select
    case 
        when hometeamgoals > guestteamgoals then hometeamid
        when hometeamgoals < guestteamgoals then guestteamid
    end teamid
from game

Отлично, запрос возвращает нам для каждой игры победителя. Для ничьей будет NULL, но это не важно, но чтобы просто быть аккуратнее можно отфильтровать такой случае, сказав, что нам нужны только игры, где количество забитых голов отличаются, то есть точно есть победитель:

select
    case 
        when hometeamgoals > guestteamgoals then hometeamid
        when hometeamgoals < guestteamgoals then guestteamid
    end teamid
from game
where hometeamgoals != guestteamgoals 

Отлично, теперь нам нужно подсчитать количество побед по каждой группе. Группой является целое условие CASE, неужели мы его можем записать в секции GROUP BY? Да, мы можем делать это без проблем:

select
    case 
        when hometeamgoals > guestteamgoals then hometeamid
        when hometeamgoals < guestteamgoals then guestteamid
    end teamid,
    count(*) as wins
from game
where hometeamgoals != guestteamgoals 
group by case when hometeamgoals > guestteamgoals then hometeamid when hometeamgoals < guestteamgoals then guestteamid end 

Первая колонка в SELECT совпадает с тем, что я написал в GROUP BY, а значит такое не нарушает правил и мы можем это делать. Главное не менять WHEN местами. Это мы знаем, что от перемены WHEN местами, сумма не меняется, а базе данных все равно, она требует, чтобы мы все писали в точности одинаково.

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

Нам остается только вывести название команды, а не ID. Для этого таблицу game нужно объединить с таблицей команд через JOIN:

И тут есть проблема, если мы делаем JOIN на team, то и тут нужно выбирать именно команду победителя и снова писать CASE:

select
    t.name,
    count(*) as wins
from game g
    join team t on t.teamid = (case when hometeamgoals > guestteamgoals then hometeamid when hometeamgoals < guestteamgoals then guestteamid end)
where hometeamgoals != guestteamgoals 
group by case 
            when hometeamgoals > guestteamgoals then hometeamid
            when hometeamgoals < guestteamgoals then guestteamid
        end, t.name

При наведении связи снова используется логика CASE, потому что мы ищем название команды победителя. В group by я добавил имя, потому что я вывожу его, а если используются агрегатные функции, то все колонки, которые выводятся в SELECT и не являются функциями, должны быть в group by.

Если вы уверены, что имя команды уникально, то из group by можно убрать CASE:

select
    t.name,
    count(*) as wins
from game g
    join team t on t.teamid = (case when hometeamgoals > guestteamgoals then hometeamid when hometeamgoals < guestteamgoals then guestteamid end)
where hometeamgoals != guestteamgoals 
group by t.name

Этот запрос будет работать только если мы уверены, что названия команд уникальны. Если же в чемпионате играет две команды Динамо из разных городов и в базе в названии команды написано только Динамо, без указания города, то их победы попадут в одну группу и объединяться в одно целое. Чтобы этого не произошло, нужно вернуть CASE. Даже несмотря на то, что я не выводил его на экран, по нему все еще группировались данные и в результате мы увидели бы две строки с именем команды Динамо.

А что, если какая-то команда ни разу не победила? Она не будет выведена на экран. Нам нужно развернуть запрос и сделать left join таким образом:

Select name, wins
From team
   Left join (
      ЗДЕСЬ ЗАПРОС ПОДСЧЕТА ПОБЕД
   ) w on t.teamid = w.teamid

Делаем шаг назад и находим запрос, который считал количество побед, мы его написали раньше:

select
case 
    when hometeamgoals > guestteamgoals then hometeamid
    when hometeamgoals < guestteamgoals then guestteamid
end teamid,
count(*) as wins
from game
where hometeamgoals != guestteamgoals 
group by case 
    when hometeamgoals > guestteamgoals then hometeamid
    when hometeamgoals < guestteamgoals then guestteamid
end

Вот теперь мы готовы объединить эти два запроса в один

select name, wins
from team t
    left join (
        select
        case 
            when hometeamgoals > guestteamgoals then hometeamid
            when hometeamgoals < guestteamgoals then guestteamid
        end teamid,
        count(*) as wins
        from game
        where hometeamgoals != guestteamgoals 
        group by case 
            when hometeamgoals > guestteamgoals then hometeamid
            when hometeamgoals < guestteamgoals then guestteamid
        end
    ) w on t.teamid = w.teamid

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

Отлично, всего два SELECT и у нас есть таблица побед. Как сервер будет выполнять этот запрос? Зависит от ситуации, наличия нужных индексов, статистики, но у него будет выбор:

1. Выполнить SELECT для team и для каждой записи из team выполнить запрос из left join. Такое объединение называют loop join, потому сервер как бы в цикле loop выполняет объединение, как это было при самом первом варианте решения, когда подзапрос был прямо на месте колонки. Этот вариант неплохо работает на небольшом количестве данных.

2. Выполнить два запроса по-отдельности, получить две таблицы с результатами и объединить их с помощью MERGE или HASH. Это два разных варианта объединения двух таблиц. Этот вариант отлично себя показывает даже на большом количестве данных в базе, но когда в результате выводиться немного данных, потому что сервер будет обращаться к большой базе только дважды, а потом в памяти объединять небольшое количество данных.

Теперь нам нужны ничьи. Если в случае победы мы должны засчитать результат только одной команде, то в случае ничьей это должно быть засчитано обеим командам. А значит CASE не подойдет, потому что он выводил только одну команду. Мы не можем писать такое:

case when hometeamgoals = guestteamgoals then hometeamid and guestteamid

Теоретически мы можем превратить это в строку, разделенную запятой:

case when hometeamgoals = guestteamgoals then hometeamid + ',' + guestteamid

и потом мудрить при наведении связей, но это будет сложно и печально.

Обе команды получают по очку за победу

А значит мы можем выполнить два запроса:

select hometeamid as tieteamid
from game
where hometeamgoals = guestteamgoals 

и

select guestteamid
from game
where hometeamgoals = guestteamgoals 

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

Теперь нам нужно объединить оба запроса в один с помощью UNION ALL, потом завернуть все это в еще один запрос и вот для него уже подсчитать количество ничейных результатов для каждой команды:

select tieteamid, count(*)
from (
    select hometeamid as tieteamid, 1 as points from game where hometeamgoals = guestteamgoals union all select guestteamid, 1 as points from game where hometeamgoals = guestteamgoals 
) ties
group by tieteamid

В данном случае в секции FROM у нас не таблица, а другой запрос, который состоит аж из двух SELECT объединенных с помощью UNION. Да, мы можем получать данные не только из таблиц, но и из других запросов. Здесь мы пишем запрос к запросу.

Выделенный жирным код возвращает какие-то данные и внешний SELECT берет эти данные и выполняет свои операции над ним. А внешний запрос группирует данные по id команды и считает количество записей для каждой команды.

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

Когда мы выводили таблицу побед, то мы к таблице команд добавили таблицу побед. Теперь нужно к таблице команд добавить таблицу ничейных результатов:

select t.name, wins, ties
from team t
left join (
    select case when hometeamgoals > guestteamgoals then hometeamid when hometeamgoals < guestteamgoals then guestteamid end winteamid, count(*) as wins from game g where hometeamgoals != guestteamgoals group by case when hometeamgoals > guestteamgoals then hometeamid when hometeamgoals < guestteamgoals then guestteamid end 
) win on t.teamid = win.winteamid

left join (
    select tieteamid, count(*) as ties from ( select hometeamid as tieteamid, 1 as points from game where hometeamgoals = guestteamgoals union all select guestteamid, 1 as points from game where hometeamgoals = guestteamgoals ) ties group by tieteamid
) tie on tie.tieteamid = t.teamid

Результат:

+-------------------+------+------+
| name              | wins | ties |
+-------------------+------+------+
| Manchester United |    2 |    3 |
| Liverpool         |    5 |    2 |
| Arsenal           |    5 |    2 |
| Everton           |    3 |    4 |
| Chelsea           |    3 |    5 |
| Manchester City   |    4 | NULL |
+-------------------+------+------+

Зная количество побед и количество ничейных результатов легко посчитать финальные очки, нужно просто умножить количество побед на 3 (три очка за победу) и прибавить количество ничейных результатов (одно очко за ничью). Но при этом нужно быть аккуратным, потому что число плюс NULL равно NULL. Число умножить на NULL – это NULL. А у меня в таблице у одной команды нет ни одной ничьей. У Manchester City всего четыре победы, а все остальные были проигрыши, так что не оказалось ни одной ничьей. Так что количество очков можно посчитать так:

ifnull(wins, 0) * 3 + ifnull(ties, 0) as points

Не забываем, что для MS SQL Server нужно заменить ifnull на isnull.

Если вас просто интересует кто победил, то запрос можно было написать проще, я бы написал его так:

select team, sum(points)
from (
    select
    case 
        when hometeamgoals > guestteamgoals then hometeamid
        when hometeamgoals < guestteamgoals then guestteamid
    end team,
    3 as points
    from game
    where hometeamgoals != guestteamgoals 

    union all

    select hometeamid, 1 as points
    from game
    where hometeamgoals = guestteamgoals 

    union all

    select guestteamid, 1 as points
    from game
    where hometeamgoals = guestteamgoals 
) p
group by team
order by 2 desc

Результат:

+------+-------------+
| team | sum(points) |
+------+-------------+
|    3 |          17 |
|    2 |          17 |
|    5 |          14 |
|    4 |          13 |
|    6 |          12 |
|    1 |           9 |
+------+-------------+

Я показал вам пару вариантов решения задачи, но я вижу и возможные другие варианты решения. Как бы ты решил задачу поиска таблицы результатов игр? Жду комментариев.

HAVING

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

Вернемся к запросу, который возвращал нам количество побед:

select
    t.name, count(*) as wins
from game g
    join team t on t.teamid = (case 
            when hometeamgoals > guestteamgoals then hometeamid
            when hometeamgoals < guestteamgoals then guestteamid
        end)
where hometeamgoals != guestteamgoals 
group by t.name

Количество побед мы считаем через count(*), группируя результат по имени команды. А что, если мы хотим увидеть команды, которые победили как минимум 4 раза? Как сказать, что мы хотим вывести только записи, где count(*) >= 4?

Тут есть два варианта. Первый вариант – SQL позволяет обращаться к запросам, то есть мы можем писать SELECT, который выбирает записи из другого SELECT:

SELECT *
FROM (
    select t.name, count(*) as wins from game g join team t on t.teamid = (case when hometeamgoals > guestteamgoals then hometeamid when hometeamgoals < guestteamgoals then guestteamid end) where hometeamgoals != guestteamgoals group by t.name
 ) t
 WHERE wins > 3

Внутренний запрос, который выделен жирным возвращает команду и количество побед в колонке wins. Внешний запрос фильтрует этот результат и отображает только те записи, где количество побед wins более 3 что идентично >= 4.

Это все работает, но как-то громоздко. Писать запрос к другому запросу только для того, чтобы указать такой фильтр, как-то сложно.

В SQL есть способ проще – HAVING После group by мы можем указать это магическое слово и указать наш фильтр:

select t.name, count(*) as wins
from game g
    join team t on t.teamid = (case 
            when hometeamgoals > guestteamgoals then hometeamid
            when hometeamgoals < guestteamgoals then guestteamid
        end)
where hometeamgoals != guestteamgoals 
group by t.name
having count(*) > 3

having имеет смысл после group by.

В having мы можем указать фильтр по агрегатной функции.

Например, мы хотим узнать, какие команды выиграли ровно три раза:

select t.name, count(*) as wins
from game g
    join team t on t.teamid = (case 
            when hometeamgoals > guestteamgoals then hometeamid
            when hometeamgoals < guestteamgoals then guestteamid
        end)
where hometeamgoals != guestteamgoals 
group by t.name
having count(*) = 3