新手救教：怎样分别统计不含重复的计录数和重复的计录数?
表a
姓名 日期
刘立 2007-01-01
王刚 2007-01-01
李山 2007-01-01
吴南 2007-01-01
刘立 2007-01-01
李海 2007-01-05
刘立 2007-01-01
李山 2007-01-01
得出:
不含重复的计录数:5条
重复的计录数:2条
[解决办法]
declare @a table (a char,b int)
insert into @a select 'a ',1
union all select 'b ',1
union all select 'c ',1
union all select 'd ',1
union all select 'a ',1
union all select 'e ',2
union all select 'a ',1
union all select 'c ',1
select * from @a
declare @b int
declare @c int
select @b =count(*) from (select distinct a,b from @a c where(select count(*) from @a where a=c.a and b=c.b)> 1) as d
select @c =count(*) from @a where a not in (select distinct a from @a c where(select count(*) from @a where a=c.a and b=c.b)> 1)
or b not in(select distinct b from @a c where(select count(*) from @a where a=c.a and b=c.b)> 1)
print '不含重复的计录数: '+cast(@c as varchar(20))
print '重复的计录数: '+cast(@b as varchar(20))
[解决办法]
来个别的：
create table #temp
(A varchar(50),
B varchar(50)
)
insert into #temp
select '刘立 ', '2007-01-01 ' union all select '王刚 ', '2007-01-01 ' union all select '李山 ', '2007-01-01 ' union all select '吴南 ', '2007-01-01 ' union all select '刘立 ', '2007-01-01 ' union all select '李海 ', '2007-01-05 ' union all select '刘立 ', '2007-01-01 ' union all select '李山 ', '2007-01-01 '
select * from #temp
重复的记录数：
select count(*) from (select A,max(B) B from #temp group by A having count(B)> 1) a
------
2
不含重复的记录数：
select count(*) from (select A,max(B) B from #temp group by A having count(B)=1) b
------------------
3
选出不重复的所有记录数：
select distinct * from #temp
------------
5
