Простой SQL запрос c EXISTS/IN

Question

Всем привет, осваиваю подзапросы. Есть простая таблица с президентами, года участия в выборах и результат W, L (winner,loser).

Нужно найти таких кандидатов, которые не проиграли, перед тем как выиграть. Т.е. подходят варианты: W и WW Не подходят: LW

Сначала делаю запрос с подзапросом, что бы выявить тех, кто сначала проиграл, а потом выиграл. LW

SELECT candidate
FROM election as loser
WHERE winner_loser_indic = 'L'
AND EXISTS
(
SELECT candidate
FROM election as loser
WHERE winner_loser_indic = 'W'
AND
loser.candidate = candidate
)

Далее делаю запрос кто выиграл и его при этом нету в запросе LW.

SELECT candidate
FROM election 
WHERE winner_loser_indic = 'W'
AND candidate NOT IN 
(
SELECT candidate
FROM election as loser
WHERE winner_loser_indic = 'L'
AND EXISTS
(
SELECT candidate
FROM election as loser
WHERE winner_loser_indic = 'W'
AND
loser.candidate = candidate
)
)

Но ответ получается не полный, не выводит всех WW кандидатов, а некоторых выводит. Как такое может быть?

Answer 1

SELECT candidate
FROM election 
GROUP BY candidate
HAVING MIN(winner_loser_indic) = 'W'

Ещё возможно, что требуется

SELECT DISTINCT t1.candidate
FROM election t1
WHERE NOT EXISTS ( SELECT NULL
                   FROM election t2
                   WHERE t1.candidate = t2.candidate
                     AND t1.election_year > t2.election_year
                     AND t2.winner_loser_indic = 'L' )
  AND t1.winner_loser_indic = 'W'

Answer 2

Исходя из того, что нужно найти "кандидатов, которые не проиграли, перед тем как выиграть", нужно смотреть на предыдущее по дате значение winner_loser_indic. Если нужно написать запрос используя только подзапросы, то решение будет следующим:

SELECT
    candidate
FROM (
    SELECT
        e.candidate,
        e.winner_loser_indic,
        (SELECT e1.winner_loser_indic
         FROM election e1
         WHERE e.candidate = e1.candidate
            AND e1.election_year = (SELECT MAX(e2.election_year)
                                    FROM election e2
                                    WHERE e.candidate = e2.candidate
                                        AND e.election_year > e2.election_year)
         ) next_indic
    FROM election e
) x
WHERE winner_loser_indic = 'W' AND COALESCE(next_indic, next_indic, 'W') = 'W'
GROUP BY candidate;

Если ваша версия СУБД поддерживает оконные функции, решение будет чуть проще для понимания. Но два варианта делают одно и тоже, находят следующее по дате значение winner_loser_indic, затем условие winner_loser_indic = 'W' AND COALESCE(next_indic, next_indic, 'W') = 'W' отфильтровывает ненужное:

SELECT
    candidate
FROM (
    SELECT
        candidate,
        election_year,
        winner_loser_indic,
        LEAD(winner_loser_indic) OVER(PARTITION BY candidate ORDER BY election_year DESC) next_indic
    FROM election
) x
WHERE winner_loser_indic = 'W' AND COALESCE(next_indic, next_indic, 'W') = 'W'
GROUP BY candidate;

Нужно пояснить COALESCE(next_indic, next_indic, 'W'). Если кандидат первый раз участвует в выборах и сразу же побеждает(т.е. предыдущего значения результатов нет), то, он нам подходит.

Answer 3

Сработало ...

SELECT candidate, election_year
FROM election
WHERE winner_loser_indic = 'W'
AND candidate NOT IN
(
SELECT DISTINCT winner.candidate
FROM election as loser, election as winner
WHERE 
loser.candidate = winner.candidate
AND
loser.winner_loser_indic = 'L'
AND 
winner.winner_loser_indic = 'W'
AND
loser.election_year < winner.election_year
)```

БЛОГ НА HUSL

Простой SQL запрос c EXISTS/IN

Ответы (3 шт):