日期:2014-05-18  浏览次数:21123 次

求一个数量排前三记录的SQL查询语句
要根据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 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
*/