我希望有一个T-SQL程序或SQL查询来执行以下操作：

Question

我有这行的表：

City  | Day 
NEWYORK | MON 
NEWYORK | MON 
NEWYORK | MON 
NEWYORK | TUE 
CHICAGO | MON 
CHICAGO | MON 

我需要编写一个查询添加列Occurrence使用从数据结果列City和Day：

City  | Day | Occurrence 
NEWYORK | MON | 1 
NEWYORK | MON | 2 
NEWYORK | MON | 3 
NEWYORK | TUE | 1 
CHICAGO | MON | 1 
CHICAGO | MON | 2 

EG：对于NEWYORK如果发生第一MON然后Occurrence = 1，如果发生MON两次，然后对应Occurrence = 2等等。

Answer 1

的row_number窗函数应该做的正是你要寻找的：

SELECT [city], [day], ROW_NUMBER() OVER (PARTITION BY [city], [day] ORDER BY 1) AS [occurence] 
FROM my_table;