Форум программистов [Powered by Invision Power Board]

Версия для печати темы
Нажмите сюда для просмотра этой темы в оригинальном формате

Форум программистов > Составление SQL-запросов > Составление сложного запроса к БД

Автор: AntonPavlov 8.6.2014, 21:47

Доброго времени суток!
Помогите, пожалуйста решить следующую проблему.
Есть три таблицы:

Income[id, Sdate, id_elem, amount] (приход деталей, содержит инфо о приходящих на склад деталях, из таблицы Nomenclature, за определенную дату, в определенном количестве),
Outgo[id, Sdate, id_elem, amount] (расход деталей, содержит инфо о расходованных деталях, из таблицы Nomenclature, за определенную дату, в определенном количестве),
Nomenclature[id, name_elem] (справочник деталей).

Необходимо выбрать наименование детали, дата расхода, когда расходованное количество превысило имеющееся количество деталей. Упорядочить по наименованию детали и дате расхода.
Пытаюсь делать так:
а) Создаем представление, которое возвращает разность между количеством каждой когда-либо израсходованной запчасти, и количеством этой же когда-либо пришедшей на склад запчасти:

Код


CREATE VIEW OutMinusInc AS 
select outgo.sdate, nomenclature.id, 
sum(outgo.amount)-sum(income.amount) AS delta 
FROM outgo JOIN nomenclature ON 
outgo.id_elem=nomenclature.id 
JOIN income ON 
income.id_elem=nomenclature.id 
group by outgo.sdate, nomenclature.id;

b) Выводим название запчасти и дату расхода, учитывая, что разность, возвращаемая представлением OutMinusInc, должна быть больше нуля:

Код


SELECT name_elem, outgo.sdate 
FROM outgo JOIN nomenclature ON 
outgo.id_elem=nomenclature.id 
JOIN income ON 
income.id_elem=nomenclature.id 
JOIN OutMinusInc ON 
nomenclature.id=OutMinusInc.id 
WHERE delta>0
GROUP BY outgo.sdate,name_elem 
ORDER BY name_elem, outgo.sdate;

Это работает не верно. Из за того, что происходит группировка по датам (в пункте а), получается неверный результат. Без группировки по дате запрос не работает. Как избавиться от группировки и получить верный результат?

Автор: Akina 8.6.2014, 22:37

Нормализуйте данные - избавьтесь от 2 идентичных таблиц, держите всё в одной (расход - отрицательное количество, например).
Тогда достаточно тривиального запроса, считающего нарастающий итог.

Цитата(AntonPavlov @ 8.6.2014, 22:47

)

Пытаюсь делать так

Подход содержит внутреннюю ошибку - не учитывается порядок обработки данных.

Автор: password 9.6.2014, 00:46

Не написали какая база данных.

Цитата

Нормализуйте данные - избавьтесь от 2 идентичных таблиц, держите всё в одной

Или добавьте в таблицу Outgo поле с таблицы Income.id (с какого прихода взята деталь.)

Можно так же написать тригер на расход, который не позволит снимать детали больше чем есть на складе.

По поводу вашего sql мои наброски (не проверял, может и не работать)

Код


select a.id_elem, z.name_elem, a.Sdate, a.amount
from Outgo a
inner join (select id_elem, sum(amount) as amount from Outgo group by id_elem) cur1 on cur1.id_elem=a.id_elem
inner join (select id_elem, sum(amount) as amount from Income group by id_elem) cur2 on cur2.id_elem=a.id_elem
left join Nomenclature z on z.id_elem=a.id_elem
where cur1.amount>cur2.amount and exists 
  (select 1 from 
     (select sum(dd.amount) as amount from Outgo dd where a.id_elem=dd.id_elem and a.Sdate>=dd.Sdate) dd1
   where cur2.amount<dd1.amount)
order by z.name_elem, a.Sdate

Автор: AntonPavlov 11.6.2014, 16:07

password, спасибо большое за помощь!
НО! Этот запрос тоже работает не совсем корректно.
Проблема в том, что в случае (ниже - примерная иллюстрация, описывающая неподходящую ситуацию):

Код


Дата     Приход     Расход    Запчасть
1.06      1               10           1
2.06      20              2            1

ничего не выведется!
То есть: в случае, если сумма пришедших за все даты запчастей "покрывает" сумму ушедших за все даты запчастей, запрос работает не верно, что иллюстрирует таблица выше.

Автор: password 11.6.2014, 21:19

Тогда попробуйте как то так:

Код


select a.id_elem, z.name_elem, a.Sdate, a.amount
from Outgo a
left join Nomenclature z on z.id_elem=a.id_elem
where exists 
  (select 1 from 
     (select sum(dd.amount) as amount from Outgo dd where a.id_elem=dd.id_elem  and a.Sdate>=dd.Sdate) s,
     (select sum(dd1.amount) as amount from Income dd1 where a.id_elem=dd1.id_elem and a.Sdate>=dd1.Sdate) s1
   where s.amount>s1.amount)
order by z.name_elem, a.Sdate

P.S. еще раз акцентирую внимание на том что нужно добавить в таблицу Outgo поле Income.id, чтобы привязать из какого прихода взята деталь, для упрощения таких выборок и недопущения таких случаев.

Автор: AntonPavlov 11.6.2014, 22:36

Спасибо еще раз, попробую чуть позже!!
А что значит

Код


select 1

?
Насчет добавления поля Income.id - я понял, но, дело в том, что эта вся муть - курсовик.
Структура БД уже утверждена, и, в связи с упоротостью и полной неадекватностью преподавателя, вносить изменения в структуру будет уже теперь крайне проблематично.

Автор: password 11.6.2014, 22:54

Код


...
exists 
  (select 1 from
...

В данном случае нам не важно какое значение в селекте, а важно что выполняется условие в exists
https://www.google.com.ua/?gfe_rd=cr&ei=3rKYU_KZH6Xc8gejt4HYDw&gws_rd=ssl#q=sql+exists