要根据name的数量,筛选出数量排前三的记录原始数据
name type
A 01
A 02
A 03
A 04
A 05
B 01
B 02
C 01
C 02
C 03
C 04
D 01
D 02
D 03
E 01查询结果
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
name type
A 01
A 02
A 03
A 04
A 05
B 01
B 02
C 01
C 02
C 03
C 04
D 01
D 02
D 03
E 01查询结果
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
select name,count(*) from tbl group by name)a)
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
)
--> 测试数据:[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
*/
order by (select count(*) from tablename where name=b.name group by name ) desc)
order by(select count(*) from tablename where name=a.name group by name ) desc