Задачи : Вопрос 1 : 61

Есть таблица результатов команд в футбольных чемпионатах вида:

 

Championship - Table(Liga, Team, Scores), ключами являются первые два поля. В каждом чемпионате больше 1 команды.
Необходимо вывести чемпионов и аутсайдеров(посл.место) каждой лиги. Обойтись без под запросов.

Таблица:

   Liga            Team           Scores 
1   England        Arsenal          83 
2   England        Chelsea          85 
3   Germany        Bayern           90 
4   Germany        Dortmund         99 
5   Germany        Gerta            91 
6   Italy          Inter            80 
7   Italy          Milan            75 
8   Spain          Barcelona        70 
9   Spain          Real-Madrid      90 

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

 

Верно ли наше решение?

Ответ:

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

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


Разберем как можно выполнить эту задачу без использования под запросов:
1. Нам необходимо c 'join'ить одну и ту же таблицу по определенному условию

    select * from Championship ch1 
    left join Championship ch2 on ch1.Scores < ch2.Scores and ch1.Liga = ch2.Liga 
 
    Liga          Team        Scores      Liga         Team         Scores 
1    England      Arsenal       83       England      Chelsea         85 
2    England      Chelsea       85       NULL         NULL           NULL 
3    Germany      Bayern        90       Germany      Dortmund        99 
4    Germany      Bayern        90       Germany      Gerta           91 
5    Germany      Dortmund      99       NULL         NULL           NULL 
6    Germany      Gerta         91       Germany      Dortmund        99 
7    Italy        Inter         80       NULL         NULL           NULL 
8    Italy        Milan         75       Italy        Inter           80 
9    Spain        Barcelona     70       Spain        Real-Madrid     90 
10    Spain       Real-Madrid   90       NULL         NULL           NULL 


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

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

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


select * from Championship ch1 
left join Championship ch2 on ch1.Scores > ch2.Scores and ch1.Liga = ch2.Liga 


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

2.а Для вывода чемпионов всех лиг

select ch1.Liga, ch1.Team, ch1.Scores from Championship ch1 
left join Championship ch2 on ch1.Scores < ch2.Scores and ch1.Liga = ch2.Liga 
where ch2.Scores is NULL 
 
     Liga            Team         Scores 
1    England        Chelsea        85 
2    Germany        Dortmund       99 
3    Italy          Inter          80 
4    Spain          Real-Madrid    90 


2.b Для вывода аутсайдеров всех лиг

select ch1.Liga, ch1.Team, ch1.Scores from Championship ch1 
left join Championship ch2 on ch1.Scores > ch2.Scores and ch1.Liga = ch2.Liga 
where ch2.Scores is NULL 
     
     Liga            Team         Scores 
1    England        Arsenal        83 
2    Germany        Bayern         90 
3    Italy          Milan          75 
4    Spain          Barcelona      70 
1 балл


© Copyright © 2017 - 2019 Все права защищены. Бесплатное онлайн образование.