如何生成具有特定条件的rownumber [英] How to generate rownumber with specific conditions

问题描述

在我的表中我有User，Month，LMonth列。我必须按月生成RowNumber订单，其中月份应大于或等于LMonth。



Ex。

用户月份Lmonth RN

A1 200810 200902 0

A1 200811 200902 0

A1 200812 200902 0

A1 200901 200902 0

A1 200902 200902 1

A1 200903 200902 2

A1 200904 200902 3

A1 200905 200902 4

A1 200906 200902 5

A1 200907 200902 6

B1 200905 200908 0

B1 200906 200908 0

B1 200907 200908 0

B1 200908 200908 1

B1 200909 200908 2

B1 200910 200908 3

B1 200911 200908 4

B1 200912 200908 5



我要求的栏目是RN。

对于用户A1 RN应该从2009/02月开始[LMonth]月份

如果ARR = 0，RN必须从1开始再次获得


我是什么尝试过：



选择用户，月，LMonth，ROW_NUMBER（）结束（按mo排序） n）来自MyTable的RN



我需要条件RN应该从哪里开始月= Lmnoth

In my table Im having User, Month, LMonth columns. I have to generate RowNumber order by month where the month should be greater than or equal to LMonth.

Ex.
User Month Lmonth RN
A1 200810 200902 0
A1 200811 200902 0
A1 200812 200902 0
A1 200901 200902 0
A1 200902 200902 1
A1 200903 200902 2
A1 200904 200902 3
A1 200905 200902 4
A1 200906 200902 5
A1 200907 200902 6
B1 200905 200908 0
B1 200906 200908 0
B1 200907 200908 0
B1 200908 200908 1
B1 200909 200908 2
B1 200910 200908 3
B1 200911 200908 4
B1 200912 200908 5

My required column is RN.
For User A1 the RN should start from 200902 [LMonth] of Month
If ARR=0 RN has to start again from 1

What I have tried:

select User,Month,LMonth,ROW_NUMBER() over(order by month)RN from MyTable

I need the condition RN should start where Month=Lmnoth

推荐答案

你可以只对需要它的行执行行编号，然后与其余行进行联合。例如。

You could perform the row numbering on just the rows that need it then union with the rest. E.g.

select [User], [Month], Lmonth,
	ROW_NUMBER() OVER (PARTITION BY [User] ORDER BY Lmonth) RN
FROM MyTable WHERE [Month] >= lMonth
UNION ALL
select [User], [Month], Lmonth, 0 
FROM MyTable WHERE [Month] < lMonth
ORDER BY 1,4

这篇关于如何生成具有特定条件的rownumber的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！