代码之家 › 专栏 › 技术社区 › Ivan-Mark Debono

分组记录时如何使用行号?

sql-server-2016 sql-server sql

1

Ivan-Mark Debono · 技术社区 · 7 年前

DECLARE @items TABLE
(
    ItemId int NOT NULL,
    [Description] varchar(255) NOT NULL,
    Amount money NOT NULL
);

INSERT INTO @items SELECT 1, 'A', 10;
INSERT INTO @items SELECT 2, 'A', 10;
INSERT INTO @items SELECT 3, 'B', 11;
INSERT INTO @items SELECT 4, 'B', 11;
INSERT INTO @items SELECT 5, 'B', 11;
INSERT INTO @items SELECT 6, 'C', 12;
INSERT INTO @items SELECT 7, 'C', 12;
INSERT INTO @items SELECT 8, 'A', 10;
INSERT INTO @items SELECT 9, 'A', 10;

SELECT 
    ROW_NUMBER() OVER(PARTITION BY b.ItemId ORDER BY b.[Description]),
    [Description],  
    COUNT(ItemId) OVER(PARTITION BY b.ItemId),
    SUM(Amount) OVER(PARTITION BY b.ItemId)
FROM @items b

结果应该是:

1, A, 4, 40
2, B, 3, 33
3, C, 2, 24

但是,没有对项目进行分组。

那我该怎么用呢 ROW_NUMBER

2 回复 | 直到 7 年前

1

2

Gordon Linoff 7 年前

这就是你想要的吗?

SELECT ROW_NUMBER() OVER (ORDER BY i.Description),
       i.Description,  
       COUNT(*),
       SUM(i.Amount) 
FROM @items i
GROUP BY Description
ORDER BY Description;

rextester .

2

0

Yuriy Tsarkov 7 年前

如果你不想用 GROUP BY 您可以单独使用两个 row_number()

select ROW_NUMBER() over(order by t.[Description]), t.Description, t.cnt, t.summ
from (
SELECT 
    ROW_NUMBER() OVER(PARTITION BY b.[Description] ORDER BY b.[Description] )  rn,
    [Description],  
    COUNT(ItemId) OVER(PARTITION BY b.[Description]) cnt,
    SUM(Amount) OVER(PARTITION BY b.[Description]) summ
FROM @items b
) t where rn = 1

不管怎样,你不应该按 ItemId -实现你的目标是错误的