ROW_Count（）根据订单重新开始

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?

这种香蕉的复发被认为是新的，所以当我们看到香蕉时，我们想要从一开始计算。我一直绞尽脑汁，想不出一个好方法。我明白为什么它不起作用但却想不出让它发挥作用的方法。有关最佳方法的建议吗？

1 个解决方案

#1

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:

有几种不同的方法来解决这个问题。一种方法是row_number（）方法的不同。此方法将识别相同类别的组：

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;

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.

您还可以使用lag（）来计算分组，但这可以在SQL Server 2005和2008以及更新版本中使用。

#1