按多列聚合功能-Java 学习之路

我有一个包含以下列的表：ClientID，OrderID，ProductID，每个ProductID只出现一次的ProductID，每个OrderID只对应一个ClientID .

样本数据：

ClientID  OrderID  ProductID  Quantity
1         100      25         10
1         100      30         20
1         101      27         10
1         101      30         10
1         102      27         30
2         103      27         10
2         104      15         10

我需要执行以下操作：按ClientID和ProductID分组时，我需要为其他列应用聚合函数，以便结果集包含每个ClientID的最低OrderID和每个ClientID的每个ProductID的Quantity列的总和 . 结果集将具有等于每个ClientID的不同ProductID数量的行数，并且每行将具有OrderID =每个ClientID的最低OrderID，而不管ProductID如何 .

期望的结果：

ClientID  OrderID  ProductID  Quantity
1         100      25         10
1         100      30         30
1         100      27         40
2         103      27         10
2         103      15         10

我尝试使用以下查询实现此目的：

select ClientID, min(OrderID) as OrderID, ProductID, sum(Quantity) as Quantity 
from table
group by ClientID, ProductID

但由于分组，结果集包含每个ClientID的多个OrderID：

目前的结果：

ClientID  OrderID  ProductID  Quantity
1         100      25         10
1         100      30         30
1         101      27         40
2         103      27         10
2         104      15         10

注意第三行和第四行的OrderID在两个结果之间是如何不同的 .

2 回答

select 
    ClientID, MinOrderID, ProductID, SUM(quantity)
from
(
    select ClientID, min(OrderID) over (partition by ClientID) as MinOrderID, ProductID, Quantity 
    from yourtable
) v
group by ClientID, MinOrderID, ProductID

或者

select distinct 
    ClientID, 
    min(OrderID) over (partition by ClientID),
    ProductID,
    sum(Quantity) over (partition by ClientID, ProductID)       
from yourtable

回复于 2024-04-29T09:02:43+08:00

create table dbo.aatest
(
    ClientID int not null,
    OrderID int not Null,
    ProductID int not null,
    Quantity int not null
);

insert into dbo.aatest values (1,100,25,10)
insert into dbo.aatest values (1,100,30,20)
insert into dbo.aatest values (1,101,27,10)
insert into dbo.aatest values (1,101,30,10)
insert into dbo.aatest values (1,102,27,30)
insert into dbo.aatest values (2,103,27,10)
insert into dbo.aatest values (2,104,15,10)

select * from dbo.aatest;

select
    T.ClientID,
    M.OrderID,
    ProductID,
    TotalQuantity = SUM(Quantity)
from
    (select 
        ClientID,
        OrderID = MIN(OrderID)
    from
        dbo.aatest
    group by
        ClientID) as M
    join dbo.aatest as T
        on M.ClientID = T.ClientID
group by
    T.ClientID,
    M.OrderID,
    ProductID
order by
    ClientID,
    ProductID

回复于 2024-04-29T09:02:43+08:00

按多列聚合功能

2 回答

相关问题