如何生成具有特定条件的rownumber

问题描述

在我的表中我有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.
UserMonthLmonth RN
A1200810200902 0
A1200811200902 0
A1200812200902 0
A1200901200902 0
A1200902200902 1
A1200903200902 2
A1200904200902 3
A1200905200902 4
A1200906200902 5
A1200907200902 6
B1200905200908 0
B1200906200908 0
B1200907200908 0
B1200908200908 1
B1200909200908 2
B1200910200908 3
B1200911200908 4
B1200912200908 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

推荐答案

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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！