时间戳

首页 > 解决方案 > 根据字段分组的 MAX 数量和 SUM 数量选择 ROW

sql-server - 根据字段分组的 MAX 数量和 SUM 数量选择 ROW

问题描述

我正在尝试对 qty 行求和并按零件号和 bin 对它们进行分组，然后选择开始时具有最大数量的 bin。在下面的查询中，它只选择 bin 1-B。我的结果集应该是 Part 1-2345: Bin 1-A, SUM qty of bins = 150 , total in that bin = 100

CREATE TABLE inventory (
ID int IDENTITY(1,1) PRIMARY KEY,
bin nvarchar(25),
partnumber nvarchar(25),
qty int
);

INSERT INTO inventory ( bin, partnumber, qty)
VALUES ('1-A', '1-2345', '100'), ('1-A', '1-2347', '10'), ('1-A', '1-2348', 
'15'), ('1-B', '1-2345', '50'), ('1-B', '1-2347', '50'), ('1-B', '1-2348', 
'55')

;With cte as
    ( SELECT bin, partnumber, sum(qty) qty
    , ROW_NUMBER() OVER( Partition By  partnumber ORDER BY bin desc) as rn 
from inventory
     GROUP BY bin, partnumber) 
SELECT * FROM cte where rn = 1

结果集应该是
输出：

bin partnumber  sum_of_bins max_qty_in_bin  
1-A 1-2345      150         100             
1-B 1-2347      60          50              
1-B 1-2348      70          55

标签： sql-servergroup-by

解决方案

试一试：

DECLARE @inventory TABLE (ID int IDENTITY(1,1), bin nvarchar(25), partnumber nvarchar(25), qty int);

INSERT INTO @inventory ( bin, partnumber, qty)
VALUES ('1-A', '1-2345', '100'), ('1-A', '1-2347', '10'), ('1-A', '1-2348','15'), ('1-B', '1-2345', '50'), ('1-B', '1-2347', '50'), ('1-B', '1-2348', '55')

;WITH CTE AS
    ( 
        SELECT bin, partnumber
                , sum(qty) OVER(Partition By partnumber) AS sum_of_bins
                , max(qty) OVER(Partition By partnumber) AS max_qty_in_bin
                , ROW_NUMBER() OVER(Partition By partnumber ORDER BY qty desc) as rn 
        FROM @inventory
        GROUP BY bin, partnumber, qty) 

SELECT * 
FROM cte
WHERE rn=1

输出：

bin partnumber  sum_of_bins max_qty_in_bin  rn
1-A 1-2345      150         100             1
1-B 1-2347      60          50              1
1-B 1-2348      70          55              1

推荐阅读