name score
a fds
a mm
b vcc
ccc cc
ccc xx
ccc ppp怎样按照name出现频率降序排,也就是出现
name score
ccc cc
ccc xx
ccc ppp
a fds
a mm
b vcc
a fds
a mm
b vcc
ccc cc
ccc xx
ccc ppp怎样按照name出现频率降序排,也就是出现
name score
ccc cc
ccc xx
ccc ppp
a fds
a mm
b vcc
insert @a select 'a' ,'fds'
union all select 'a','mm'
union all select 'b','vcc'
union all select 'c','cc'
union all select 'c','xx'
union all select 'c','ppp'select a.* from @a a Inner join (select top 100 percent name,count(1) s from @a group by name order by count(1) desc) b on a.name=b.name order by s desc
from tablename a
join (select [name], count(*) as [count]
from tablename
group by [name]) b on a.[name] = b.[name]
order by b.[count] desc
(
t_name varchar(10),
score varchar(10)
)insert into t_t(t_name,score)
select 'ccc','cc'union
select 'ccc','xx'union
select 'ccc','ppp'union
select 'a','fds'union
select 'b','mm'union
select 'c','vcc'select a.t_name,a.score,b.amount from t_t a,
(select t_name,count(*) as amount from t_t group by t_name) b
where a.t_name=b.t_name
order by b.amount desc
insert into t select 3,1
insert into t select 3,2
insert into t select 3,3
insert into t select 2,4
insert into t select 2,5
insert into t select 1,6
select a.* from t a
inner join (select c1,count(*) as num from t group by c1) b
on a.c1=b.c1
order by b.num desc drop table tc1 c2
----------- -----------
3 1
3 2
3 3
2 4
2 5
1 6
insert @a
select 'a', 'fds' union all
select 'a', 'mm' union all
select 'b', 'vcc' union all
select 'ccc', 'cc' union all
select 'ccc', 'xx' union all
select 'ccc', 'ppp'select * from @a a order by (select count(*) from @a where [name]=a.[name] ) desc
(所影响的行数为 6 行)name score
-------------------- --------------------
ccc cc
ccc xx
ccc ppp
a fds
a mm
b vcc(所影响的行数为 6 行)