注:sqlserver下
create table test
(
areaid int,
score int
)
insert into test select 0,10
union all select 0,20
union all select 0,30
union all select 0,40
union all select 0,50
union all select 1,10
union all select 1,20
union all select 1,30
union all select 1,40
union all select 1,50
union all select 2,10
union all select 2,20
union all select 2,30
union all select 2,40
union all select 2,50
go
select * from test
--第一种方法适用于sql2000和，其代码如下：
select * from test a
where checksum(*) in (select top 3 checksum(*) from test b where a.areaid=b.areaid order by score desc)
--第二种方法是利用sql2005的函数ROW_NUMBER，其代码如下：
(1)
WITH test1 AS
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY areaid ORDER BY score desc) AS 'RowNumber'
FROM test
)
SELECT *
FROM test1
WHERE RowNumber BETWEEN 1 AND 3;
(2)
select areaid,score from(
select *,row_number() over(partition by areaid order by areaid desc) row_number from test) a where row_number<6 and row_number>2
--第三种方法是利用sql2005的cross apply来实现，其代码如下：
select distinct t.* from test a
cross apply
(select top 3 areaid,score from test
where a.areaid=areaid order by score desc) as T
sql取每个分组的第一行数据
SELECT a.* FROM table1 a INNER JOIN ( SELECT MAX(a + b)TIME FROM table1 GROUP BY c ) b ON a.a + a.b = b.TIME
当然sql2005 的Row_Number让很多问题有多种解法：
SELECT * FROM (SELECT t.*, RANK() OVER (PARTITION BY t.a ORDER BY t.b DESC) AS drankFROM table1 t) a WHERE drank=1
转载于:https://www.cnblogs.com/caicainiao/archive/2013/02/20/2918835.html
相关资源：JAVA上百实例源码以及开源项目