日期:2014-5-19 浏览次数:20105次

求一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个列出来(重复的只列一个)?请教了

------解决方案--------------------
select top 40 id from aaa group by id order by count(*) desc
------解决方案--------------------
select top 40 id from aaa group by id order by count(id) desc
------解决方案--------------------

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
------解决方案--------------------
select top 40 * from (select id,count(*) as bb from aaa group by id)a order by bb desc
------解决方案--------------------
select top 40 id into #temp from aaa group by id order by count(id) desc
------解决方案--------------------
select top 40 id from aaa
group by id
order by count(*) desc