select [CriterionID],[MissionIsFinish] ,[MissionMonth] from
(
select [CriterionID],[MissionIsFinish],[MissionMonth],
row_number() over(partition by [CriterionID] order by [MissionMonth] desc) as rowNum
from [fts].[v_TTM_DOAM_MissionList]
where [IsDel]=0 and [MissionYear]=2013 and [MissionMonth]<=12 and [MissionMonth]>0
group by [CriterionID],[MissionIsFinish],[MissionMonth]
) as tb
where rowNum=1
[fts].[v_TTM_DOAM_MissionList]列出了过往各工作内容(CriterionID)每个已录入月份(MissionMonth)的进展状态(MissionIsFinish),其中可能有的进展已经在过去的时间已经完成,后续不再录入,或者有的进展录入中间隔了n个月没录。
上面的代码是查询各进展最新月份(top 1)的记录, 如果要取top n,把最后的where 条件改为rowNum<=n即可
partition by 是用来分组的,可以理解为group by
该语句要实现的功能是:
查询工作内容(CriterionID)在当前月份(MissionMonth)的工作进展状态(MissionIsFinish)
问题在于:
如果我们要取工作内容某月份的进展状态,但是