如果一个数据库表中,有这么些记录:
id, aid content
1 1 aa
2 1 bb
3 1 cc
4 2 dd
5 2 hh
6 5 ll
7 5 oo
8 5 pp
9 8 rr
10 8 qq
……
如何才能获得 aid 记录最多的 3个
id, aid content
1 1 aa
2 1 bb
3 1 cc
4 2 dd
5 2 hh
6 5 ll
7 5 oo
8 5 pp
9 8 rr
10 8 qq
……
如何才能获得 aid 记录最多的 3个
select top 3 aid,count(1) as aidnums from tb group by aid order by aidnums desc
group by aid
order by count(1) desc
id, aid content
1 1 aa
2 1 bb
3 1 cc
4 2 dd
5 2 hh
6 5 ll
7 5 oo
8 5 pp
9 8 rr
10 8 qq
……
如何才能获得 aid 记录最多的 3个
select top 3 aid , count(*) cnt from tb group by aid order by cnt desc
insert into tb values(1 , 1 , 'aa')
insert into tb values(2 , 1 , 'bb')
insert into tb values(3 , 1 , 'cc')
insert into tb values(4 , 2 , 'dd')
insert into tb values(5 , 2 , 'hh')
insert into tb values(6 , 5 , 'll')
insert into tb values(7 , 5 , 'oo')
insert into tb values(8 , 5 , 'pp')
insert into tb values(9 , 8 , 'rr')
insert into tb values(10, 8 , 'qq')
goselect top 3 aid , count(*) cnt from tb group by aid order by cnt descdrop table tb/*
aid cnt
----------- -----------
5 3
1 3
8 2(3 行受影响)
*/
if object_id('tb') is not null
drop table tb
go
create table tb(id int, aid int, content varchar(10))
insert into tb values(1 , 1 , 'aa')
insert into tb values(2 , 1 , 'bb')
insert into tb values(3 , 1 , 'cc')
insert into tb values(4 , 2 , 'dd')
insert into tb values(5 , 2 , 'hh')
insert into tb values(6 , 5 , 'll')
insert into tb values(7 , 5 , 'oo')
insert into tb values(8 , 5 , 'pp')
insert into tb values(9 , 8 , 'rr')
insert into tb values(10, 8 , 'qq')
select * from tb
select top 3 aid,count(1) from tb group by aid order by count(1) desc
select top 3 aid,count(1) from tb group by aid order by count(1) desc