求一条查询语句，按月分划分
本帖最后由 lzxue1989 于 2013-03-26 18:08:54 编辑 有一表，表结构和数据如下
编号姓名入职日期 离职日期 工号
0001AA1 2010-10-252013-1-310001
0002AA22011-9-202013-1-310002
0003AA32013-1-42013-1-410003
0004AA42013-1-42013-1-510004
0005AA52013-1-72013-1-710005
0006AA62012-6-262013-1-1210006
0007AA72012-1-312013-1-1610007
0008AA82012-2-202013-1-1610008
0009AA92012-10-232013-1-1710009
0010AA102012-7-52013-1-1810010
0011AA112012-6-82013-1-1810011
0012AA122013-1-182013-1-1810012
0013AA132011-9-72013-1-1810013
0014AA142011-4-202013-1-2310014
0015AA152011-11-162013-1-2310015
0016AA162013-1-282013-1-2810016
0017AA172012-10-242013-2-510017
0018AA182012-9-182013-2-510018
0019AA192012-10-232013-2-510019
0020AA202012-12-182013-2-510020
0021AA212011-5-52013-2-510021
0022AA222011-6-82013-2-510022
0023AA232011-9-142013-2-510023
0024AA242011-2-142013-2-510024
0025AA252010-5-102013-2-510025
0026AA262005-9-52013-2-610026
0027AA272012-6-42013-2-610027
0028AA282012-10-252013-2-610028
0029AA292005-9-52013-2-810029
0030AA302013-2-152013-2-1610030
0031AA312013-2-152013-2-1610031
0032AA322010-8-172013-2-1810032
0033AA332013-2-182013-2-1910033
0034AA342013-2-192013-2-1910034
0035AA352013-3-12013-2-2110035
0036AA362013-2-212013-2-2110036
0037AA372013-2-212013-2-2110037
0038AA382013-2-212013-2-2110038
0039AA392013-2-202013-2-2210039
0040AA402013-2-202013-2-2210040
0041AA412013-2-182013-2-2210041
0042AA422013-2-182013-2-2210042
0043AA432013-2-182013-2-2210043
0044AA442013-2-182013-2-2210044
0045AA452013-2-182013-2-2210045
0046AA462013-2-222013-2-2210046
0047AA472013-2-182013-2-2210047
0048AA482013-2-182013-2-2210048
0049AA492013-2-182013-2-2210049
0050AA502013-2-182013-2-2210050
0051AA512013-2-182013-2-2210051
0052AA522013-2-182013-2-2210052
0053AA532013-2-182013-2-2210053
0054AA542013-2-182013-2-2210054
0055AA552013-2-182013-2-2210055
0056AA562013-2-182013-2-2210056
0057AA572013-2-182013-2-2210057
0058AA582013-2-252013-2-2410058
0059AA592013-2-212013-2-2410059
0060AA602013-2-212013-2-2410060
0061AA612013-2-212013-2-2410061
0062AA622013-2-182013-2-2510062
0063AA632013-2-192013-2-2510063
0064AA642013-2-262013-2-2510064
0065AA652013-2-262013-2-2510065
0066AA662013-2-262013-2-2510066
0067AA672013-2-262013-2-2510067
0068AA682013-2-182013-2-2510068
0069AA692013-2-182013-2-2510069
0070AA702013-2-182013-2-2510070
0071AA712013-2-182013-2-2510071
0072AA722013-2-182013-2-2510072
0073AA732013-2-182013-2-2510073
0074AA742013-2-182013-2-2510074
0075AA752013-2-182013-2-2510075
0076AA762013-2-182013-2-2510076
0077AA772013-2-182013-2-2510077
0078AA782013-2-182013-2-2510078
0079AA792013-2-182013-2-2510079
0080AA802013-2-182013-2-2510080
0081AA812013-2-182013-2-2510081
0082AA822013-2-182013-2-2510082
0083AA832013-2-182013-2-2510083
0084AA842013-2-182013-2-2510084
0085AA852013-2-182013-2-2510085
0086AA862013-2-232013-2-2510086
0087AA872004-3-12013-2-2610087
0088AA882005-6-242013-2-2610088
0089AA892006-11-82013-2-2610089
0090AA902013-2-262013-2-2610090
0091AA912013-2-272013-2-2610091
0092AA922013-2-272013-2-2610092
0093AA932013-2-272013-2-2610093
0094AA942012-8-312013-2-2710094
0095AA952013-2-282013-2-2810095
0096AA962013-3-12013-2-2810096
0097AA972013-3-12013-3-210097
0098AA982013-3-12013-3-210098
0099AA992013-3-12013-3-210099
0100AA1002012-8-152013-3-210100
0101AA1012013-3-42013-3-410101
0102AA1022013-3-52013-3-410102
0103AA1032013-3-42013-3-410103
0104AA1042013-3-12013-3-410104
0105AA1052013-3-12013-3-410105
0106AA1062013-3-12013-3-410106
0107AA1072013-3-52013-3-510107
0108AA1082013-3-52013-3-510108
0109AA1092013-3-52013-3-510109
0110AA1102013-3-52013-3-510110
0111AA1112013-3-52013-3-510111
0112AA1122013-3-52013-3-510112
0113AA1132013-3-52013-3-510113
0114AA1142013-3-12013-3-610114
0115AA1152013-3-12013-3-610115
0116AA1162013-3-12013-3-610116
0117AA1172013-3-72013-3-610117
0118AA1182013-3-72013-3-610118
0119AA1192011-3-212013-3-610119
0120AA1202012-10-112013-3-610120
0121AA1212012-2-62013-3-610121
0122AA1222011-8-222013-3-610122
0123AA1232013-3-72013-3-710123
0124AA1242013-3-72013-3-710124
0125AA1252013-3-12013-3-710125
0126AA1262013-3-82013-3-710126
0127AA1272013-3-82013-3-710127
0128AA1282013-3-82013-3-710128
0129AA1292013-3-82013-3-710129
0130AA1302013-3-82013-3-710130
0131AA1312013-3-82013-3-710131
0132AA1322013-3-112013-3-810132
0133AA1332013-3-112013-3-810133
0134AA1342013-3-112013-3-810134
0135AA1352011-2-232013-3-810135
0136AA1362011-2-232013-3-810136
0137AA1372011-3-42013-3-810137
0138AA1382012-10-242013-3-810138
0139AA1392012-11-22013-3-810139
0140AA1402012-9-172013-3-1110140
0141AA1412008-9-42013-3-1110141
0142AA1422006-10-192013-3-1110142
0143AA1432013-3-122013-3-1210143
0144AA1442013-3-122013-3-1210144
0145AA1452013-3-142013-3-1310145
0146AA1462013-3-142013-3-1310146
0147AA1472012-11-142013-3-1310147
0148AA1482010-7-12013-3-1410148
0149AA1492011-9-282013-3-1410149
0150AA1502013-3-142013-3-1410150
0151AA1512012-10-242013-3-1510151
0152AA1522011-9-192013-3-1810152
0153AA1532004-3-162013-3-1810153
0154AA1542013-3-182013-3-1810154
0155AA1552013-3-182013-3-1810155
0156AA1562013-3-192013-3-1810156
0157AA1572013-3-192013-3-1810157
0158AA1582013-3-192013-3-1810158
0159AA1592013-3-192013-3-1810159
0160AA1602007-7-312013-3-1910160
0161AA1612011-10-62013-3-1910161
0162AA1622013-3-52013-3-2010162
0163AA1632013-3-182013-3-2510163
0164AA1642012-12-62013-3-2510164
0165AA1652012-10-222013-3-2610165
0166AA1662012-10-222013-3-2610166
0167AA1672012-2-132013-3-2610167
0168AA1682012-2-132013-3-2610168
0169AA1692013-3-272013-3-2610169
0001AA1 2010-10-252013-1-310001
0002AA22011-9-202013-1-310002
0003AA32013-1-42013-1-410003
0004AA42013-1-42013-1-510004
0017AA172012-10-242013-2-510017
0018AA182012-9-182013-2-510018
0019AA192012-10-232013-2-510019
0020AA202012-12-182013-2-510020
0097AA972013-3-12013-3-210097
0098AA982013-3-12013-3-210098
0099AA992013-3-12013-3-210099
0100AA1002012-8-152013-3-210100
select a.* from tb a,
(
select 编号,row_number() over (partition by convert(nvarchar(7),离职日期,111) order by 离职日期 asc) num
from tb
) b
where a.编号 = b.编号 and b.num <=4
select a.* from tb a,
(
select 编号,row_number() over (partition by convert(nvarchar(7),离职日期,111) order by 离职日期 asc) num
from tb
) b
where a.编号 = b.编号 and b.num <=4
