sqlserver分组取前n条记录
sqlserver分组取前n条记录
1.前言。这个有两种方法。1是用in,2是用row_number()，可根据情况来用。两种都可以。
2.举例。
方法1:
Java代码
select a.* from table1 a where a.column1 in (select max(column1) from table1 group by a.column2,column3)
方法2:
Java代码
with nyr as (
select NYR,QYZX_DM,FXSP_XH,SPBM_DLJ,WBSP_GG,JGLX,JZJG,MAX(SJGX_SJ)，
ROW_NUMBER() over (partition by nyr order by nyr) as 'rowno'
from WD_O_T_IC_SPSJ_FX
where rowno=1
group by NYR,QYZX_DM,FXSP_XH,SPBM_DLJ,WBSP_GG,JGLX,JZJG
order by NYR,QYZX_DM,FXSP_XH,SPBM_DLJ,WBSP_GG,JGLX,MAX(SJGX_SJ) desc )
select *
from nyr
where rowno = 1