Question

sql如何查询出某字段的重复数据并输出重复次数

sql如何查询出某字段的重复数据并输出重复次数

例如：表tt中有字段cc，现要查询cc中等于0，等于1，等于2的数量，并输出
那如何输出 等于0的有多少 等于1的有多少 等2的有多少呢

Answer 1

select sum(case when cc=0 then 1 else 0) 0次数,sum(case when cc=1 then 1 else 0) 1次数,sum(case when cc=2 then 1 else 0) 2次数 from tt

Answer 2

declare @Mytable table (Value int,Num int)
declare mycursor cursor for select distinct cc from tt
declare @value int
declare @num int

open mycursor

fetch next from mycursor into @value

while @@fetch_status=0
begin
select @num=count(*) from tt where cc=@value
insert into @mytable values(@value,@num)
fetch next from mycursor into @value
end

select value 值,num 数量 from @mytable

close mycursor
deallocate mycursor

--呵呵，

Answer 3

二楼的加个or嘛..
select count(*) from tt where cc=0 or cc=1 or cc=2

如果要分开统计的话如下：
select count(*)
from tt
group by cc
having cc=0 or cc=1 or cc=2

Answer 4

select count(*) from 表tt where cc=0 UNION select count(*) from 表tt where cc=1 UNION select count(*) from 表tt where cc=2