Слияние данных из двух таблиц в отчет

Автор Igor, 17 сентября 2012, 15:55

0 Пользователи и 1 гость просматривают эту тему.

Igor

Добрый день!
Подскажите пожалуйста, как можно свести остатки из таблиц Приход и Расход в отчет?


[вложение удалено Администратором]

JohnSUN

#1
Если я правильно понял твою проблему, то для отчета ты хочешь получить что-то вроде
Выбрать все "НазваниеПродукта", которые встречаются в "Запрос_Приход" или "Запрос_Расход"
и
для каждого из них выбрать "Количество" из "Запрос_Приход" (назвать  "Приход")
и
для каждого из них выбрать "Количество" из "Запрос_Расход" (назвать  "Расход")

Будет проще, если первую строку ты оформишь отдельным запросом
SELECT * FROM
( SELECT "НазваниеПродукта" FROM "Запрос_Приход"
UNION
SELECT "НазваниеПродукта" FROM "Запрос_Расход" )
и назовешь его, скажем, "Запрос_Названия"

Тогда окончательный результат можно получить запросом вида
SELECT "Запрос_Названия"."НазваниеПродукта"
       , "Запрос_Приход"."Количество" AS "Приход"
       , "Запрос_Расход"."Количество" AS "Расход"
FROM { OJ "Запрос_Названия"
LEFT OUTER JOIN
"Запрос_Приход" ON "Запрос_Названия"."НазваниеПродукта" = "Запрос_Приход"."НазваниеПродукта"
LEFT OUTER JOIN
"Запрос_Расход" ON "Запрос_Названия"."НазваниеПродукта" = "Запрос_Расход"."НазваниеПродукта" }
Владислав Орлов aka JohnSUN
Благодарить-не зазорно.
Подарить благо создателям офиса, нашему ресурсу, мне

Igor

Здравствуйте! JohnSUN
Да что-то вроде этого за некоторыми исключениями данные по количеству необходимо представить в едином столбце "Остаток" т. е. приход минус расход и с точностью до тысячных.
Думаю что проблему, которую я себе создал нужно изложить конкретней!
По сути дела хочу создать и вести базу складского учета, где имеются таблицы: приход, расход, резерв, заявки (само собой таблицы - товары, клиенты, типы упаковок и т.д.).  С базами ранее не сталкивался (работу выполнял в Excel). Предложенный Вами вариант имеет каскад запросов и как это скажется на скорости работы при расширении базы? Ранее полагал выборку остатков производить при помощи запроса из таблиц приход, расход, резерв, заявки (пока "тренируюсь" на приход, расход) т.е. при каждом обращении к запросу он всякий раз пересчитывает все таблицы и выводит в отчет. Сейчас пришла идея создать еще таблицу Остаток. Запросом выбирать данные из таблиц +приход, -расход, -резерв, -заявки и вносить в таблицу Остаток и далее из нее через запрос в отчет. Подскажите, как правильно реализовать мою затею.

JohnSUN

И тебе доброго здоровья, Igor!
Цитата: Igor от 18 сентября 2012, 10:05
Да что-то вроде этого за некоторыми исключениями данные по количеству необходимо представить в едином столбце "Остаток" т. е. приход минус расход и с точностью до тысячных.
Упс! До тысячных? Тогда зачем понижал точность в  "Запрос_Приход" и  "Запрос_Расход"? Пусть считают "как есть", тогда и в результирующем запросе все значения будут с нужной точностью. А Остаток можно вычислить прямо в запросе:
SELECT "Запрос_Названия"."НазваниеПродукта" AS "Название продукта"
      , "Запрос_Приход"."Количество" AS "Приход"
      , "Запрос_Расход"."Количество" AS "Расход"
      , "Запрос_Приход"."Количество"-"Запрос_Расход"."Количество" AS "Остаток"
FROM { OJ "Запрос_Названия"
  LEFT OUTER JOIN "Запрос_Расход" ON "Запрос_Названия"."НазваниеПродукта" = "Запрос_Расход"."НазваниеПродукта"
  LEFT OUTER JOIN "Запрос_Приход" ON "Запрос_Названия"."НазваниеПродукта" = "Запрос_Приход"."НазваниеПродукта" }

Цитата: Igor от 18 сентября 2012, 10:05
Думаю что проблему, которую я себе создал нужно изложить конкретней!
По сути дела хочу создать и вести базу складского учета, где имеются таблицы: приход, расход, резерв, заявки (само собой таблицы - товары, клиенты, типы упаковок и т.д.). 
Хорошая задача, увлекательная. Какова задача таблиц "резерв" и "заявки"? Условный отпуск и условное получение товара? Тогда есть ли смысл заводить отдельные таблицы? Может, стоит просто дополнить таблицы "расход" и "приход" каким-нибудь вспомогательным полем "статус", где отмечать реальная это запись движения товара или условная? Если заявка будет выполнена (или резерв будет отгружен) просто изменить статус на реально выполненное действие да и дело с концом - не нужно переносить данные из таблицы в таблицу, как-то закрывать выполненные заявки, прочая возня....
Цитата: Igor от 18 сентября 2012, 10:05С базами ранее не сталкивался (работу выполнял в Excel).
Ну, это у нас с тобой общее: я тоже с ними не сталкивался, предпочитал таблицы Калка...
Цитата: Igor от 18 сентября 2012, 10:05Предложенный Вами вариант имеет каскад запросов и как это скажется на скорости работы при расширении базы?
Во-первых, не должно сказаться до много-многократного расширения. Но в этом случае можно или попытаться оптимизировать запрос, или улучшить структуру базы введением дополнительных индексов, или перенести базу на другой движок. 
Во-вторых, здесь речь шла о запросе для отчета. То есть выполняется он один раз в день, в неделю, в месяц... И потому нет разницы, сколько придется ждать результата - то ли 5 секунд, то ли полторы минуты.
И в-третьих - прекрати "выкать": общение "на ты" гораздо ненапряжнее и быстрее.
Цитата: Igor от 18 сентября 2012, 10:05Ранее полагал выборку остатков производить при помощи запроса из таблиц приход, расход, резерв, заявки (пока "тренируюсь" на приход, расход) т.е. при каждом обращении к запросу он всякий раз пересчитывает все таблицы и выводит в отчет. 
Нормальный подход. Конечно запрос будет просматривать все нужные таблицы - иначе где он возьмет данные? Экселевские таблицы поступают точно также, когда ты используешь ВПР() или СУММЕСЛИ().
Цитата: Igor от 18 сентября 2012, 10:05Сейчас пришла идея создать еще таблицу Остаток. Запросом выбирать данные из таблиц +приход, -расход, -резерв, -заявки и вносить в таблицу Остаток и далее из нее через запрос в отчет. Подскажите, как правильно реализовать мою затею.
Тоже нормальный подход. В терминах 1С эта таблица называется Регистр. В них накапливаются все движения по определенным операциям и выдергиваются для отчета легко и быстро. Сложнее в реализации, чем выборка данных по отдельным таблицам, но тоже имеет право на существование. Делать или не делать - решай сам
Владислав Орлов aka JohnSUN
Благодарить-не зазорно.
Подарить благо создателям офиса, нашему ресурсу, мне

Hasim

Можно и попроще, воспользовавшись уже имеющимися у тебя запросами "Запрос_Приход", "Запрос_Расход", и добавив еще один запрос "Запрос_Приход_Расход" и по нему построив отчет. Нужное число десятичных знаков в отчете делается форматированием соответствующего поля отчета ("Остаток", например).


[вложение удалено Администратором]

Hasim

Цитата: Igor от 18 сентября 2012, 09:05Сейчас пришла идея создать еще таблицу Остаток. Запросом выбирать данные из таблиц +приход, -расход, -резерв, -заявки и вносить в таблицу Остаток и далее из нее через запрос в отчет.
А вот это не очень хорошая идея. В базах данных в таблицах нужно хранить только уникальные данные, не зависящие от других таблиц. Ваша идея возможна, но опасна - нужно постоянно следить за вводом новых данных или изменением старых, чтобы данные в таблице Остаток не устарели,  и для реализации потребует программирования.

И еще. Следовало бы создать отдельную таблицу с Перечнем Продуктов с ID, и в таблицах "Приход" и "Расход" вместо ввода каждый раз вручную  названий Продуктов (что опасно с точки зрения возможности орфографической ошибки) выбирать их из списка на основе этой таблицы ("Перечень_Продуктов"). Кстати, при изменении вдруг названия какого-либо продукта потребуется изменить его только один раз в этой таблице с Перечнем Продуктов.

Igor

На "ты" удобнее переходить когда тебя об этом просит собеседник, а сначала не хотелось проявить неуважение.
   По предпоследнему примеру: сейчас у меня так данные отражаются в Excel, называю отчет движение за период (период может быть любым). Но хотел бы что бы отчет содержал чистые остатки (хотя я не определился "нужно ли?").
   Я занимаюсь продажами, условный приход и расход мне ненужен. Назначение таблиц "Резерв" это учет кол-ва оплаченного но не отпущенного товара, а "Заявки" это учет кол-ва товара на который выставлены счета на оплату (могут купить, но не обязательно) т.е. учет кол-ва товара свободного к продаже. Примеры приведенные тобой буду прорабатывать. я далек от СУБД и тяжело даются принципы организации данных в базе, поэтому иду от простого к сложному и как оказывается топчусь на месте, в том смысле что создал некие таблицы с полями, формы для их заполнения (тут все просто) оставляю в сыром виде перехожу к сложному (организация запроса, создание отчета - типа на бланке "акт возврата" дизайнер отчета не позволяет печатать как в текстовом редакторе) и тут тупик. Подумываю что для начала надо схематично проработать все операции с товаром что бы правильно организовать данные в таблицах.
   в добавок к последнему сообщению: в первом ответе я писал По сути дела хочу создать и вести базу складского учета, где имеются таблицы: приход, расход, резерв, заявки (само собой таблицы - товары, клиенты, типы упаковок и т.д.).

JohnSUN

Цитата: Hasim от 18 сентября 2012, 13:17
Можно и попроще...
В данном случае можно, но не нужно: простота хуже воровства.
Причину можно понять, посмотрев дополненный вариант тестовой базы.
Условие WHERE <Таблица1.Поле>=<Таблица2.Поле> означает внутреннее объединение таблиц (может быть записано как INNER JOIN). В результат попадут только те записи, которые есть в обеих таблицах. То есть, если был приход Продукт3, но не было ни одного расхода, то и строка  этим товаром в отчете не появится... И что сделает кладовщик? Закажет Продукт3 еще раз, поскольку уверен, что остатков по нему просто нет.

Мой вариант запроса тоже оказался не очень хорош: не учел, что если один из операндов NULL, то и все выражение (Остаток) будет NULL. Поэтому нужно каждый из них приводить с помощью функции IFNULL

[вложение удалено Администратором]
Владислав Орлов aka JohnSUN
Благодарить-не зазорно.
Подарить благо создателям офиса, нашему ресурсу, мне

Hasim

Цитата: JohnSUN от 18 сентября 2012, 15:34В данном случае можно, но не нужно: простота хуже воровства.
Это верно. Торопиться не надо было.

Igor

Цитата: JohnSUN от 18 сентября 2012, 12:01Тогда зачем понижал точность в  "Запрос_Приход" и  "Запрос_Расход"? Пусть считают "как есть", тогда и в результирующем запросе все значения будут с нужной точностью
можно по подробней "так сказать носом ткнуть", где вышло так что я понизил точность. в полях таблиц тип данных decimal, формат 0,000 запрос же округляет до сотых. проблема была изложена в предыдущей теме: "изменение точности данных в запросе" с которой успешно справился Hasim за что ему и Спасибо!

JohnSUN

А? А, да... Верно... Не обращай внимания - температура у меня, разный бред морозю...
Ну, Hasim прав: то что результат запроса отображается только с двумя знаками - ничего не значит. Выдели колонку результата, щелкни по заголовку правой кнопкой да и измени формат колонки на числовой с тремя знаками после точки... Тебе-то это не принципиально, в отчете все равно будешь форматы задавать, там и выставишь нужный
Владислав Орлов aka JohnSUN
Благодарить-не зазорно.
Подарить благо создателям офиса, нашему ресурсу, мне

Igor

Цитата: JohnSUN от 18 сентября 2012, 22:08Выдели колонку результата, щелкни по заголовку правой кнопкой да и измени формат колонки на числовой с тремя знаками после точки
это временное решение тем более сам запрос я открывать не планирую.
Цитата: JohnSUN от 18 сентября 2012, 22:08в отчете все равно будешь форматы задавать, там и выставишь нужный
Вот тут по подробней, мастером создал отчет а как дальше настроить не знаю. с запросами и отчетами мне пока тяжело нежели с таблицами и формами.