問題描述
Create Table #Test (
ID Int Primary Key Identity,
Category VarChar(100)
)
Insert into #Test
(Category)
Values
('Banana'),
('Banana'),
('Banana'),
('Banana'),
('Banana'),
('Banana'),
('Strawberry'),
('Strawberry'),
('Strawberry'),
('Banana'),
('Banana')
Select
*
,ROW_NUMBER() Over (Partition by Category order by ID) as RowNum
From #Test
Order by ID
所以這個腳本返回這個:
So this script returns this:
ID Category RowNum
1 Banana 1
2 Banana 2
3 Banana 3
4 Banana 4
5 Banana 5
6 Banana 6
7 Strawberry 1
8 Strawberry 2
9 Strawberry 3
10 Banana 7
11 Banana 8
這是完全有道理的，除了我希望它返回這個:
Which makes perfect sense, except I want it to return this:
ID Category RowNum
1 Banana 1
2 Banana 2
3 Banana 3
4 Banana 4
5 Banana 5
6 Banana 6
7 Strawberry 1
8 Strawberry 2
9 Strawberry 3
10 Banana 1
11 Banana 2
我希望它在遇到一組新香蕉時重新開始計數.顯然，我的數據并不是真正的香蕉，但它使可視化變得容易.
I want it to restart the count when it hits a new set of Banana. Obviously my data is not really bananas, but it makes it easy to visualize.
香蕉的這種復發被認為是新的，所以當我們看到這一點時，我們想從一個開始數.我一直在絞盡腦汁，想不出一個好的方法來做到這一點.我明白為什么它不工作，但想不出辦法讓它工作.關于做到這一點的最佳方法有什么建議嗎?
This recurrence of bananas is considered to be new, so we want to start counting from one when we see this. I've been racking my brain and can't think of a good way to do this. I understand why it is not working but can't think of a way to make it work. Any advice on the best way to do this?
推薦答案
有幾種不同的方法可以解決這個問題.一種方法是 row_number() 方法的不同.此方法將識別相同的相鄰類別組:
There are several different ways to approach this. One method is the difference of row_number() approach. This method will identify groups of adjacent categories that are the same:
Select t.*,
row_number() over (partition by grp, category order by id) as rownum
From (select t.*,
(row_number() over (order by id) -
row_number() over (partition by category order by id)
) as grp
from #Test t
) t
Order by ID;
您也可以使用 lag() 計算分組，但這將適用于 SQL Server 2005 和 2008 以及更新的版本.
You can also figure out the groupings using lag(), but this will work in SQL Server 2005 and 2008 as well as more recent versions.
這篇關于ROW_Count() 根據順序重新開始的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！