Запрос с суммированием записей разных таблиц

[MoreHate]

Пишу простенькую бухгалтерскую программку. В ней есть такая вещь как "расход".
Расход = списания брака + денежная выручка

Получаются такие таблички:

Списания (OUT_ALLOWS):
ID | A_Date | A_Sum

Выручки(OUT_GAINS):
ID | G_Date | G_Sum

Выручек и списаний в день может быть много.

Так вот, нужно вывести таблицу Расхода в таком виде

Дата | Выручка | Сумма списаний за день | Общая сумма


Т.е. нужно сделать

Код: Выделить всё

SELECT A.A_DATE, SUM(A.A_SUM), G.G_DATE, SUM(G.GAIN) FROM OUT_ALLOWS A, OUT_GAINS G  GROUP BY A.A_DATE, G.G_DATE

+
соединить данные по датам.

Знающие люди посоветовали cначала сгруппировать и суммировать, чтобы дата в обоих таблицах стала уникальной, потом соединять.
Заодно дали такой кусок кода

Код: Выделить всё

SELECT DT, A_SUM1, GAIN1	
FROM ( SELECT A_DATE AS DT, SUM(A_SUM) AS A_SUM1, 	
  FROM OUT_ALLOWS	
  GROUP BY A_DATE	
) A	
FULL JOIN ( SELECT G_DATE AS DT, SUM(GAIN) AS GAIN1	
  FROM OUT_GAINS	
  GROUP BY G_DATE	
) G	
ON A.DT = G.DT

Но он для MSSQL.

Так вот, как я не бился, так и не смог переписать его для FIREBIRD.

p.s. Статью http://www.ibase.ru/devinfo/temptables.htm читал. Но толку это не дало.

[kdv]

логин MoreHate удален за недопустимое имя. Выберите, пожалуйста, что-нибудь более приличное, и зарегистрируйтесь еще раз.

[NoHate]

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

[Dimitry Sibiryakov]

Т.е. нужно сделать
+
соединить данные по датам.

Ну так и соединяй:

Код: Выделить всё

SELECT A.A_DATE, SUM(A.A_SUM), G.G_DATE, SUM(G.GAIN) FROM OUT_ALLOWS A FULL JOIN OUT_GAINS G ON A.A_DATE=G.G_DATE GROUP BY A.A_DATE, G.G_DATE

[WildSery]

Ну так и соединяй:

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

2 NoHate:
А нельзя процедурой сделать, обязательно запрос с клиента?

[Dimitry Sibiryakov]

Логично. Тогда либо derived tables для двойки или вьюхи.

[NoHate]

Прошу прощения за несколько ламерские вопросы. С FB работаю недавно и еще не успел со всем разобраться. Поэтому буду благодарен, если вы кое-что мне разъясните. Или в линки ткнете.

А нельзя процедурой сделать, обязательно запрос с клиента?

Запрос идет с клиента.
Процедуру разве нельзя вызвать с клиента?
Хотя бы с помощью EXECUTE PROCEDURE.

Еще вопрос по процедурам. Почему, когда я писал процедуру, у меня всегда выводилась только одна строка (первая)? Могу код показать,но думаю, дело не в нем, т.к. такое было при любой процедуре.

Логично. Тогда либо derived tables для двойки или вьюхи.

У меня FB 1.5.
А что за вьюхи? По слову view ничего не нашел. В http://ibase.ru/develop.htm покопался, но тоже ничего не обнаружил (на первый взгляд).

Т.е. в моем случае выход только один: через эти самые "вьюхи"?

[kdv]

Хотя бы с помощью EXECUTE PROCEDURE.

да хоть с помощью select * from procedure.

Еще вопрос по процедурам. Почему, когда я писал процедуру, у меня всегда выводилась только одна строка (первая)? Могу код показать,но думаю, дело не в нем, т.к. такое было при любой процедуре.

а ты доку почитай, datadef.pdf или книжку Хелен. примеры в employee.fdb посмотри.

А что за вьюхи? По слову view ничего не нашел

гм. я ж не буду на сайте цитировать всякие азы из документации.

[CyberMax]

Еще вопрос по процедурам. Почему, когда я писал процедуру, у меня всегда выводилась только одна строка (первая)?

suspend надо делать для каждой строки.
Пример:

Код: Выделить всё

for select id, name from sometable
  into :id, :name do
  begin
    -- некоторые действия
    suspend;
  end  

А что за вьюхи?

Цитата из "Мир InterBase":
"Представление (VIEW) - это виртуальная таблица, созданная на основе запроса к обычным таблицам."
В общем, почитай литературу по SQL. Потом задавай вопросы.

[Merlin]

Т.е. в моем случае выход только один: через эти самые "вьюхи"?

ГУСАРЫ, МАЛЧАТЬ

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

Процедуру разве нельзя вызвать с клиента?
Хотя бы с помощью EXECUTE PROCEDURE.

Можно. И можно через Select From Procedure.

Еще вопрос по процедурам. Почему, когда я писал процедуру, у меня всегда выводилась только одна строка (первая)?

Потому что вызывал через Execute Procedure и в теле не ставил Suspend.

А что за вьюхи? По слову view ничего не нашел.

Ищи в доке. В Language Reference и в Data Definition Guide. И не только view.

[NoHate]

Слепил я запрос. Очень криво, как мне кажется.

Сделал две вьюхи:

OUT_ALLOWS_SUM

Код: Выделить всё

CREATE VIEW OUT_ALLOWS_SUM(
    DT,
    ALLOWS_SUM)
AS
select A_DATE AS DT, SUM(A_SUM) AS ALLOWS_SUM from OUT_ALLOWS
GROUP BY A_DATE

OUT_GAINS_SUM

Код: Выделить всё

CREATE VIEW OUT_GAINS_SUM(
    DT,
    GAINS_SUM)
AS
select G_DATE AS DT, SUM(GAIN) AS GAINS_SUM from OUT_GAINS
GROUP BY G_DATE

И затем выгреб все такой процедурой:

Код: Выделить всё

ALTER PROCEDURE "OUTPUT" 
RETURNS (
    O_DATE DATE,
    GAINS DOUBLE PRECISION,
    ALLOWS DOUBLE PRECISION,
    O_SUM DOUBLE PRECISION)
AS
DECLARE VARIABLE ADT DATE;
DECLARE VARIABLE ASUM DOUBLE PRECISION;
DECLARE VARIABLE GDT DATE;
DECLARE VARIABLE GSUM DOUBLE PRECISION;
begin
o_sum = 0;
  for SELECT A.DT, ALLOWS_SUM, G.DT, GAINS_SUM
  FROM OUT_ALLOWS_SUM A
  FULL JOIN OUT_GAINS_SUM G
  ON A.DT = G.DT
  into :adt, :asum, :gdt, :gsum
  do
  begin

  if(gsum is null) then
  begin
    gains = 0.0;
    o_date = adt;
    allows = asum;
    o_sum = allows;
  end
  else if(asum is null) then
  begin
    gains = gsum;
    o_date = gdt;
    allows = 0.0;
    o_sum = gsum;
  end
  else
  begin
   o_date = adt;
   gains = gsum;
   allows = asum;
   o_sum = gains+allows;
  end

  suspend;
  end

end

Что можно поправить? Ну кроме временных переменных . Их я и так уберу.

И почему 1+NULL будет NULL? Или это разные типы?

Может быть вообще реально от процедуры отказаться? Уж больно громоздко выглядит запрос в целом: две вьюхи и процедура.

[WildSery]

Код: Выделить всё

{скипаем заголовок}
AS
declare variable adt date;
begin
  for select a.dt, allows_sum, g.dt, gains_sum
    from out_allows_sum a full join out_gains_sum g on (a.dt = g.dt)
    into o_date, allows, adt, gains
  do begin
    if (o_date is null) then o_date=adt;
    if (allows is null) then allows=0;
    if (gains is null) then gains=0;
    o_sum=allows+gains;
    suspend;
  end
end;

кстати, тебе для отчёта нужны только даты, которые есть в этих таблицах, или все даты, даже когда ничего не было?

[CyberMax]

Код: Выделить всё

CREATE VIEW OUT_ALLOWS_SUM( 
    DT, 
    ALLOWS_SUM) 
AS 
select A_DATE AS DT, SUM(A_SUM) AS ALLOWS_SUM from OUT_ALLOWS 
GROUP BY A_DATE

Заменяется на

Код: Выделить всё

CREATE VIEW OUT_ALLOWS_SUM( 
    DT,
    ALLOWS_SUM)
AS 
select A_DATE AS DT, COALESCE(SUM(A_SUM), 0) AS ALLOWS_SUM from OUT_ALLOWS 
GROUP BY A_DATE

То же самое и во втором запросе.
Этим избавляемся от

Код: Выделить всё

    if (allows is null) then allows=0; 
    if (gains is null) then gains=0;

Не забываем пользоваться возможностями FB 1.5!

[WildSery]

Вообще-то эти проверки на нулл нужны не для таблицы (я грешным делом думаю, что раз запись есть, то уж сумма там не null), а для FULL JOIN, когда в одной из таблиц записей не было, там null'ы появятся при соединении.

[CyberMax]

Вообще-то эти проверки на нулл нужны не для таблицы (я грешным делом думаю, что раз запись есть, то уж сумма там не null), а для FULL JOIN, когда в одной из таблиц записей не было, там null'ы появятся при соединении.

Да, я ошибся. Их в самом деле надо в самой процедуре ставить.

[NoHate]

Всем большое спасибо. Все работает нормально.