sql - 如何组合 GROUP BY 和 ROW_NUMBER？

Question

我希望以下示例代码是不言自明的:

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按 T2.ID 分组的所有 T1 价格的在关系表和外部查询中 t1ID以最高的价格。

换句话说:如何更改 MAX(Rel.t1ID)AS t1ID以某种方式返回价格最高的 ID？

所以期望的结果是(注意第一个 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 clause回答可应用于 任何 聚合函数如 SUM (见 Damien's answer)对我来说是新的。感谢大家的工作方法。

Answer 1

哇，其他答案看起来很复杂 - 所以我希望我没有遗漏一些明显的东西。

您可以使用 OVER/PARTITION BY针对聚合，然后他们将在没有 GROUP BY 的情况下进行分组/聚合条款。所以我只是将您的查询修改为:

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

这给出了请求的结果集。