如何到达具有多种条件的rownumber [英] How to arrive rownumber with multiple conditions
问题描述
我必须到达rownumber以获得以下条件
用户月份月份ARR RN
A1 200810 200902 1 0
A1 200811 200902 1 0
A1 200812 200902 2 0
A1 200901 200902 3 0
A1 200902 200902 3 1
A1 200903 200902 4 2
A1 200904 200902 4 3
A1 200905 200902 0 0
A1 200906 200902 1 < span class =code-digit> 1
A1 200907 200902 < span class =code-digit> 2 2
B1 200905 < span class =code-digit> 200908 0 0
B1 < span class =code-digit> 200906 200908 1 0
B1 2 00907 200908 2 0
B1 200908 200908 2 1
B1 200909 200908 2 2
B1 200910 200908 0 0
B1 200911 200908 1 1
B1 200912 200908 1 2
在我的上表我必须到达RN列。条件是
1.RN应该在Month> = LMonth
时开始2.RN应该是0如果ARR = 0并且当ARR> 1时RN从1重新开始。 0
我尝试过:
选择用户,月, Lmonth,ARR,ROW_NUMBER()OVER(按用户订购按月划分)RN FROM MyTable WHERE Month> = LMonth
UNION ALL
选择User,Month,Lmonth, ARR,0来自MyTable WHERE月< LMonth
订购1,2
Iam在第二个条件下挣扎。
您已经在如何生成具有特定条件的rownumber [ ^ ]。
如果您需要进一步的帮助,请回答该问题的解决方案,而不是重新发布。
首先,请阅读我对这个问题的评论...
在我看来,实现这一目标的方法很少。
其中一个是使用 CTE [ ^ ]。
另一个是使用ROW_NUMBER()
功能:
DECLARE @ tmp TABLE ([用户] VARCHAR ( 10 ),[月] INT ,Lmonth INT ,ARR INT )
INSERT INTO @ tmp ([用户],[月],[Lmonth],ARR)
VALUES (' A1', 200810 , 200902 , 1 ),
(' A1', 200811 , 200902 , 1 ),
(' A1', 200812 , 200902 , 2 ),
(' A1', 200901 , 200902 , 3 ),
(' < span class =code-string> A1', 200902 , 200902 , 3 ),
(' A1', 200903 , 200902 , 4 ) ,
(' A1', 200904 , 200902 , 4 ),
(' A1', 200905 , 200902 , 0 ),
(' A1', 200906 , 200902 , 1 ),
(' A1', 200907 , 200902 , 2 ),
(' B1', 200905 , 200908 , 0 ),
( ' B1', 200906 , 200908 , 1 ),
(' B1', 200907 , 200908 , 2 ),
(' B1',< span class =code-digit> 200908 , 200908 , 2 ),
(' B1', 200909 , 200908 , 2 ),
(' B1', 200910 , 200908 , 0 ),
(' B1', 200911 , 200908 , 1 ),
(' B1',< span class =code-digit> 200912 , 200908 , 1 )
SELECT [用户],[月],[Lmonth],ARR, 0 AS RN
FROM @ tmp
WHERE [月]< [Lmonth] OR ARR = 0
UNION ALL
SELECT [用户],[月],[Lmonth ],ARR,ROW_NUMBER() OVER ( PARTITION BY [用户] ORDER BY [Month]) AS RN
FROM @ tmp
WHERE [月]> = [Lmonth] AND ARR> 0
结果:
用户月份月份ARR RN
A1 200810 200902 1 0
A1 200811 200902 1 0
A1 200812 200902 2 0
A1 200901 200902 3 0
A1 200905 200902 0 0
B1 200905 200908 0 0
B1 200906 200908 1 0
B1 200907 200908 2 0
B1 200910 200908 0 0
A1 200902 200902 3 1
A1 200903 200902 4 2
A1 200904 200902 4 3
A1 200906 200902 1 4
A1 200907 200902 2 5
B1 200908 200908 2 1
B1 200909 200908 2 2
B1 200911 200908 1 3
B1 200912 200908 1 4
试试!
I have to arrive rownumber for following conditions
User Month Lmonth ARR RN
A1 200810 200902 1 0
A1 200811 200902 1 0
A1 200812 200902 2 0
A1 200901 200902 3 0
A1 200902 200902 3 1
A1 200903 200902 4 2
A1 200904 200902 4 3
A1 200905 200902 0 0
A1 200906 200902 1 1
A1 200907 200902 2 2
B1 200905 200908 0 0
B1 200906 200908 1 0
B1 200907 200908 2 0
B1 200908 200908 2 1
B1 200909 200908 2 2
B1 200910 200908 0 0
B1 200911 200908 1 1
B1 200912 200908 1 2
In my above table I have to arrive RN column. The conditions are
1. RN should starts when Month >= LMonth
2. RN should be 0 If ARR = 0 and RN restarts from 1 when ARR > 0
What I have tried:
select User,Month,Lmonth,ARR,ROW_NUMBER() OVER (PARTITION BY User ORDER BY Month)RN FROM MyTable WHERE Month >= LMonth
UNION ALL
select User,Month,Lmonth,ARR,0 FROM MyTable WHERE Month < LMonth
order by 1,2
Iam struggling with second condition.
You have already been given an answer in your original of this question at How to generate rownumber with specific conditions[^].
If you need further help then reply to the solution in that question, rather than reposting.
First of all, please read my comment to the question...
In my opinion, there's few ways to achieve that.
One of them is to create custom row numbering using CTE[^].
Another one is to useROW_NUMBER()
function:
DECLARE @tmp TABLE ([User] VARCHAR(10), [Month] INT, Lmonth INT, ARR INT) INSERT INTO @tmp ([User], [Month], [Lmonth], ARR) VALUES('A1',200810, 200902, 1), ('A1',200811, 200902, 1), ('A1',200812, 200902, 2), ('A1',200901, 200902, 3), ('A1',200902, 200902, 3), ('A1',200903, 200902, 4), ('A1',200904, 200902, 4), ('A1',200905, 200902, 0), ('A1',200906, 200902, 1), ('A1',200907, 200902, 2), ('B1',200905, 200908, 0), ('B1',200906, 200908, 1), ('B1',200907, 200908, 2), ('B1',200908, 200908, 2), ('B1',200909, 200908, 2), ('B1',200910, 200908, 0), ('B1',200911, 200908, 1), ('B1',200912, 200908, 1) SELECT [User], [Month], [Lmonth], ARR, 0 AS RN FROM @tmp WHERE [Month]<[Lmonth] OR ARR = 0 UNION ALL SELECT [User], [Month], [Lmonth], ARR, ROW_NUMBER() OVER(PARTITION BY [User] ORDER BY [Month]) AS RN FROM @tmp WHERE [Month]>=[Lmonth] AND ARR > 0
Result:
User Month Lmonth ARR RN A1 200810 200902 1 0 A1 200811 200902 1 0 A1 200812 200902 2 0 A1 200901 200902 3 0 A1 200905 200902 0 0 B1 200905 200908 0 0 B1 200906 200908 1 0 B1 200907 200908 2 0 B1 200910 200908 0 0 A1 200902 200902 3 1 A1 200903 200902 4 2 A1 200904 200902 4 3 A1 200906 200902 1 4 A1 200907 200902 2 5 B1 200908 200908 2 1 B1 200909 200908 2 2 B1 200911 200908 1 3 B1 200912 200908 1 4
Try!
这篇关于如何到达具有多种条件的rownumber的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!