求一SQL，关于有条件消除重复列，表结构很简单，就两列，内容里有具体要求，谢谢~~~~~

[哈哈哈哈]

表table结构如下：

id name
1 a1
2 a1
3 a1
4 a1
5 a2
6 a2
7 a3

需要如下结果
id name
1 a1
2 a1
3 a1
5 a2
6 a2
7 a3
其实就是需要重复列name中前三条记录，超过三条取前三条，没超过三条都取~~~~~

谢谢~！！！！

[Limpire]

--> 测试数据: #
if object_id('tempdb.dbo.#') is not null drop table #
create table # (id int,name varchar(2))
insert into #
select 1,'a1' union all
select 2,'a1' union all
select 3,'a1' union all
select 4,'a1' union all
select 5,'a2' union all
select 6,'a2' union all
select 7,'a3'

select * from # a where id in (select top 3 id from # where name = a.name order by 1)
/*
id      name
1        a1
2        a1
3        a1
5        a2
6        a2
7        a3
*/

[bqb]

create table tb(id int,name varchar(10))
insert into tb
select 1,'a1' union all 
select 2,'a1' union all  
select 3,'a1' union all   
select 4,'a1' union all   
select 5,'a2' union all   
select 6,'a2' union all   
select 7,'a3'  

select id,name
from tb a
where (select count(1) as num from tb where name=a.name and id<=a.id)<4
/*
id    name
---------------
1	a1
2	a1
3	a1
5	a2
6	a2
7	a3
*/



drop table tb