Требуется помощь в создании запроса.

[Linlees]

Запрос на PostgreSQL
Имееются таблицы:
заказы; поля: номер,  работник_оформивший_заказ, дата_размещения, дата_исполнения, номер_товара, кол-во_заказанного_товара. (В одном заказе может быть только один товар)
поставки; поля: номер,  дата_поставки.
накладные; поля: номер_поставки, номер_товара, кол-во_поставленного_товара. (В одной накладной может быть только один товар).
Необходимо вывести:
Для каждого заказа - номер_товара, количество_заказанного_товара, дата_исполнения, количество товара в наличии на дату исполнения. Упорядочить по дате исполнения и наименованию товара.

Заранее спасибо.

[Dimka]

Больше всего меня радует вот этот шаблон:

Требуется помощь
Дано:...
Нужно:...
Заранее спасибо.

Стандартная форма заказа

[Linlees]

Простите, не поняла юмора...
Что значит: форма заказа?
Если вы имеете ввиду, что данная помощь оказывается платно, то я готова заплатить.

[Джон]

Дорогая Linlees, с этого и надо начинать. Просто есть существенная разница между "помогите" и "сделайте за меня". В названии темы у тебя стоит "помощь", что подразумевает под собой, что ты что-то сделала сама, но это частично или полностью не работает. Значит надо это сделанное показать и попросить помощь, которая будет получена ессно бесплатно. Это основная философия нашего форума. Содержание же темы сводится к простой формулировки задания. В чём именно требуется помощь - не понятно. Это вводит в заблуждение.

Поэтому надо просто называть вещи своими именами не стесняясь, Сразу оговорить условия, сроки (это тоже важно) или срочность исполнения уже теперь ЗАКАЗА, а не ПОМОЩИ и тд. При наличии желающих с достаточным количеством свободного времени я думаю решение будет найдено, при полном непротивлении сторон.

[Linlees]

Вот то, что делала я сама:
SELECT O_DATE AS DATA_ZAKAZA, P_NAME AS TOVAR, SUM(ZAKAZ_NUM) AS ZAKAZANO, SUM(OSTATOK) AS V_OSTATKE
FROM (

SELECT q1.ddate AS O_DATE, q1.PROD_NAME AS P_NAME, q1.number AS ZAKAZ_NUM, (q2.number - q1.number) AS OSTATOK
FROM (
   SELECT p.item AS PROD_NAME, p.id AS product, o.number AS number, o.execution_date AS ddate
   FROM
      orders AS o JOIN products AS p ON(o.product_id=p.id)
   ORDER BY o.execution_date) AS q1
   

   JOIN

      (SELECT c.product_id AS product, d.date AS ddate, SUM(c.number) AS number
      FROM
         consignment AS c JOIN delivery AS d ON(c.delivery_id=d.id)
      GROUP BY d.date, c.product_id
      ORDER BY d.date,c.product_id) AS q2

   ON(q1.product=q2.product AND q1.ddate > q2.ddate)   
ORDER BY q1.ddate, q1.PROD_NAME

   ) AS Q
GROUP BY Q.O_DATE, Q.P_NAME
ORDER BY Q.O_DATE, Q.P_NAME;

consignment - накладная, order - заказ, delivery - поставка.

Но я уверена, что это не правильно.  Мне кажется, что к данному запросу нужно делать какую то дополнительную процедуру, но т.к. мои знания ограничиваются общим курсом БД, преподаваемом в ВУЗе, я прошу помощи.
Подскажите хотя бы в каком направлении двигаться.

[Dimka]

ЗАКАЗЫ: Номер,  Работник оформивший заказ, Дата размещения, Дата исполнения, Номер товара, Количество заказанного товара.

ПОСТАВКИ: Номер,  Дата поставки.

НАКЛАДНЫЕ: Номер поставки, Номер товара, Количество поставленного товара.

Необходимо вывести:

Для каждого заказа: Номер товара, Количество заказанного товара, Дата исполнения, количество товара в наличии на дату исполнения.

Упорядочить по дате исполнения и наименованию товара.

Вот скажи, как по-твоему, достаточно ли информации указано в "Дано", чтобы выполнить "Необходимо"?

[Sla]

Для каждого заказа - номер_товара, количество_заказанного_товара, дата_исполнения, количество товара в наличии на дату исполнения. Упорядочить по дате исполнения и наименованию товара.

Код:

   SELECT p.item AS PROD_NAME, p.id AS product, o.number AS number, o.execution_date AS ddate
   FROM
      orders AS o JOIN products AS p ON(o.product_id=p.id)
   ORDER BY o.execution_date) 

Выбрали продукт и дату исполнения

Кстати ORDER BY здесь не нужен, потому как у тебя далее будет идти join  и, кроме того, это у тебя подзапрос

Код:

      SELECT c.product_id AS product, d.date AS ddate, SUM(c.number) AS number
      FROM
         consignment AS c JOIN delivery AS d ON(c.delivery_id=d.id)
      GROUP BY d.date, c.product_id
      ORDER BY d.date,c.product_id

здесь то же лишний order by

Но принципиальная ошибка, ведь ты используешь объединение запросов
Сравни:
   SELECT p.item AS PROD_NAME, p.id AS product, o.number AS number, o.execution_date AS ddate
   SELECT c.product_id AS product, d.date AS ddate, SUM(c.number) AS number
И скажи, почему я обратил на это внимание?

[Linlees]

Вот скажи, как по-твоему, достаточно ли информации указано в "Дано", чтобы выполнить "Необходимо"?

Ивините,  если чего то не хватает, то скажите чего, я дополню. Но я перечислила все поля, которые содержат эти таблицы.

Сравни:
   SELECT p.item AS PROD_NAME, p.id AS product, o.number AS number, o.execution_date AS ddate
   SELECT c.product_id AS product, d.date AS ddate, SUM(c.number) AS number
И скажи, почему я обратил на это внимание?

Возможно потому, что эти запросы нельзя объединять?
Если я права, то объясните, пожалуйста, почему, т.к. повторюсь - мои знания очень ограничены.

Меня в данном запросе больше всего беспокоит именно подсчет остатков на определенную дату, как это реализовать - я ума не приложу.

[McZim]

Linlees, Что делает SELECT q1.ddate?

[Linlees]

Выводит дату исполнения заказа...

[Sla]

Возможно потому, что эти запросы нельзя объединять?

Почему?

[Linlees]

Почему?

А вот этого я уже не знаю:(

[Sla]

Давай с самого начала (потому как я тут такого ща наговорил, что самому стыдно - union с join перепутал)  разгребать постепенно, а не сразу ваять большой запрос
1. Выбрать
    Заказ, Номер_товара, количество_заказанного_товара, дата_исполнения
2. Выбрать
   номер_товара, количество_поставленного_товара

3. Выбрать
Количество_товара_в наличии

[Linlees]

1. SELECT id, product_id, number, execution_date FROM orders

2. SELECT product_id, number FROM consignment

3. вот тут у меня загвоздка: я не знаю как это можно вычислить.

[Sla]

Linlees, ну а как ты посчитала бы без базы, а на бумажке?

[Linlees]

Нужно проверить на какую дату мы считаем это наличие, потом сосчитать общее количество товари, поставленного на эту дату и вычесть из этого количество товара, заказанного на эту дату.
Если не брать в расчет определенную дату, то запрос на разницу между ВСЕМ поставленным и ВСЕМ заказанным я могу написать

Код:

 SELECT o.product_id, COUNT(c.number), COUNT(o.number) 
FROM orders AS o 
JOIN consignment AS c ON c.product_id=o.product_id)
GROUP BY o.product_id;

   - что-то подобное

а вот как это привязать к определенной дате которая заранее неизвестна ....

[Sla]

а если так?

Код:

SELECT o.product_id, ( COUNT(c.number) - COUNT(o.number) )
FROM orders AS o 
JOIN consignment AS c ON c.product_id=o.product_id)
GROUP BY o.product_id;

[Linlees]

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

[Sla]

А теперь
1. Выбрать
    Заказ, Номер_товара, количество_заказанного_товара,
где дата_исполнения меньше 01/01/2009

2. Выбрать
   номер_товара, количество_поставленного_товара, дата_поставки
где дата_поставки меньше 01/01/2009

[Linlees]

1.SELECT product_id, number FROM orders WHERE (execution_date > 2009-01-01);

2.SELECT c.product_id, c.number, d.date FROM consignment AS c JOIN delivery AS d ON (c.product_id=d.product_id) WHERE (d.date > 2009-01-01); - возможно здесь нужен GROUP BY...

[Sla]

SELECT c.product_id, c.number, d.date
     FROM consignment AS c
      JOIN delivery AS d
on (c.product_id=d.product_id) WHERE (d.date < 2009-01-01)

А зачем group by? У тебя здесь есть агрегатные функции?

МЕНЬШЕ!

[Linlees]

Ой, простите, это моя невнимательность.

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

[Dimka]

Ивините,  если чего то не хватает, то скажите чего, я дополню. Но я перечислила все поля, которые содержат эти таблицы.

Да, но в таблицах нет непосредственно запрашиваемого результата. Дальше Sla уже понял главную проблему:

а как ты посчитала бы без базы, а на бумажке?

Нужно проверить на какую дату мы считаем это наличие, потом сосчитать общее количество товари, поставленного на эту дату и вычесть из этого количество товара, заказанного на эту дату.

Любой запрос - это последовательные операции над таблицами. Проделай это на бумажке, и станет совершенно очевидно, как написать запрос.

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

ну если честно, я хотела так написать, но подумала, вдруг он так не посчитает, а проверить я к сожалению пока не могу, т.к. постгреса на работе нету

"Вдруг" не бывает. Это ж не гадание на картах.

Возьми по 5 записей с каждой таблицы (лишь бы они сочетались по датам и связям), и на них подбери, какие нужны операции для получения результата:
1) объединение таблиц (и способ объединения - FROM ... JOIN),
2) фильтрация строк (WHERE),
3) группировка (GROUP BY),
4) фильтрация групп (HAVING),
5) отбор столбцов (SELECT), вычисление значений (в том числе функции COUNT, SUM, AVG и т.д.), ликвидация повторов строк (DISTINCT)
6) сортировка (ORDER BY)
Операции применять в указанном порядке.

[Sla]

dimka, тут есть уже сдвиги

Кажется в голове начинает проясняться, но надо проверить на практике.

[Dimka]

Sla, не думаю.

Начать можно с малого:

сосчитать общее количество товари, поставленного на эту дату

Это элементарно для случая, когда дата задаётся параметром запроса. Но вот случай, когда нужно считать накопительную сумму - это уже на догадливость.

Пока опустим разные товары, чтобы не добавлять группировок - отдельная тема.

Допустим, есть таблица
ГРАФИК ПЛАТЕЖЕЙ: Дата платежа, Сумма платежа.

Например:
01.04.09   20000.00
02.04.09   03742.11
03.04.09   01743.37
04.04.09   00003.37
13.04.09   01129.80
14.04.09   15000.00


Требуется написать запрос, который для каждой даты выдаёт накопительный итог:

01.04.09   20000.00
02.04.09   23742.11
03.04.09   25485.48
04.04.09   25488.85
13.04.09   26618.65
14.04.09   41618.65

[Sla]

select field_data, (select sum(field_sum) from table where field_data<=t.field_data) itog
from table t
order by field_data

Не знаю, не проверял, но должно работать
А вот интересно, поможет ли это в нашем случае?

[Linlees]

Практически не удалось посидеть подумать над запросом, результата пока никакого, последний вариант:

Код:

SELECT ord.product_id, ord.number, ord.execution_date, COUNT(ord.number) AS ostatok 
GROUP BY ord.product_id, ord.number, ord.execution_date
HAVING	COUNT(ord.number)=(
			SELECT (COUNT(c.number) - COUNT(o.number)) 
			FROM orders AS o 
			JOIN consignment AS c ON (c.product_id=o.product_id) 
			JOIN delivery AS d ON (c.delivery_id=d.id)
			GROUP BY o.product_id, d.date, o.execution_date 
			HAVING (d.date<ord.execution_date AND o.execution_date<ord.execution_date)
			)
FROM orders AS ord
ORDER BY ord.product_id;

Но он не работает,  выдает ошибку ERROR:  more than one row returned by a subquery used as an expression.

Завтра буду разбираться, что не так.

[Linlees]

В общем это все что у меня получилось
 

Код:

SELECT (SUM(c.number)-SUM(o.number)) 
			FROM orders AS o 
			JOIN consignment AS c ON (c.product_id=o.product_id) 
			JOIN delivery AS d ON (c.delivery_id=d.id)
			GROUP BY o.product_id, d.date, o.execution_date 
			HAVING (d.date<'2009-04-01' AND o.execution_date<'2009-04-01')
			; 

  -это внутренний  подзапрос, с COUNT он не работает, выдает одни нули. А с SUM работает - проверила по значениям из таблицы. Выдает столбец с разницей поставленного и заказанного на эту дату.
К внешнему запросу где нужно выбрать номер_продукта и дату_исполнения мне его никак не прикрутить.
Все время пишет эту ошибку, что подзапрос выдает более чем один ряд. Где я ошиблась?

[Dimka]

select field_data, (select sum(field_sum) from table where field_data<=t.field_data) itog
from table t
order by field_data

Да, это типовое решение. Я только не знаю, есть ли в PostgreSQL поддержка подзапросов, которые работают в секции SELECT.

А ты напиши решение без таких подзапросов. Чтобы. допустим, в FoxPro 2.6 под DOS работало

[Linlees]

Я только не знаю, есть ли в PostgreSQL поддержка подзапросов, которые работают в секции SELECT.

не поддерживает, сегодня это проверила на собственном опыте.

Код:

SELECT  o.product_id, o.execution_date,  (q2.summa1-q1.summa) AS OSTATOK		
FROM		(SELECT product_id,  SUM(number) AS summa
		FROM orders 
		GROUP BY product_id 
		HAVING MAX(execution_date)<'2009-05-23') AS q1
JOIN
		(SELECT product_id,  SUM(number) AS summa1 
		FROM consignment AS c  
		JOIN delivery AS d ON (c.delivery_id=d.id)
		GROUP BY product_id
		HAVING MAX(d.date)<'2009-05-23') AS q2
ON (q1.product_id=q2.product_id)
JOIN orders AS o
ON (o.product_id=q1.product_id)	
GROUP BY o.execution_date, o.product_id, q2.summa1, q1.summa 
ORDER BY o.execution_date;

Осталась проблема привязки к дате_исполнения...