求一SQL语句：出现次数最多的前40个列出来

[一剑]

表名：aaa，只有一列整形int，列名id
现在有一些记录记录：
50
52
52
52
52
52
52
53
53
53
53
53
53
53
53
53
53
53
53
53
53
53
53
53
56
56
56
62
62
62
64
64
64
64
64
64
64
64
64
64
64
64
64
64
64
64
64
64
64
64
64
80
80
80
80
81
81
82
82
82
82
83
83
85
86
86
86
86
86
86
86
86
86
87
87
87
87
87
87
87
87
87
87
87
87
87
87
87
87
88
88
88
88
88
88
88
88
88
88
88
88
88
88
88
89
90
90
90
90
90
90
91
91
91
91
91
91
92
93
95
96
96
96
96
96
96
96
97
97
97
97
97
97
97
97
97
97
97
97
97
97
97
97
98
98
98
98
98
99
99
99
99
100
100
100
100
100
100
101
101
101
101
101
101
101
101
101
101
101
101
101
101
101
101
103
104
104
104
106
106
109
109
110
110
110
110
110
110
110
110
110
110
110
113
113
113
113
113
114
114
...
求一SQL语句把出现次数最多的前40个列出来（重复的只列一个）？请教了

[akuzou]

select top 40 id from aaa group by id order by count(*) desc

[jacobsan]

select top 40 id from aaa group by id order by count(id) desc

[leo_lesley]

create table aaa
(
id varchar(10),
)
insert into aaa select '1'
insert into aaa select '1'
insert into aaa select '1'
insert into aaa select '2'
insert into aaa select '2'
insert into aaa select '2'
insert into aaa select '3'
insert into aaa select '3'
insert into aaa select '4'

select top 40 id,count(id) num
from aaa
group by id
order by count(id) desc

drop table aaa

[一剑]

谢谢了哈，感谢了，帮了大忙了

[caixia615]

select top 40 * from (select id,count(*) as bb from aaa group by id)a order by bb desc

[一剑]

再问下啊
得到这40条记录后我如何把他们加入另一个临时表中去啊？

[jacobsan]

select top 40 id into #temp from aaa group by id order by count(id) desc

[jacobsan]

insert into #temp select top 40 id from aaa group by id order by count(id) desc

如果临时表存在这样写

[xiequanqin]

都解决了。。
只有顶的份~

[marco08]

select top 40 id from aaa
group by id
order by count(*) desc

[dingzhihui]

学习了

[river_2012]

太有帮助了，正好遇到同样的问题！谢谢！