¿Cómo combinar GROUP BY y ROW_NUMBER?

Espero que el siguiente código de muestra se explique por sí mismo:

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 

Resultado:

 T2ID T2Name Orders T1ID T1Name Price 1 aaa 25 2 BBB 350,00 2 bbb 35 3 CCC 100,00 

Lo que necesito se comentó anteriormente, una forma de get el ROW_NUMBER pero también el de Group By en primer lugar. Entonces necesito la sum de todos los precios T1 agrupados por T2.ID en la tabla de relaciones y en la consulta externa el t1ID con el precio más alto.

En otras palabras: ¿Cómo cambiar MAX(Rel.t1ID)AS t1ID para devolver el ID con el precio más alto?

Entonces, el resultado deseado es (observe que el primer T1ID cambió de 2 a 1, ya que tiene el precio más alto):

 T2ID T2Name Orders T1ID T1Name Price 1 aaa 25 1 AAA 350,00 2 bbb 35 3 CCC 100,00 

Nota : en caso de que te estés preguntando por qué no multiplico Orders con Precio: no se vuelven a juntar (así que debería haber dejado esta columna ya que es un poco ambigua, por favor ignórala, la acabo de agregar para hacer todo less abstracto). En realidad, los Orders deben permanecer sin cambios, esa es la razón por la cual el enfoque de subconsulta se une a ambos y la razón por la que necesito agruparme en primer lugar.

Conclusión : obviamente, el núcleo de mi pregunta puede ser respondido por la cláusula OVER que se puede aplicar a cualquier function agregada, como SUM (ver la respuesta de Damien ), lo que era nuevo para mí. Gracias a todos por sus enfoques de trabajo.

Wow, las otras respuestas parecen complejas, así que espero no haberme olvidado algo obvio.

Puedes usar OVER / PARTITION BY contra agregados, y luego harán grouping / aggregating sin una cláusula GROUP BY . Así que acabo de modificar su consulta a:

 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 ,Rel.t1ID -- ,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)OVER(PARTITION BY Rel.t2ID) 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 where t1Sum.PriceList = 1 

Que da el set de resultados solicitado.

La deduplicación (para seleccionar la T1 máxima) y la agregación deben realizarse como pasos distintos. He usado un CTE porque creo que esto lo hace más claro:

 ;WITH sumCTE AS ( SELECT Rel.t2ID, SUM(Price) price FROM @t1 AS T1 JOIN @relation AS Rel ON Rel.t1ID=T1.ID GROUP BY Rel.t2ID ) ,maxCTE AS ( SELECT Rel.t2ID, Rel.t1ID, ROW_NUMBER()OVER(Partition By Rel.t2ID Order By Price DESC)As PriceList FROM @t1 AS T1 JOIN @relation AS Rel ON Rel.t1ID=T1.ID ) SELECT T2.ID AS T2ID ,T2.Name as T2Name ,T2.Orders ,T1.ID AS T1ID ,T1.Name As T1Name ,sumT1.Price FROM @t2 AS T2 JOIN sumCTE AS sumT1 ON sumT1.t2ID = t2.ID JOIN maxCTE AS maxT1 ON maxT1.t2ID = t2.ID JOIN @t1 AS T1 ON T1.ID = maxT1.t1ID WHERE maxT1.PriceList = 1 
 ;with C as ( select Rel.t2ID, Rel.t1ID, t1.Price, row_number() over(partition by Rel.t2ID order by t1.Price desc) as rn from @t1 as T1 inner join @relation as Rel on T1.ID = Rel.t1ID ) select T2.ID as T2ID, T2.Name as T2Name, T2.Orders, T1.ID as T1ID, T1.Name as T1Name, T1Sum.Price from @t2 as T2 inner join ( select C1.t2ID, sum(C1.Price) as Price, C2.t1ID from C as C1 inner join C as C2 on C1.t2ID = C2.t2ID and C2.rn = 1 group by C1.t2ID, C2.t1ID ) as T1Sum on T2.ID = T1Sum.t2ID inner join @t1 as T1 on T1.ID = T1Sum.t1ID 

Sin dudas, esto se puede simplificar, pero los resultados coinciden con sus expectativas.

La esencia de esto es

  • Calcule el precio máximo en un CTE separado para cada t2ID
  • Calcule el precio total en un CTE separado para cada t2ID
  • Combina los resultados de ambos CTE

Declaración de SQL

 ;WITH MaxPrice AS ( SELECT t2ID , t1ID FROM ( SELECT t2.ID AS t2ID , t1.ID AS t1ID , rn = ROW_NUMBER() OVER (PARTITION BY t2.ID ORDER BY t1.Price DESC) FROM @t1 t1 INNER JOIN @relation r ON r.t1ID = t1.ID INNER JOIN @t2 t2 ON t2.ID = r.t2ID ) maxt1 WHERE maxt1.rn = 1 ) , SumPrice AS ( SELECT t2ID = t2.ID , Price = SUM(Price) FROM @t1 t1 INNER JOIN @relation r ON r.t1ID = t1.ID INNER JOIN @t2 t2 ON t2.ID = r.t2ID GROUP BY t2.ID ) SELECT t2.ID , t2.Name , t2.Orders , mp.t1ID , t1.ID , t1.Name , sp.Price FROM @t2 t2 INNER JOIN MaxPrice mp ON mp.t2ID = t2.ID INNER JOIN SumPrice sp ON sp.t2ID = t2.ID INNER JOIN @t1 t1 ON t1.ID = mp.t1ID