select count(*) as 项目数量,name,identity(int,1,1) as xh into #a from tb order by 项目数量 descselect xh as 排名,name as 姓名,项目数量 from #a
试试这个: select identity(int,1,1) as 排名,[name] as 姓名,num as 项目数量 into #t from ( select name,count(*) as num from yourtable group by [name] ) a order by num select * from #t drop table #t
应该降序,下面是测试:create table #a( [name] varchar(10), obj varchar(10))insert into #a select '1','adfa' insert into #a select '1','adfa' insert into #a select '1','adfa' insert into #a select '2','adfa' insert into #a select '2','adfa' insert into #a select '3','adfa' insert into #a select '3','adfa' insert into #a select '4','adfa'select identity(int,1,1) as 排名,[name] as 姓名,num as 项目数量 into #t from ( select name,count(*) as num from #a group by [name] ) a order by num desc select * from #t drop table #a drop table #t
from tb
order by 项目数量 descselect xh as 排名,name as 姓名,项目数量 from #a
select identity(int,1,1) as 排名,[name] as 姓名,num as 项目数量 into #t from (
select name,count(*) as num from yourtable group by [name] ) a order by num
select * from #t
drop table #t
[name] varchar(10),
obj varchar(10))insert into #a select '1','adfa'
insert into #a select '1','adfa'
insert into #a select '1','adfa'
insert into #a select '2','adfa'
insert into #a select '2','adfa'
insert into #a select '3','adfa'
insert into #a select '3','adfa'
insert into #a select '4','adfa'select identity(int,1,1) as 排名,[name] as 姓名,num as 项目数量 into #t from (
select name,count(*) as num from #a group by [name] ) a order by num desc
select * from #t
drop table #a
drop table #t