Я надеюсь, что следующий примерный код не требует пояснений:
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);
select T2.ID AS T2ID
,T2.Name as T2Name
,T2.Orders
,T1.ID AS T1ID
,T1.Name As T1Name
,T1Sum.Price
FROM @t2 T2
INNER JOIN (
SELECT Rel.t2ID
,MAX(Rel.t1ID)AS t1ID
-- the MAX returns an arbitrary ID, what i need is:
-- ,ROW_NUMBER()OVER(Partition By Rel.t2ID Order By Price DESC)As PriceList
,SUM(Price)AS Price
FROM @t1 T1
INNER JOIN @relation Rel ON Rel.t1ID=T1.ID
GROUP BY Rel.t2ID
)AS T1Sum ON T1Sum.t2ID = T2.ID
INNER JOIN @t1 T1 ON T1Sum.t1ID=T1.ID
Результат:
T2ID T2Name Orders T1ID T1Name Price
1 aaa 25 2 BBB 350,00
2 bbb 35 3 CCC 100,00
Что мне нужно, прокомментировано выше, способ получить ROW_NUMBER
, но также и Group By
в первую очередь. Поэтому мне нужно sum
всех T1-цен, сгруппированных по T2.ID
в таблице отношений и во внешнем запросе t1ID
с самой высокой ценой.
Другими словами: Как изменить MAX(Rel.t1ID)AS t1ID
на несколько возврат идентификатора с самой высокой ценой?
Итак, желаемый результат (обратите внимание, что первый T1ID изменился с 2 на 1, так как он имеет более высокую цену):
T2ID T2Name Orders T1ID T1Name Price
1 aaa 25 1 AAA 350,00
2 bbb 35 3 CCC 100,00
Примечание: в случае, если вам интересно, почему я не умножаю Orders
с ценой: они не реализованы (поэтому я должен был оставить этот столбец, так как это немного неоднозначно, пожалуйста игнорировать его, я только что добавил его, чтобы сделать все менее абстрактным). На самом деле Orders
должен оставаться неизменным, что причина подхода подзапроса к объединению и с тем, почему мне нужно группировать в первую очередь.
Заключение: очевидно, на ядро моего вопроса можно ответить OVER
статья, которая может быть применяется к любой агрегатной функции, например sum
(см. Damien answer), что было ново для меня. Спасибо всем за ваши рабочие подходы.