求一条sql语句

[small_wei]

有一表，有两个字段：
A列，B列
a 1
a 2
a 3
a 4
a 5
a .
.....
b 2
b 3
b 4
b 5
b 9
b 10
...

d 10
d 12
d 13
d 14


现在想得到的结果是A列中内容相同的，且B列不同的，取前3行结果。
如
A列，B列
a 1
a 2
a 3
b 2
b 3
b 4
d 10
d 12
d 13

一条sql 语句如何实现？

[Limpire]

--> 测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (A varchar(1),B int)
insert into #T
select 'a',1 union all
select 'a',1 union all
select 'a',2 union all
select 'a',3 union all
select 'a',4 union all
select 'a',5 union all
select 'b',2 union all
select 'b',3 union all
select 'b',3 union all
select 'b',4 union all
select 'b',5 union all
select 'b',9 union all
select 'b',10 union all
select 'd',10 union all
select 'd',12 union all
select 'd',12 union all
select 'd',12 union all
select 'd',13 union all
select 'd',14

--> 哦，去重复：
select distinct A, B from #T as t where B in (select distinct top 3 B from #T where A=t.A) order by A, B

/*
A    B
a    1
a    2
a    3
b    2
b    3
b    4
d    10
d    12
d    13
*/

[tianhuo_soft]

select * from #T as a
where B in(select top 3 b from #T where A=a.A order by B)

[让你望见影子的墙]

先分组，然后使用top3 取前三行

[-狙击手-]

给一个例子

--测试环境
create table student --学生列表
(
s_id int,
s_name char(10)
)
create  table class --课程列表
(
c_id int,
c_name char(10)
)

create table grade --学生成绩表
(
s_id int,
c_id int,
cj int
)

--测试数据
insert student values(1,'A')
insert student values(2,'B')
insert student values(3,'C')
insert student values(4,'D')
insert student values(5,'E')

insert class values(1,'历史')
insert class values(2,'数学')

insert grade values(1,1,60)
insert grade values(2,1,70)
insert grade values(3,1,80)
insert grade values(4,1,90)
insert grade values(5,1,100)
insert grade values(1,2,85)
insert grade values(2,2,67)
insert grade values(3,2,94)
insert grade values(4,2,63)
insert grade values(5,2,87)


select a.c_id,a.s_id,a.cj from grade a 
join grade b
on a.c_id = b.c_id 
group by a.c_id,a.s_id,a.cj
having count(case when a.cj <= b.cj then 1 else null end) < = 5 --可动态修改
order by a.c_id,a.cj desc


--处理重复分数
select a.c_id,a.s_id,a.cj 
from grade a join 
( 
  select c_id,cj
  from grade
  group by c_id,cj
) b
on a.c_id = b.c_id 
group by a.c_id,a.s_id,a.cj
having count(case when a.cj <= b.cj then 1 else null end) < = 5 --可动态修改
order by a.c_id,a.cj desc

--你可以以动态的修改: n  <= n 来获得
--每门课程的前n个最高分的

[-狙击手-]

楼主的应该是

create table ta (A varchar(1),B int)
insert into ta
select 'a',1 union all
select 'a',2 union all
select 'a',3 union all
select 'a',4 union all
select 'a',5 union all
select 'b',2 union all
select 'b',3 union all
select 'b',4 union all
select 'b',5 union all
select 'b',9 union all
select 'b',10 union all
select 'd',10 union all
select 'd',12 union all
select 'd',13 union all
select 'd',14


select a.a,a.b
from ta a 
join ta b
on a.a = b.a 
group by a.a,a.b
having count(case when a.b <= b.b then 1 else null end) < = 3 --可动态修改
order by a.a,a.b desc


drop table ta

/*
a    b           
---- ----------- 
a    5
a    4
a    3
b    10
b    9
b    5
d    14
d    13
d    12
*/

[-狙击手-]

create table ta (A varchar(1),B int)
insert into ta
select 'a',1 union all
select 'a',2 union all
select 'a',3 union all
select 'a',4 union all
select 'a',5 union all
select 'b',2 union all
select 'b',3 union all
select 'b',4 union all
select 'b',5 union all
select 'b',9 union all
select 'b',10 union all
select 'd',10 union all
select 'd',12 union all
select 'd',13 union all
select 'd',14


select a.a,a.b
from ta a 
join ta b
on a.a = b.a 
group by a.a,a.b
having count(case when a.b >= b.b then 1 else null end) < = 3 --可动态修改
order by a.a,a.b


drop table ta

/*

a    b           
---- ----------- 
a    1
a    2
a    3
b    2
b    3
b    4
d    10
d    12
d    13

（所影响的行数为 9 行）

*/

[Limpire]

--> 测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (A varchar(1),B int)
insert into #T
select 'a',1 union all
select 'a',2 union all
select 'a',3 union all
select 'a',4 union all
select 'a',5 union all
select 'b',2 union all
select 'b',3 union all
select 'b',4 union all
select 'b',5 union all
select 'b',9 union all
select 'b',10 union all
select 'd',10 union all
select 'd',12 union all
select 'd',13 union all
select 'd',14

select * from #T as t where B in (select top 3 B from #T where A=t.A order by B)

/*
A    B
a    1
a    2
a    3
b    2
b    3
b    4
d    10
d    12
d    13
*/

[dawugui]

select t.* from tb t
where b in (select top 3 b from tb where a = t.a order by a,b)

[dawugui]

如果A+B有重复.
则:

select t.* from
(
select distinct a , b from tb
) t
where b in (select top 3 b from
(
select distinct a , b from tb
) m
where a = t.a order by a,b)