4.3. Решение упр.1.
Основная задача
Получить номера и наименования изделий, для которых поставлялась каждая деталь, поставленная для изделия J3.
Визуальный анализ данных
Попытаемся понять, какой результат должен будет вернуть наш, еще не написанный, запрос.
Прежде всего посмотрим, какие детали поставляются для изделия J3.
select distinct n_det
from spj
where n_izd=’J3’;
В этом запросе используется ключевое слово distinct, поскольку нам нужно получить список номеров деталей без повторов.
Этот запрос возвращает
|
P3 |
|
P6 |
Значит, необходимо определить изделия, для которых поставлялась и деталь P3, и деталь P6.
Проверим, для каких изделий поставляется деталь P3 и для каких - P6. Для этого выполним два запроса
select distinct n_izd
from spj
where n_det='P3';
select distinct n_izd
from spj
where n_det='P6';
Первый запрос возвращает все изделия.
Второй запрос возвращает
|
J2 |
|
J3 |
|
J4 |
|
J7 |
Понятно, что это и есть тот результат, который должен будет вернуть наш запрос.
Следует помнить, что эти три запроса решают поставленную задачу только на тех данных, которые сейчас есть в наших таблицах. Нам же нужно написать запрос, который будет работать на любых данных. Выделим более простые подзадачи.
Подзадачи
Подзадача1.
Очевидно, что первой подзадачей будет определение перечня деталей, поставлявшихся для изделия J3.
select distinct a.n_det
from spj a
where a.n_det=’J3’;
В этом запросе используется псевдоним a для таблицы spj.
Подзадача2.
Далее, нам нужно получить номера и наименования изделий, которые в принципе могу быть включены в результат. Это изделия, для которых поставлялись детали из множества подзадачи 1.
Получим сначала только номера изделий
select distinct t.n_izd
from spj t
where t.n_det in ( select b.n_det
from spj b
where b.n_izd='J3');
select distinct t.n_izd, j.name
from spj t
join j on j.n_izd=spj.n_izd
where t.n_det in ( select b.n_det
from spj b
where b.n_izd='J3');
Подзадача3.
Получим перечень деталей, которые поставлялись для некоторого другого изделия (например J2).
select distinct c.n_detfrom spj c
where c.n_izd='J2';
Подзадача 4.
Если изделие J2 удовлетворяет условию основной задачи (изделия для которых поставлялась каждая деталь, поставленная для изделия J3), то деталей, которые поставляются для J3 и не поставляются для J2 не должно быть. Это и будет четвертая подзадача - выбрать номера деталей, которые поставляются для J3 и не поставляются для J2. Для решения этой подзадачи найдем разность множеств, полученных в подзадачах 1 и 3.
from spj b
where b.n_izd='J3'
except
select c.n_det
from spj c
where c.n_izd='J2' ;
Если в полученный запрос вместо 'J2' поочередно подставлять все номера изделий, то для интересующих нас изделий этот запрос должен давать пустое множество. Теперь комбинируя подзадачи 2 и 4 получаем
Решение основной задачи.
select distinct t.n_izd, j.name
from spj t
join j on j.n_izd=spj.n_izd
where t.n_det in ( select b.n_det
from spj b
where b.n_izd='J3')
and not exists ( select b.n_det
from spj b
where b.n_izd='J3'
except
select c.n_det
from spj c
where c.n_izd=j.n_izd );
Ключевой момент - заменяем условие c.n_izd='J2' на условие c.n_izd=j.n_izd.
Полученный запрос выполняется следующим образом :
для каждой строки таблицы spj, для которой первый подзапрос в условии отбора является истиной, выполняется второй подзапрос. Если записей , удовлетворяющих условиям подзапроса не существует( подзапрос не вернул ни одной записи), строка остается в результирующем наборе, иначе удаляется из результирующего набора.