UniLecs #131. Champions League

Задача: есть таблица группового этапа футбольной Лиги Чемпионов вида: 

ChampionsLeage - Table (Group, Club, Points)

Здесь ключами являются первые два поля (Group, Club). В каждой группе по 4 команды. Необходимо вывести чемпионов и аутсайдеров (последнее место) каждой группы. 

Условие: использовать SQL, нельзя использовать подзапросы.

Пример:

Answer:

Чемпионы:

Group A|Borussia D|18
Group B|Inter|18

Аутсайдеры:

Group A|Brugge|0
Group B|Barcelona|0

Идея: классическое решение с подзапросом выглядело бы следующим образом:

select GroupName, Team, [Points] from ChampionsLeague ch1
where [Points] = (select Max([Points]) -- Max - для 'чемпионов' / Min - для 'аутсайдеров'--
from ChampionsLeague ch2
where ch1.GroupName = ch2.GroupName)

Test:

http://tpcg.io/v4JxTb

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

1. Нам необходимо c 'join'ить одну и ту же таблицу по определенному условию

select * from ChampionsLeague ch1 
  left join ChampionsLeague ch2 on ch1.Points < ch2.Points and ch1.GroupName = ch2.GroupName

Group A|Atletico M|15|Group A|Borussia D|18
Group A|Borussia D|18|NULL|NULL|NULL
Group A|Monaco|6|Group A|Atletico M|15
Group A|Monaco|6|Group A|Borussia D|18
Group A|Brugge|0|Group A|Atletico M|15
Group A|Brugge|0|Group A|Borussia D|18
Group A|Brugge|0|Group A|Monaco|6
Group B|Barcelona|0|Group B|Inter|18
Group B|Barcelona|0|Group B|PSV|6
Group B|Barcelona|0|Group B|Tottenham|15
Group B|Tottenham|15|Group B|Inter|18
Group B|PSV|6|Group B|Inter|18
Group B|PSV|6|Group B|Tottenham|15
Group B|Inter|18|NULL|NULL|NULL

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

Соответственно это условие не выполнится только для тех команд, которые являются лидерами в своих группах

(ch1.MaxPointsByGroup < ch2.MaxPointsByGroup), в этом случае в этих строках будет Null для элементов из таблицы ch2.

Аналогично, для аутсайдеров, нужно только поменять знак в запросе

select * from ChampionsLeague ch1 
  left join ChampionsLeague ch2 on ch1.Points > ch2.Points and ch1.GroupName = ch2.GroupName

2. Подправим запрос и выведем итоговый запрос

Для вывода чемпионов всех групп:

select ch1.GroupName, ch1.Team, ch1.Points from ChampionsLeague ch1 
  left join ChampionsLeague ch2 on ch1.Points < ch2.Points and ch1.GroupName = ch2.GroupName
where ch2.Points is NULL

Group A|Borussia D|18
Group B|Inter|18

Для вывод аутсайдеров групп:

select ch1.GroupName, ch1.Team, ch1.Points from ChampionsLeague ch1 
  left join ChampionsLeague ch2 on ch1.Points > ch2.Points and ch1.GroupName = ch2.GroupName
where ch2.Points is NULL

Group A|Brugge|0
Group B|Barcelona|0

Реализация:

https://gist.github.com/unilecs/a5f13e74767df7331c732dd9f0d3cc1e

Test:

http://tpcg.io/4dGHu0