要根据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
------解决方案--------------------
- SQL code
select *from tbwhere 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 allselect 'A','02' union allselect 'A','03' union allselect 'A','04' union allselect 'A','05' union allselect 'B','01' union allselect 'B','02' union allselect 'C','01' union allselect 'C','02' union allselect 'C','03' union allselect 'C','04' union allselect 'D','01' union allselect 'D','02' union allselect 'D','03' union allselect 'E','01'select * from tbl where name in(select name from(select row_number()over(order by count(*) desc) as id,namefrom tbl group by name)a where id<=3)/*name typeA 01A 02A 03A 04A 05C 01C 02C 03C 04D 01D 02D 03*/