SQL-запрос ОСТАТОК ТОВАРОВ НА СКЛАДЕ

← →
vader © (2005-05-27 14:27) [0]

Помогите пожалуйста оформить SQL-запрос считающий остаток на складе. Имеется изначально такая таблица(см. ниже), где тип, если 0 - приход, если 1 - то расход соответствующего продукта. Приход и расход таким образом храниться в одной таблице и отличается только флагом Тип.

Продукт | Кол-во | Тип
-------------------------
Помидор | 30 | 0
Яблоко | 40 | 0
Мясо | 50 | 0
Сок | 100 | 0
Помидор | 10 | 1
Яблоко | 20 | 1
Мясо | 30 | 1
Помидор | 5 | 1

После запроса надо получить такие выходные данные:

Продукт | Расход | Приход | Осталось(Приход-Расход)
-----------------------------------------------------
Помидор | 15 | 30 | 15
Яблоко | 20 | 40 | 20
Мясо | 30 | 50 | 20
Сок | 0 | 100 | 100

т.е. считается общий расход для каждого продукта, и просто переносятся данные о приходе(где тип=0), а так же(это наверное вычисляемое поле) считается остаток как разница между приходом и расходом.

Чтобы получить приход, нужно выполнить:
SELECT *
FROM products.db
WHERE (tip>0)
ORDER BY name

а для расхода:
SELECT *
FROM products.db
WHERE (Tip>0)
GROUP BY name
ORDER BY name

Но необходимо чтобы всё это было в одном запросе и получились данные в таком виде, как представлено выше. Опыта у меня построения таких сложных отчётов почти нет. Что для этого надо использовать? Вложенные запросы, группировку или что?

← →
Sergey13 © (2005-05-27 14:38) [1]

Попробуй так

SELECT Продукт,sum(кол*(1-тип)) приход, sum(кол)-sum(кол*(1-тип)) расход, sum(кол*(1-тип)) -(sum(кол)-sum(кол*(1-тип))) остаток
FROM products.db
GROUP BY Продукт

← →
msguns © (2005-05-27 14:53) [2]

>Sergey13 © (27.05.05 14:38) [1]

А простым вложением нельзя ?

← →
Sergey13 © (2005-05-27 14:56) [3]

2 [2] msguns © (27.05.05 14:53)
А как вариант не пойдет? Я первое что в голову пришло написАл. Наверное лучшее приходит дольше. 8-)

← →
Johnmen © (2005-05-27 15:00) [4]

>vader ©

Ты бы тип БД уточнил.

← →
Vader © (2005-05-27 15:05) [5]

А БД у меня на Paradox!

← →
Johnmen © (2005-05-27 15:11) [6]

Если строго tip=0 приход, 1 расход, то
SELECT Товар, SUM(количество*(1-tip)) AS Приход, SUM(количество*tip) AS Расход, SUM(количество*(1-tip)-количество*tip) AS Осталось FROM products.db GROUP BY Товар ORDER BY Товар

← →
sniknik © (2005-05-27 15:11) [7]

> А БД у меня на Paradox!
а какого... билла гейтца тогда народ в заблуждение вводиш?

вариант
> Sergey13 © (27.05.05 14:38) [1]
вполне нормальный, немного только упростить надо, он там чересчур "завернул".

← →
Vader © (2005-05-27 15:11) [8]

Sergey13, я попробовал твой вариант... Вроде работает, но что-то данные в итоге не совпадают, считает не правильно...

← →
Johnmen © (2005-05-27 15:14) [9]

Что-то у меня с глазами...:)))
Не заметил Sergey13 © (27.05.05 14:38) [1]

← →
sniknik © (2005-05-27 15:14) [10]

Vader © (27.05.05 15:11) [8]
критерий правильности? выдели сюда товар, его историю по которой считает не правильно, чтобы всем видно было.

← →
Vader © (2005-05-27 15:17) [11]

И опять же,Sergey13, извини пожалуйста, но ты оказался абсолютно прав! РАБОТАЕТ!!! Это я просто немного прокосячил, но только потому, что тип у меня не всегда бывает 0 или 1, тот который 1, у меня меняется в широких масштабах, как быть тогда в таком случае? Что нужно исправить чтобы считалось правильно в том варианте о котором я изначально задумал.

← →
Johnmen © (2005-05-27 15:31) [12]

SELECT Товар, SUM(количество*(1-X)) AS Приход, SUM(количество*X) AS Расход, SUM(количество*(1-X)-количество*X) AS Осталось FROM products.db GROUP BY Товар ORDER BY Товар где X=CAST(0.5-tip AS INT)+tip

← →
Vader © (2005-05-27 15:55) [13]

Спасибо, Johnmen! Я так понял что P.S. твой про X, значит что где X встречается в запросе его надо заменить на то выражение что стоит справа от него. Но это получиться некрасиво в итоге. Можно как-то в запросе вычислять этот X и подставлять потом? Например ч/з SET, или так нельзя?

← →
Johnmen © (2005-05-27 15:59) [14]

>Vader © (27.05.05 15:55) [13]

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

← →
Vader © (2005-05-27 16:13) [15]

Спасибо тебе,Johnmen! Твой вариант работает отлично, то что мне надо. Я и не подумал что так можно, изначально думал, что там надо как-то через группировки делать или с помощью вложенного запроса? А можно ли на самом деле это сделать с помощью вложенного запроса? Про то что SET нет в Paradox я убедился уже сам, попробовал, но тоже об этом не знал. Тут специально подборку статей нашел по SQL, изучу в ближайшее время. Классная вещь! Sergey13, тебе тоже спасибо и за то что первый откликнулся!
Спасибо всем кто помог, этот фрагмент мне нужен был для курсача!

← →
sniknik © (2005-05-27 16:46) [16]

> что там надо как-то через группировки делать или с помощью вложенного запроса?
и групировки и вложенные запросы... только не советую так делать. это пример того как не надо писать запросы
SELECT [Продукт], (SELECT Sum([Кол-во]) FROM [База] b WHERE a.[Продукт]=b.[Продукт] AND [Тип] = 0) AS [Приход], (SELECT Sum([Кол-во]) FROM [База] b WHERE a.[Продукт]=b.[Продукт] AND [Тип] > 0) AS [Расход], (SELECT Sum(CASE WHEN [Тип] = 0 THEN [Кол-во] ELSE -[Кол-во] END) FROM [База] b WHERE a.[Продукт]=b.[Продукт]) AS [Осталось] FROM [База] a GROUP BY [Продукт]
это для MSSQL, парадокс(BDE) не поймет.

А подскажите пожалуйста, если считать, что я принял последний вариант от Johnmen сделать так, что моя таблица вида:

Продукт | Кол-во | Тип
-------------------------

является подчиненной, главной же является таблица вида:

Блюдо | Тип
--------------------------

где вот этот Тип и начинает фигурировать, он у меня сделан для связки двух этих таблиц: мастер-подчиненный. Т.е. имеется какое-то блюдо в мастер-таблице, его "тип" указывает на связь с подчиненной таблицей, в которой этому типу соответствуют те продукты которые входят в приготовлении этого блюда("тип">0, а "тип"=0 означает у меня приход продуктов). Нужно сделать такой запрос, чтобы выводились по нему только те названия блюд из имеющихся продуктов соответственно которых на складе можно приготовить эти блюда на N человек(N задается как параметр в программе).

Я б конечно мог это сделать на BDE ч/з TTable, по привычке, но коли решил на SQL, то хочу доделать и это будет куда более красивый и короткий вариант.

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

Таблицу блюд можно назвать "recepts.db". И можно ли и как это сделать, повторно использовать сформированный запрос. В таком случае я бы сначала использовал запрос предложенный Johnmen-ом, потом бы по типу там посчитав используя recepts для вывода названия возможно ли приготовить такое блюдо и вывести его в результате? Можно так сделать, так будет лучше? Если да, то как повторно использовать в запросе ссылку на уже полученные данные от запроса Johnmen-а?

> Я б конечно мог это сделать на BDE ч/з TTable

через TQuery делается точно так же. Тольк select * запихать надо.

ANB, а ты не подскажешь как надо сделать это? Как написать?
Я сейчас пробую написать вложенный запрос, и сделать это в секции WHERE используя нечто предложенное тем же Johnmen.

> Я б конечно мог это сделать на BDE ч/з TTable
для локального(файлового) типа базы такой какой является парадокс, это было бы и более правильным.

> Я сейчас пробую написать вложенный запрос,
для BDE/Paradox-а? оставь надежду, он вложенных не поддерживает. так что подсказывать нечего... почти. можно только если сохранить запрос в файл .sql
вот тут обсуждалось, правда не совсем это но подробно ;о)
http://delphimaster.net/view/3-1116909077/

Честно говоря, я уже потерял надежду такой сделать запрос. С вложенными не получилось, а тут оказывается и не возможно. Это точно? даже и пытаться не стоит? Замечательно... на BDE ч/з TTable я это ясно представляю как это сделать, только получается всё не так как я хотел, после той краткости что дается при использовании SQL снова погружаться в ту рутину и писать циклы в ломы, но тогда придётся. А всё-таки, точно никак не возможно? ;))

SQL-запрос ОСТАТОК ТОВАРОВ НА СКЛАДЕ Найти похожие ветки