select *
from tb
where name in (
  select top 3 name
  from (
    select name,count(*) cnt
    from tb
    group by name
  )t
  order by cnt desc
)

------解决方案--------------------

SQL code

--> 测试数据：[tbl]
if object_id('[tbl]') is not null drop table [tbl]
create table [tbl]([name] varchar(1),[type] varchar(2))
insert [tbl]
select 'A','01' union all
select 'A','02' union all
select 'A','03' union all
select 'A','04' union all
select 'A','05' union all
select 'B','01' union all
select 'B','02' union all
select 'C','01' union all
select 'C','02' union all
select 'C','03' union all
select 'C','04' union all
select 'D','01' union all
select 'D','02' union all
select 'D','03' union all
select 'E','01'
select * from tbl where name in(select name from(
select row_number()over(order by count(*) desc) as id,name
from tbl group by name)a where id<=3)

/*
name    type
A    01
A    02
A    03
A    04
A    05
C    01
C    02
C    03
C    04
D    01
D    02
D    03
*/