Блог

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

Это таблицы:

• cyclist (cyclist_id: Int, name_cyclist: строка, команда: строка из трех букв, страна: строка из трех букв)
• лента (_name: строка, km: int, тип: ‘flat’ или ‘high_mountain’ или ‘medium_mountain’ или ‘chronometro_a_team’ или ‘chronometro_individual’ или ‘time_trial’)
• arrival_order (cyclist_id:int ; tape_name:строка, порядок: int): где cyclist_id (соответственно, tape_name) — это внешний ключ, который ссылается на велосипедиста (соответственно, лента);

Это сценарий, который мне удалось сделать. Проблема в том, что он возвращает мне название команды, если, например, есть 2 спортсмена, и один входит в топ-2, а другой нет. Как я могу решить эту проблему?

 SELECT DISTINCT c.team
FROM arrival_order oa
JOIN tape s   ON oa.tape_name   = s.tape_name
JOIN cyclist c   ON oa.cyclist_id = c.cyclist_id
WHERE oa.order>2 AND s.type='flat';
 

Используйте агрегацию и выполните проверку order в HAVING предложении:

 SELECT c.team
FROM arrival_order oa JOIN
     tape s
     ON oa.tape_name = s.tape_name JOIN
     cyclist c
     ON oa.cyclist_id = c.cyclist_id
WHERE s.type = 'flat'
GROUP BY c.team
HAVING MIN(oa.order) > 2 ;
 

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

 CREATE TABLE teams AS SELECT DISTINCT team FROM cyclists;
ALTER TABLE teams ADD PRIMARY KEY (team);
 

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

найдите команды, в которых нет ни одного спортсмена, который достиг финишной черты на равнинном этапе первым или вторым.

Ну, это звучит как НЕ СУЩЕСТВУЕТ:

 SELECT * FROM teams
WHERE NOT EXISTS(
    SELECT * FROM arrival_order
    JOIN cyclists ON (cyclists.id=arrival_order.cyclist_id)
    JOIN tape ON (tape.name=arrival_order.tape_name)
    WHERE arrival_order.order <= 2
     AND  tape.type = 'flat'
     AND  cyclists.team = team.name
)
 

Вы также можете использовать ЛЕВОЕ СОЕДИНЕНИЕ, но это было бы сложнее, и postgres, вероятно, в любом случае превратит ваше NOT EXISTS в ЛЕВОЕ СОЕДИНЕНИЕ для оптимизации.

Примечание. если в команде нет велосипедистов или ее велосипедисты не участвовали ни в каких гонках на равнинах, команда все равно будет в результате… потому что именно это было задано в вопросе: «в команде нет ни одного спортсмена, который первым или вторым достиг финишной черты плоского этапа».

Если вы хотите найти команды, в которых есть спортсмены, достигшие финишной черты этапа flat, но не занявшие первое или второе место, тогда вам следует использовать JOIN вместо NOT EXIST и поместить order>2 где-нибудь в WHERE .