日期:2014-05-18  浏览次数:20564 次

问个题,统计个数
有个表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