如何统计以下数据！

[newabby]

F1 F2
A i1
A i1
A i1
A i2
A i2
A i2
A i3
A i3
A i4
B i3
B i3
B i3
B i2
B i2
B i5
B i1
B i6
B i4
.....

怎么用SQL语句统计出按F1，F2分组后再按F2的排名取出两N位，比如上面的例子，取前两位的结果

A i1 3
A i2 3
B i3 3
B i2 2

后面这排数字是F2的count值

[九斤半]

第一步:
select F1,F2,count(*) as 记录数 into # from tablename

[九斤半]

第二步：参考

--求学生语文（CHINESE）成绩的名次）
--同分的名次一样，后面的名次要跳过一个名次
declare @t table(stu_id int,stud_name varchar(20),chinese decimal(10,2))
insert @t
select 1,'jfk',77 union all
select 2,'Jk',63.2 union all
select 3,'djfkd',44 union all
select 4,'fddk',63.2 union all
select 5,'fd',75 union all
select 6,'gg',77

select *,chineseorder = (select count(*) from @t where chinese > a.chinese) + 1 from @t a
order by chineseorder

[hellowork]

----创建测试数据
declare @t table(F1 varchar(10), F2 varchar(10))
insert @t
select 'A', 'i1' union all
select 'A', 'i1' union all
select 'A', 'i1' union all
select 'A', 'i2' union all
select 'A', 'i2' union all
select 'A', 'i2' union all
select 'A', 'i3' union all
select 'A', 'i3' union all
select 'A', 'i4' union all
select 'B', 'i3' union all
select 'B', 'i3' union all
select 'B', 'i3' union all
select 'B', 'i2' union all
select 'B', 'i2' union all
select 'B', 'i5' union all
select 'B', 'i1' union all
select 'B', 'i6' union all
select 'B', 'i4'

----查询
SELECT * FROM
(select F1,F2,COUNT(*) as counts from @t group by F1,F2) as a
WHERE a.F2 in (select top 2 F2 from
(select F1,F2,COUNT(*) as counts from @t group by F1,F2) as b
where b.F1 = a.F1 order by b.counts desc)
ORDER BY a.F1,a.counts DESC

/*结果
F1 F2 counts
--------------------------------
A i1 3
A i2 3
B i3 3
B i2 2
*/