Oracle, типичные задачи SQL. Гарантированный выбор

На носу выборы, а это значит, что сегодня мы будем выбирать губернатора. А точнее назначать барьер прохождения кандидатов в следующий тур. Но сначала надо определиться с терминами. Что такое гарантированный выбор в SQL? Допустим, в условии запроса к таблице выполняется сравнение какого-либо поля с какой-нибудь переменной. В зависимости от значения этой переменной запрос может вернуть строки из таблицы, а может и не вернуть их вовсе. Если выпадет такое значение переменной, что строки из таблицы не возвращаются, то для этого случая надо специально сгенерировать заранее определенный левый результат. То есть в любом случае общий запрос должен гарантированно что-нибудь да вернуть. Сам термин взят отсюда. Однако задача усложняется тем (а может и наоборот, упрощается), что вместо одной простой ячейки со значением, нам нужно гарантировано вернуть полноценную строку.

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

create table election  as   with t (id, name, profession, votes) as ( select 1, 'Неподкупный Аморал Чисторукович', 'прокурор', 9867 from dual union all select 2, 'Эффективный Бюджет Освоилович', 'бизнесмен', 8650 from dual union all select 3, 'Правдивый Чтодадут Написайлович', 'редактор газеты', 745 from dual union all select 4, 'Благообразная Люцифера Феоктистовна ', 'настоятельница', 234 from dual union all select 5, 'Хренсгоры Ктотакой Никтонезнаевич', 'учитель сельской школы', 3 from dual ) select * from t;  alter table election add primary key (id); 

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

select * from election where votes > :bound 

Предположим, что проходной барьер равен 8000 голосов. Сбиндив это число с :bound, получаем

Но что если проходной барьер 10 000, то есть больше, чем максимально набранное количество голосов? Тогда очевидно, что из вышеозначенных кандидатов в следующий тур не проходит никто. В этом случае устанавливается диктатура и губернатором области автоматически становится кот Полковник. Вот некоторые из способов его назначения:

Способ 1. UNION ALL таблицы с агрегированной собой же

with t as (   select *   from election    where votes > :bound ) select id, name, profession, votes from t  union all select 0, 'Полковник', 'кот', null from t having count(*) = 0 order by votes desc 

Способ 2. UNION ALL таблицы с DUAL

with t as (   select *   from election    where votes > :bound ) select id, name, profession, votes  from t union all select 0, 'Полковник', 'кот', null  from dual  where not exists (select null from t) order by votes desc 

Способ 3. LEFT JOIN таблицы с DUAL

select nvl2(e.rowid, e.id, 0) id,        nvl2(e.rowid, e.name, 'Полковник') name,        nvl2(e.rowid, e.profession, 'кот') profession,        e.votes from dual d  left join election e on e.votes > :bound order by e.votes desc 

Способ 4. Моделька с котом.

select id, name, profession, votes from election where votes > :bound model dimension by (rownum rn) measures (id, name, profession, votes) rules (   id[1] = nvl(id[1], 0),   name[1] = nvl(name[1],  'Полковник'),   profession[1] = nvl(profession[1],'кот') ) order by votes desc 

Ниже гарантированный результат при бинде со слишком большим барьером 10 000

Ясно, что если задать начальную планку 8000, то эти запросы также отработают корректно.

На этом пока всё. Все совпадения имен персонажей с реальными людьми считать случайными.

Решение некоторых других типичных задач SQL можно посмотреть здесь и здесь.

До новых встреч.

ссылка на оригинал статьи http://habrahabr.ru/post/269281/