转载：SQL篇---分组排序

1、分组，每组取前三行数据

SELECT C_BH,C_BT,C_BirthCorpID
FROM 
( 
   SELECT rid=ROW_NUMBER() 
                  OVER(PARTITION BY C_BirthCorpID 
                          ORDER BY D_FBSJ DESC), 
          * 
   FROM T_GY_WZ
) AS T 
WHERE rid<=3

2、分组，取排序最大的行数据

--方法1：select a.* from tb a where val = (select min(val) from tb where name = a.name) order by a.name 
--方法2： 
select a.* from tb a where not exists(select 1 from tb where name = a.name and val < a.val) 
--方法3： 
select a.* from tb a,(select name,min(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name 
--方法4： 
select a.* from tb a inner join (select name , min(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name 
--方法5 
select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val < a.val) order by a.name 
/*