问题描述
尊敬的先生，
听到的是我的数据，我想选择每个月的前4条记录
听说是9月有8条记录，只能选择每个月的前4条记录.
Dear sir,
hear is my data and i want to select every month top 4 record
hear is sep month have 8 record only select top 4 record of every month .
FreightRate DwtDate ZoneFrom ZoneTo Dwt
0.00 22 Nov 2011 19 14 1
89.00 22 Nov 2011 19 14 2
70.00 22 Nov 2011 19 14 5
49.00 22 Nov 2011 19 14 10
0.00 28 Sep 2011 19 14 1
87.00 28 Sep 2011 19 14 2
68.00 28 Sep 2011 19 14 5
48.00 28 Sep 2011 19 14 10
0.00 06 Sep 2011 19 14 1
89.00 06 Sep 2011 19 14 2
69.00 06 Sep 2011 19 14 5
49.00 06 Sep 2011 19 14 10
0.00 20 Jun 2011 19 14 1
93.00 20 Jun 2011 19 14 2
70.00 20 Jun 2011 19 14 5
51.00 20 Jun 2011 19 14 10
90.00 10 May 2011 19 14 2
64.00 10 May 2011 19 14 5
48.00 10 May 2011 19 14 10
91.00 10 Apr 2011 19 14 2
在此先感谢.
thanks in advance .
推荐答案
select * from (
SELECT *, DATEPART(MONTH, dwtdate) month , rank() over (partition by DATEPART(MONTH, dwtdate) order by freightrate desc) Ranking
FROM Freight
) marshaled where Ranking <= 4
order by month, ranking
要了解有关排名功能的更多信息:
http://msdn.microsoft.com/en-us/library/ms189798.aspx [ ^ ]
请记住，我以为您的所有数据都在同一年.因此，请考虑对其进行更改并使其适应您的需求.
我也为您的桌子命名为Freight，所以请记住也要更改它.
祝您好运
To read more about ranking functions :
http://msdn.microsoft.com/en-us/library/ms189798.aspx[^]
And remember that I supposed that all of your data is at the same year. So consider to change it and adapt it to your needs.
Also I named your table Freight so remember to change it too.
Good Luck
select TOP 4 * from MonthTable where dwtDate LIKE %+ 'SEP' +%
希望对您有帮助，
thnks
hope it helps,
thnks
这篇关于如何选择一个月中的前4个日期.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！