это дополнительный вопрос os this. Поскольку другой отвечает на важную часть (как SUM
по разделам), я оставляю его принятым. Но меня беспокоит другая часть.
Как SUM
] в отношениях "многие ко многим" бедро, если «левая» сторона должна учитываться только один раз.
Рассмотрим следующие примеры данных, которые, надеюсь, объяснят мою проблему:
declare @t1 table (ID int,Price money, Name varchar(10))
declare @t2 table (ID int,Orders int, Name varchar(10))
declare @relation table (t1ID int,t2ID int)
insert into @t1 values(1, 200, 'AAA');
insert into @t1 values(2, 150, 'BBB');
insert into @t1 values(3, 100, 'CCC');
insert into @t2 values(1,25,'aaa');
insert into @t2 values(2,35,'bbb');
insert into @relation values(1,1);
insert into @relation values(2,1);
insert into @relation values(3,2);
-- following record will cause the "wrong" sum
insert into @relation values(2,2);
select
T2.Name as T2Name
,T2.Orders As T2Orders
,T1Sum.Price As T1SumPrice
,T1.Price As T1HighestPrice
,T1.Name As T1HighestPrice_Name
FROM @t2 T2
INNER JOIN (
SELECT Rel.t2ID
,Rel.t1ID
,ROW_NUMBER()OVER(Partition By Rel.t2ID Order By Price DESC)As PriceList
,SUM(Price)OVER(PARTITION BY Rel.t2ID) AS Price
FROM @t1 T1
INNER JOIN @relation Rel ON Rel.t1ID=T1.ID
)AS T1Sum ON T1Sum.t2ID = T2.ID AND t1Sum.PriceList = 1
INNER JOIN @t1 T1 ON T1Sum.t1ID=T1.ID
Фактический результат:
T2Name T2Orders T1SumPrice T1HighestPrice T1HighestPrice_Name
aaa 25 350,00 200,00 AAA
bbb 35 250,00 150,00 BBB
Желаемый результат:
T2Name T2Orders T1SumPrice T1HighestPrice T1HighestPrice_Name
aaa 25 350,00 200,00 AAA
bbb 35 100,00 150,00 BBB
Обратите внимание на разницу в T1SumPrice
.Последняя запись в таблице отношений вставляет T1 в bbb
, который уже был назначен другому T2 (aaa
), следовательно, он уже является частью T1SumPrice
для T2. -group с Name= aaa
.
Итак, как я могу предотвратить двойной подсчет значений, когда они уже являются частью другой группы?
Изменить:
Это результирующий запрос из ответа Николы(который на самом деле является частью табличной функции). Обратите внимание, что мне нужно использовать скалярные функции для получения значений:
select T2.idSparePart As T2_ID
,T2.PartNumber as T2_PartNumber
,Gambio.getGoodsIn(T2.idSparePart,@FromDate,@ToDate) AS GoodsIn
,Gambio.getOrdered(T2.idSparePart,@FromDate,@ToDate) AS Ordered
,T1Sum.ClaimedReused As T1SumPrice_ClaimedReused
,T1Sum.Costsaving As T1SumPrice_Costsaving
,T1.Price As T1HighestPrice
,T1.idSparePart AS T1HighestPrice_ID
,T1.SparePartName As T1HighestPrice_Number
,T1.SparePartDescription As T1HighestPrice_Name
,Cat.SparePartCategoryName As T1HighestPrice_Category
FROM Gambio.SparePart T2
INNER JOIN (
SELECT Rel.fiSparePart
,Rel.fiTabSparePart
,ROW_NUMBER()OVER(Partition By Rel.fiSparePart Order By Price DESC)As PriceList
,SUM(Gambio.getMaterialQuantity(Rel.fiTabSparePart,NULL,NULL,@idClaimStatus,1))OVER(PARTITION BY Rel.fiSparePart) AS ClaimedReused
,SUM(Gambio.getCostSaving(Rel.fiTabSparePart,NULL,NULL,@idClaimStatus))OVER(PARTITION BY Rel.fiSparePart) AS Costsaving
FROM tabSparePart T1
INNER JOIN
(select fiTabSparePart
, fiSparePart
from Gambio.trelSparePartClaimGroup relation
where not exists (
select null
from Gambio.trelSparePartClaimGroup rel
where rel.fiTabSparePart = relation.fiTabSparePart
and rel.fiSparePart < relation.fiSparePart
)
)
Rel ON Rel.fiTabSparePart=T1.idSparePart
)AS T1Sum ON T1Sum.fiSparePart = T2.idSparePart AND t1Sum.PriceList = 1
INNER JOIN tabSparePart T1 ON T1Sum.fiTabSparePart=T1.idSparePart
INNER JOIN tabSparePartCategory AS Cat
ON Cat.idSparePartCategory=T1.fiSparePartCategory
К сожалению, похоже, что некоторые записи T1 пропущены, поэтому я получаю неправильный результат