sql server 按外键分组查询

SELECT ROW_NUMBER() over(partition by a.yngrbsh order by a.SFRQ desc ) RN,
 c.* FROM JB_NCZ_GW_SFJL A,JB_MB_ZHXX_DJ C  WHERE A.MBDJLSH=C.LSH AND  a.SFJY='2' and a.LSH 
not in(select SFLSH from JB_NCZ_GW_ZZD b)

在结果中根据yngrbsh进行分组，然后可以取出RN等于1的数据。