如何按组对行重新编号 [英] How to renumber rows by groups

问题描述

我需要对列SeqNo中的组(Class, Color, Type)序列从以下行重新编号:

I need to renumber rows by group (Class, Color, Type) sequences in column SeqNo from:

Class  | Color | Type | SeqNo
Animal | Brown | Terr | 1
Animal | Brown | Aqua | 3
Animal | White | Terr | 3
Plant  | Green | Aqua | 2
Plant  | Green | Aqua | 2
Plant  | Green | Aqua | 2
Platn  | Green | Terr | 9

收件人:

Class  | Color | Type | SeqNo
Animal | Brown | Terr | 1
Animal | Brown | Aqua | 2
Animal | White | Terr | 1
Plant  | Green | Aqua | 1
Plant  | Green | Aqua | 1
Plant  | Green | Aqua | 1
Plant  | Green | Terr | 2

请问该怎么做?

推荐答案

您需要使用DENSE_RANK()函数对中间结果集的序列和CTE进行编号.

You need to use DENSE_RANK() function to number your sequence and the CTE for the intermediary result set.

INSERT INTO @t VALUES
('Animal', 'Brown', 'Terr', 1),
('Animal', 'Brown', 'Aqua', 3),
('Animal', 'White', 'Terr', 3),
('Plant', 'Green', 'Aqua', 2),
('Plant', 'Green', 'Aqua', 2),
('Plant', 'Green', 'Aqua', 2),
('Plant', 'Green', 'Terr', 9)

;WITH cte AS(
SELECT *, DENSE_RANK() OVER (PARTITION BY Class, Color ORDER BY Class, Color, Type) AS NewSeq 
FROM @t 
) 
UPDATE t1
SET t1.SeqNo = t2.NewSeq
FROM @t AS t1
INNER JOIN cte AS t2 ON t1.Class = t2.Class AND t1.Color = t2.Color AND t1.Type = t2.Type

SELECT * FROM @t

输出:

Class   Color   Type    SeqNo
Animal  Brown   Terr    2
Animal  Brown   Aqua    1
Animal  White   Terr    1
Plant   Green   Aqua    1
Plant   Green   Aqua    1
Plant   Green   Aqua    1
Plant   Green   Terr    2

这篇关于如何按组对行重新编号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！