表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