如何求某表中，某列同一值出现的总次数

[PB菜鸟]

如 表A有列AA,BB 数据如下： 怎么写语句得到 该表中AA列值为2的重复值最大，有3个重复值。
或者能把该表中有重复值的最多的值取出来，就是把2取去

AA, BB
1 ,a
2 , b
1 , c
3 , b
4 , b
2 ，d
2 ，e

[喜-喜]

use test
go
if object_id('test.dbo.tb') is not null drop table tb
-- 创建数据表
create table tb
(
AA int,
BB char(2)
)
go
--插入测试数据
insert into tb select 1,'a'
union all select 2,'b'
union all select 1,'c'
union all select 3,'b'
union all select 4,'b'
union all select 2,'d'
union all select 2,'e'
go
--代码实现

;with t as(select AA,num=count(AA) from tb group by AA)
select * from t tt where not exists(select 1 from t where num>tt.num)

/*测试结果

AA  num
---------------------
2	3

(1 行受影响)
*/

[s_111111]

select top 1 AA,count(AA) as cnt from AA group by AA order by cnt desc

[就是just4]

DECLARE @tab TABLE(aa INT,bb VARCHAR(20))
INSERT INTO @tab(aa,bb)
select 1,'a' union all
select 2,'b' union all
select 1,'c' union all
select 3,'b' union all
select 4,'b' union all
select 2,'d' union all
select 2,'e'

SELECT TOP 1 tt.aa,tt.cnt FROM (SELECT t.aa,COUNT(*) AS cnt FROM @tab AS t GROUP BY t.aa ) AS tt ORDER BY tt.cnt DESC
/*
aa          cnt         
----------- ----------- 
2           3
*/

[s_111111]

select top 1 AA,count(AA) as cnt from A group by AA order by cnt desc

[Mark杨]

declare @tb table(AA int,BB varchar(4))
insert @tb 
select 1,'a' union all
select 2,'b' union all
select 3,'c' union all
select 1,'d' union all
select 2,'e' union all
select 2,'f' 

select top 1 AA from
(
select AA,count(1) id from @tb group by AA
) b order by id

（所影响的行数为 6 行）

AA          
----------- 
3

（所影响的行数为 1 行）

[Mark杨]

select top 1 AA from
(
select AA,count(1) id from @tb group by AA
) b order by id desc

AA          
----------- 
2

[claro]

怎么写语句得到 该表中AA列值为2的重复值最大，有3个重复值。
借1F数据：

select top 1 BB,COUNT(BB) 次数
from tb
group by BB
order by 2 desc
--result:
BB   次数
---- -----------
b    3

(1 行受影响)

或者能把该表中有重复值的最多的值取出来，就是把2取去
--不知道2是指第二行还是AA为2？还是数据有问题。

--try
select *
from tb a
where not exists (
select *
from tb b 
where a.BB=b.BB and a.AA < b.AA )
--result:
AA          BB
----------- ----
1           a 
1           c 
4           b 
2           d 
2           e 

(5 行受影响)