如何结合GROUP BY和ROW_NUMBER？ [英] How to combine GROUP BY and ROW_NUMBER?

问题描述

  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'）; 
插入@ t1值（2，150，'BBB'）; 
插入@ t1值（3，100，'CCC'）; 
插入@ t2值（1,25，'aaa'）; 
插入@ t2值（2,35，'bbb'）; 
插入@relation值（1,1）; 
插入@relation值（2,1）; 
插入@relation值（3,2）; 
 
选择T2.ID AS T2ID 
，T2.Name作为T2Name 
，T2.Orders 
，T1.ID AS T1ID 
，T1.Name由于T1Name 
，T1Sum.Price 
 FROM @ t2 T2 
 INNER JOIN（
 SELECT Rel.t2ID 
，MAX（Rel.t1ID）AS t1ID 
 -  MAX返回一个任意的ID，我需要的是：
  - ，ROW_NUMBER（）OVER（分区按Rel.t2ID顺序按价格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订单T1ID T1Name价格
 1 aaa 25 2 BBB 350,00 
 2 bbb 35 3 CCC 100,00 
  

我需要的是上面的注释，一种获取 ROW_NUMBER ，但也可以先放在 Group By 中。所以我需要在关系表和外部关系中由 T2.ID 分组的所有T1价格的总和以最高价格查询 t1ID 。换句话说：如何改变 MAX（Rel.t1ID）AS t1ID 以稍微返回ID最高价格？



所以期望的结果是（注意，由于价格较高，第一个T1ID从2变为1）：



  T2ID T2Name订单T1ID T1Name价格
 1 aaa 25 1 AAA 350,00 
 2 bbb 35 3 CCC 100,00 
  

注意：如果您想知道为什么我不乘以订单与价格：他们不相关（所以我应该离开这个专栏，因为它有点模糊，请忽略它，我刚刚添加它，使所有不那么抽象）。实际上， Orders 必须保持不变，这就是子查询方法加入两者的原因，也是我需要首先分组的原因。

b
$ b

结论：显然，我的问题的核心可以通过 OVER 子句，它可以应用于任何聚合函数，如 SUM （请参阅达米安的答案）对我来说是新的东西。谢谢大家的工作方式。

解决方案

哇，其他答案看起来很复杂 - 所以我希望我没有您可以使用 OVER / PARTITION BY 反对集合，然后它们会进行分组/聚合，而不需要 GROUP BY 子句。所以我只是修改你的查询为：

  select T2.ID AS T2ID 
，T2.Name as T2Name 
，T2.Orders 
，T1.ID AS T1ID 
，T1.Name作为T1Name 
，T1Sum.Price 
 FROM @ t2 T2 
 INNER JOIN（
 SELECT Rel.t2ID 
，Rel.t1ID 
  - ，MAX（Rel.t1ID）AS t1ID 
  -  MAX返回一个任意的ID，我需要的是：
，ROW_NUMBER（）OVER（Partition By Rel.t2ID Order By Price DESC）As PriceList 
，SUM（Price）OVER（PARTITION BY Rel.t2ID）作为价格
从@ 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 
其中t1Sum.PriceList = 1 
  

结果集。

I hope following sample code is self-explanatory:

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

Result:

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

What i need is commented above, a way to get the ROW_NUMBER but also to Group By in the first place. So i need the sum of all T1-prices grouped by T2.ID in the relation-table and in the outer query the t1ID with the highest price.

In other words: How to change MAX(Rel.t1ID)AS t1ID to somewhat returning the ID with the highest price?

So the desired result is(notice that first T1ID changed from 2 to 1 since it has the higher price):

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

Note: in case you're wondering why i don't multiply Orders with Price: they are not realated(so i should have left off this column since it's a bit ambiguous, please ignore it, i've just added it to make all less abstract). Actually Orders must remain unchanged, that's the reason for the sub-query approach to join both and the reason why i need to group by in the first place.

Conclusion: obviously the core of my question can be answered by the OVER clause that can be applied to any aggregate function like SUM(see Damien's answer) what was new to me. Thank you all for your working approaches.

解决方案

Wow, the other answers look complex - so I'm hoping I've not missed something obvious.

You can use OVER/PARTITION BY against aggregates, and they'll then do grouping/aggregating without a GROUP BY clause. So I just modified your query to:

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

Which gives the requested result set.

这篇关于如何结合GROUP BY和ROW_NUMBER？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！