有个表people有个字段name,我想统计name重复次数大于2的个数
如:
name
--------
abc
abc
abc
bbb
bbb
bbb
ccc
查询结果 为 : 2
------解决方案--------------------
select count(*) from
(select name from people group by name having(count(1))> 2) a
------解决方案--------------------
create table #
(
name varchar(10)
)
insert into #
select 'zhang ' union all
select 'zhang ' union all
select 'zhang ' union all
select 'zhao ' union all
select 'zhao ' union all
select 'zhao ' union all
select 'chen '
select count(*) from
(
select name ,count(*)as c from # group by name having count(*)> 2
)a
-----------
2
(1 row(s) affected)
------解决方案--------------------
select name,total=count(1) from people
group by name having count(1)> 2