問(wèn)題描述
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
所以這個(gè)腳本返回這個(gè):
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
這是完全有道理的，除了我希望它返回這個(gè):
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
我希望它在遇到一組新香蕉時(shí)重新開始計(jì)數(shù).顯然，我的數(shù)據(jù)并不是真正的香蕉，但它使可視化變得容易.
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.
香蕉的這種復(fù)發(fā)被認(rèn)為是新的，所以當(dāng)我們看到這一點(diǎn)時(shí)，我們想從一個(gè)開始數(shù).我一直在絞盡腦汁，想不出一個(gè)好的方法來(lái)做到這一點(diǎn).我明白為什么它不工作，但想不出辦法讓它工作.關(guān)于做到這一點(diǎn)的最佳方法有什么建議嗎?
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?
推薦答案
有幾種不同的方法可以解決這個(gè)問(wèn)題.一種方法是 row_number() 方法的不同.此方法將識(shí)別相同的相鄰類別組:
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() 計(jì)算分組，但這將適用于 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.
這篇關(guān)于ROW_Count() 根據(jù)順序重新開始的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！