Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Проблема с join + sum()  [new]
Лесь
Guest
Хотел попросить уважаемое сообщество помочь со скриптом, никак не могу сам сообразить, что же делаю не так. Есть три таблички: в одной предметы, в другой - их наличие в разных локациях, в третьей - перемещения между локациями. Проблема в том, что перемещений может быть несколько, а значит нужно суммировать количество. Количество в локациях уже просуммировано (используется view, но для простоты я сделал в виде таблицы). Для перемещений у меня view отсутствует и требуется справиться без него. "Тестовый стенд" выглядит примерно так:

CREATE TABLE Items(
  ItemID int NOT NULL,
  Name varchar(50) NOT NULL
);

INSERT INTO Items (ItemID, Name) VALUES (1, 'Candy');
INSERT INTO Items (ItemID, Name) VALUES (2, 'Chocolate');
INSERT INTO Items (ItemID, Name) VALUES (3, 'Roll');
INSERT INTO Items (ItemID, Name) VALUES (4, 'Pie');
INSERT INTO Items (ItemID, Name) VALUES (5, 'Pasty');

CREATE TABLE ItemAvail(
  ItemID int NOT NULL,
  Location varchar(1) NOT NULL,
  Quantity int NOT NULL
);

INSERT INTO ItemAvail (ItemID, Location, Quantity) VALUES (1, 'A', 10);
INSERT INTO ItemAvail (ItemID, Location, Quantity) VALUES (1, 'C', 2);
INSERT INTO ItemAvail (ItemID, Location, Quantity) VALUES (3, 'A', 10);
INSERT INTO ItemAvail (ItemID, Location, Quantity) VALUES (3, 'B', 5);
INSERT INTO ItemAvail (ItemID, Location, Quantity) VALUES (4, 'D', 1);
INSERT INTO ItemAvail (ItemID, Location, Quantity) VALUES (4, 'A', 3);
INSERT INTO ItemAvail (ItemID, Location, Quantity) VALUES (5, 'A', 3);
INSERT INTO ItemAvail (ItemID, Location, Quantity) VALUES (5, 'C', 7);
INSERT INTO ItemAvail (ItemID, Location, Quantity) VALUES (5, 'A', 4);

CREATE TABLE dbo.ItemMove(
  ItemID int NOT NULL,
  SrcLocation varchar(1) NOT NULL,
  DstLocation varchar(1) NOT NULL,
  Quantity int NOT NULL
);

INSERT INTO ItemMove (ItemID, SrcLocation, DstLocation, Quantity) VALUES (1, 'A', 'B', 5);
INSERT INTO ItemMove (ItemID, SrcLocation, DstLocation, Quantity) VALUES (3, 'B', 'A', 2);
INSERT INTO ItemMove (ItemID, SrcLocation, DstLocation, Quantity) VALUES (1, 'A', 'C', 1);
INSERT INTO ItemMove (ItemID, SrcLocation, DstLocation, Quantity) VALUES (5, 'C', 'A', 2);
INSERT INTO ItemMove (ItemID, SrcLocation, DstLocation, Quantity) VALUES (4, 'A', 'B', 2);
INSERT INTO ItemMove (ItemID, SrcLocation, DstLocation, Quantity) VALUES (1, 'A', 'B', 1);
INSERT INTO ItemMove (ItemID, SrcLocation, DstLocation, Quantity) VALUES (5, 'C', 'A', 1);


Мой скрипт выборки таков:

select
  I.ItemID,
  I.Name,
  -- наличие (0 если нет)
  isnull(AA.Quantity, 0) as Qty_A,
  isnull(AB.Quantity, 0) as Qty_B,
  isnull(AC.Quantity, 0) as Qty_C,
  -- сколько суммарно будет перемещено (без разницы откуда)
  sum(isnull(DA.Quantity, 0)) as MoveTo_A,
  sum(isnull(DB.Quantity, 0)) as MoveTo_B,
  sum(isnull(DC.Quantity, 0)) as MoveTo_C,
  -- подсчёт итогов перемещения: наличие - сумма расхода + сумма прихода
  isnull(AA.Quantity, 0) - sum(isnull(SA.Quantity, 0)) + sum(isnull(DA.Quantity, 0)) as Total_A,
  isnull(AB.Quantity, 0) - sum(isnull(SB.Quantity, 0)) + sum(isnull(DB.Quantity, 0)) as Total_B,
  isnull(AC.Quantity, 0) - sum(isnull(SC.Quantity, 0)) + sum(isnull(DC.Quantity, 0)) as Total_C
from
  Items I
  -- наличие
  left outer join ItemAvail AA on I.ItemID=AA.ItemID and AA.Location='A'
  left outer join ItemAvail AB on I.ItemID=AB.ItemID and AB.Location='B'
  left outer join ItemAvail AC on I.ItemID=AC.ItemID and AC.Location='C'
  -- приход / movement destination
  left outer join ItemMove DA on I.ItemID=DA.ItemID and DA.DstLocation='A'
  left outer join ItemMove DB on I.ItemID=DB.ItemID and DB.DstLocation='B'
  left outer join ItemMove DC on I.ItemID=DC.ItemID and DC.DstLocation='C'
  -- расход / movement source
  left outer join ItemMove SA on I.ItemID=SA.ItemID and SA.SrcLocation='A'
  left outer join ItemMove SB on I.ItemID=SB.ItemID and SB.SrcLocation='B'
  left outer join ItemMove SC on I.ItemID=SC.ItemID and SC.SrcLocation='C'
group by
  I.ItemID,
  I.Name,
  AA.Quantity,
  AB.Quantity,
  AC.Quantity


Результат таков: Скриншот.
Сверху - то, что имеем. И это неправильно.
А снизу - то, как должно быть. Но у меня не получается этого добиться. Понимаю, что что-то упускаю, только что?
23 ноя 15, 20:47    [18461650]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с join + sum()  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
select
  I.ItemID,
  I.Name,
  -- наличие (0 если нет)
  isnull(a.QuantityAA, 0) as Qty_A,
  isnull(a.QuantityAB, 0) as Qty_B,
  isnull(a.QuantityAC, 0) as Qty_C,
  -- сколько суммарно будет перемещено (без разницы откуда)
  isnull(m.QuantityDA, 0) as MoveTo_A,
  isnull(m.QuantityDB, 0) as MoveTo_B,
  isnull(m.QuantityDC, 0) as MoveTo_C,
  -- подсчёт итогов перемещения: наличие - сумма расхода + сумма прихода
  isnull(a.QuantityAA, 0) - isnull(m.QuantitySA, 0) + isnull(m.QuantityDA, 0) as Total_A,
  isnull(a.QuantityAB, 0) - isnull(m.QuantitySB, 0) + isnull(m.QuantityDB, 0) as Total_B,
  isnull(a.QuantityAC, 0) - isnull(m.QuantitySC, 0) + isnull(m.QuantityDC, 0) as Total_C
from
  Items I left join
  (
   select
    ItemID,
    sum(case when Location = 'A' then Quantity else 0 end) as QuantityAA,
    sum(case when Location = 'B' then Quantity else 0 end) as QuantityAB,
    sum(case when Location = 'C' then Quantity else 0 end) as QuantityAC
   from
    ItemAvail
   group by
    ItemID
  ) a on a.ItemID = I.ItemID left join
  (
   select
    ItemID,
    sum(case when SrcLocation = 'A' then Quantity else 0 end) as QuantitySA,
    sum(case when SrcLocation = 'B' then Quantity else 0 end) as QuantitySB,
    sum(case when SrcLocation = 'C' then Quantity else 0 end) as QuantitySC,
    sum(case when DstLocation = 'A' then Quantity else 0 end) as QuantityDA,
    sum(case when DstLocation = 'B' then Quantity else 0 end) as QuantityDB,
    sum(case when DstLocation = 'C' then Quantity else 0 end) as QuantityDC
   from
    ItemMove
   group by
    ItemID
  ) m on m.ItemID = I.ItemID;
23 ноя 15, 21:37    [18461783]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с join + sum()  [new]
Лесь
Guest
invm, спасибо большое! Да, решение значительно отличается от того, что я пытался сдлать. А можно объяснить, в чём я был не прав - то есть почему мои джоины не фильтровали данные так, как я предполагал и насчитывали лишнее? Хотелось бы разобраться в этом восросе :) Как вижу, "наличие" через множественные case и через множественные join работают одинаково, а вот "подсчёты", в которых у меня используется суммирование, уже не работают.
23 ноя 15, 22:40    [18461924]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с join + sum()  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
Лесь
А можно объяснить, в чём я был не прав - то есть почему мои джоины не фильтровали данные так, как я предполагал и насчитывали лишнее?
Разъясняющий пример:
declare @a table (id int);
declare @b table (id int, g int, v int);

insert into @a values (1);
insert into @b values (1, 1, 1), (1, 2, 1), (1, 2, 1);

select
 a.id, b.v as v1, c.v as v2
from
 @a a left join
 @b b on b.id = a.id and b.g = 1 left join
 @b c on b.id = a.id and c.g = 2;
23 ноя 15, 23:18    [18462010]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить