我创建了以下查询,以显示一年的“最后”值的总和,通常这是一个12月的值,但是该年可能会在任何月份结束,因此我想将每个目标的最后一个值加在一起。我让它工作99%,但是[year]值中有一些随机重复项。
WITH endBalances AS (
SELECT ROW_NUMBER() OVER (PARTITION By GoalMonteCarloHeaderID, Year(Convert(date,MonthDate)) Order By Max(Month(Convert(date,MonthDate))) desc) n, Max(Month(Convert(date,MonthDate))) maxMonth, GrowthBucket, WithdrawalBucket, NoTaxesBucket,
Year(MonthDate) [year]
From GoalMonteCarloMedianResults mcmr
full join GoalMonteCarloHeader mch on mch.ID = mcmr.GoalMonteCarloHeaderID
full join GoalChartData gcd on gcd.ID = mch.GoalChartDataID and gcd.TypeID = 2
inner join Goal g on g.iGoalID = gcd.GoalID
where g.iTypeID in (1) and g.iHHID = 850802
group by GoalMonteCarloHeaderID, MonthDate, GrowthBucket, WithdrawalBucket, NoTaxesBucket
)
SELECT [year], Sum(GrowthBucket) GrowthBucket, Sum(WithdrawalBucket) WithdrawalBucket,Sum(NoTaxesBucket) NoTaxesBucket, maxMonth
From endBalances
where [year] is not null and n=1
Group By [year], maxMonth
order by [year] asc
在数据库结果中显示两个随机重复项;
您可以在图像中看到两个示例,其中年份重复且显示的时间不仅仅是一年中的最后一个月。我的查询中的group by或PARTITION BY()是否做错了?我对T-SQL的此功能不是最熟悉。
T-SQL对此具有很好的功能,在MySQL中没有直接等效的功能。
ROW_NUMBER() OVER (PARTITION BY [year] ORDER BY MonthDate DESC) AS rn
那么rn = 1的任何内容将是一年中的最后一个条目。
这个问题的答案有一些想法:
MySQL中的ROW_NUMBER()
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句