代码之家 › 专栏 › 技术社区 › BrianMichaels

根据字段分组的最大数量和总和选择行

group-by sql-server

BrianMichaels · 技术社区 · 7 年前

我试图合计数量行,并按零件号和箱子将它们分组,然后选择有最大数量的箱子开始。在下面的查询中,它只选择bin 1-B。我的结果集应该是第1-2345部分:箱子1-A,箱子总数=150,箱子总数=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

2 回复 | 直到 7 年前

Dave C 7 年前

试一试:

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

Cetin Basoz 7 年前

因为你没有给我们样本输出,所以不清楚你想做什么。从你的最后一句话:

With cte as
( SELECT bin, partnumber, 
      sum(qty) over (Partition By  partnumber) as sumQty,
      sum(qty) over (Partition By  partnumber Order by bin) as totQty,
     ROW_NUMBER() OVER ( Partition By  partnumber ORDER BY bin) as rn 
from inventory
) 
SELECT * FROM cte where rn = 1;

DBFiddle Demo.