对表按字段分组,并且取每组前3条记录
select a.specObjID,a.subclass from SpecObjAll a where a.specObjID in(select top 3 b.specObjID from SpecObjAll b where b.subclass=a.subclass) order by a.subclass
结果如下:
specObjID subclass
299489951853078528 null
299489952322840576 null
299489676975171584 null
299493250387961856 A0
299493525735630848 A0
299511942555396096 A0
299515515968186368 A0p
299515790376331264 A0p
305178279973775360 A0p
299517714521679872 AGN
299518539625162752 AGN
299528159882143744 AGN
299547951091443712 AGN BROADLINE
299548776194926592 AGN BROADLINE
299555097917024256 AGN BROADLINE
318750652782110720 B6
317534038116034560 B6
318683851159267328 B6
299579012294928384 B9
299581761073997824 B9
299581761543759872 B9
299490776956561408 BROADLINE
299490226730985472 BROADLINE
299490227200747520 BROADLINE
302916585008424960 Carbon
300749442523359232 Carbon
301840433355450368 Carbon