TSQL的count问题

[kingmeta]

id username Point
1 张三 100
2 李四 100
3 张三 100
4 张三 100
5 赵大 100
6 赵大 100

如果能得出以下结果

张三 3
赵大 2
李四 1

注意要从大到小排序，排序是按记录数的多少排的

[q806294478]

select username,count(*) from tb group by username order by count(*) desc

[geniuswjt]

select username,count(1) from tb
group by username
order by 2 desc

[唐诗三百首]

create table kin
(id int, username varchar(8), Point int)

insert into kin
select 1, '张三', 100 union all
select 2, '李四', 100 union all
select 3, '张三', 100 union all
select 4, '张三', 100 union all
select 5, '赵大', 100 union all
select 6, '赵大', 100


select username,count(*) ct
from kin
group by username
order by count(*) desc

username ct
-------- -----------
张三       3
赵大       2
李四       1

(3 row(s) affected)

[dawugui]

select username , count(1) cnt from tb group by username order by cnt desc

[叶子]

declare @T table (id int,username varchar(4),Point int)
insert into @T
select 1,'张三',100 union all
select 2,'李四',100 union all
select 3,'张三',100 union all
select 4,'张三',100 union all
select 5,'赵大',100 union all
select 6,'赵大',100

--1.group by 
select username,count(1) cnt from @t group by  username order by 2 DESC

--2.子查询
select distinct username,
(select count(1) from @t where username=t.username) cnt 
from @t t order by 2 desc

--3.count+over
select distinct username,count(1) over (partition by username) from @t 
order by 2 DESC

/*
username cnt
-------- -----------
张三       3
赵大       2
李四       1
*/

[Felixzhaowenzhong]

select distinct username,count(*) from tb group by username order by count(*) desc --只有 ORDER BY 中可以使用别名

[勿勿]

if OBJECT_ID('tb') is not null
drop table tb
go
create table tb(id int, username varchar(50), Point int)
insert into tb
select 1, '张三', 100 union all
select 2, '李四', 100 union all
select 3, '张三', 100 union all
select 4, '张三', 100 union all
select 5, '赵大', 100 union all
select 6, '赵大', 100

select username,COUNT(*)as 次数 from tb group by username order by 次数 desc








username                                           次数
-------------------------------------------------- -----------
张三                                                 3
赵大                                                 2
李四                                                 1

(3 行受影响)

[lwqhp]

--分组，然后按记录数排序
select username,count(*) as count
from tb
group by username
order by count desc