表结果如下:id, name, date
1, 张三, 2010-2-10
2, 张三, 2010-2-10
3, 李四, 2010-2-10
4, 李四, 2010-2-10
5, 李四, 2010-2-10
6, 小明, 2010-2-10
7, 小明, 2010-2-10
8, 小明, 2010-2-10
9, 小明, 2010-2-10现要求姓名最多的进行倒序显示,并显示相同记录的一条记录,如:
小明(4)
李四(3)
张三(2)
1, 张三, 2010-2-10
2, 张三, 2010-2-10
3, 李四, 2010-2-10
4, 李四, 2010-2-10
5, 李四, 2010-2-10
6, 小明, 2010-2-10
7, 小明, 2010-2-10
8, 小明, 2010-2-10
9, 小明, 2010-2-10现要求姓名最多的进行倒序显示,并显示相同记录的一条记录,如:
小明(4)
李四(3)
张三(2)
select name,n_count from (select name,count(name) from tb group by name) a order by name desc
if object_id('[tb]') is not null drop table [tb]
create table [tb] (id int,name varchar(4),date datetime)
insert into [tb]
select 1,'张三','2010-2-10' union all
select 2,'张三','2010-2-10' union all
select 3,'李四','2010-2-10' union all
select 4,'李四','2010-2-10' union all
select 5,'李四','2010-2-10' union all
select 6,'小明','2010-2-10' union all
select 7,'小明','2010-2-10' union all
select 8,'小明','2010-2-10' union all
select 9,'小明','2010-2-10'select result=name+'('+ltrim(count(1))+')' from [tb] group by name order by count(1) desc
--结果:
result
------------------
小明(4)
李四(3)
张三(2)
from tb
group by name
order by cnt desc
结果
------------------
小明(4)
李四(3)
张三(2)
if OBJECT_ID('tb') is not null
drop table tb
go
create table tb (id int, name varchar(5),date date)
insert into tb
select 1, '张三', '2010-2-10' union all
select 2, '张三', '2010-2-10' union all
select 3, '李四', '2010-2-10' union all
select 4, '李四', '2010-2-10' union all
select 5, '李四', '2010-2-10' union all
select 6, '小明', '2010-2-10' union all
select 7, '小明', '2010-2-10' union all
select 8, '小明', '2010-2-10' union all
select 9, '小明', '2010-2-10'select rn=DENSE_RANK()over(order by count(name) desc) ,name,date
from tb
group by name,date
order by COUNT(name) descrn name date
1 小明 2010-02-10
2 李四 2010-02-10
3 张三 2010-02-10select name+'('+cast(COUNT(name) as varchar(5))+')' as name
from tb
group by name
order by COUNT(name) descname
小明(4)
李四(3)
张三(2)
create table [tb] (id int,name Nvarchar(4),date datetime)
insert into [tb]
select 1,N'张三','2010-2-10' union all
select 2,N'张三','2010-2-10' union all
select 3,N'李四','2010-2-10' union all
select 4,N'李四','2010-2-10' union all
select 5,N'李四','2010-2-10' union all
select 6,N'小明','2010-2-10' union all
select 7,N'小明','2010-2-10' union all
select 8,N'小明','2010-2-10' union all
select 9,N'小明','2010-2-10'
SELECT * FROM tbSELECT [NAME]+'('+CONVERT(VARCHAR(10),COUNT(*))+')' FROM TB GROUP BY [NAME] ORDER BY COUNT(*) DESC