(后篇)输出同一时间两个字段的count值（有其他条件）？
上午鸟儿前辈帮我解决了一个类似的问题，中午的时候我尝试自己增加字段
但是不能解决，希望各位前辈来帮我看看

declare @a table(VisDate smalldatetime, BaseDate smalldatetime, CompName varchar(50), IpAddr varchar(50))
insert @a select null , '2007-10-18' , 'aaa' , null
union all select null ,'2007-10-18' ,'iyv', null
union all select null ,'2007-10-23' ,'qq', null
union all select '2007-10-18', null , null , '55.55.55.5'
union all select '2007-10-18', null , null, '123.123.123.1io2'
union all select '2007-10-18', null , null, '123.123.123.12'
union all select '2007-10-18', null , null, '414.41.411.1'
union all select '2007-10-23', null , null, '202.199.139.80'
union all select '2007-10-23', null , null, '11.2.3.3'

我现在要搜出
当VisDate和BaseDate 不为空而且相等的时候分别的四个count值，前两个没有条件，后两个第一个是 compname <> aaa
第二个是ipaddr <> '414.41.411.1' 输出完了应该是这样的 

visdate visi base realbase realvisi
2007-10-18 4 2 1 3
2007-10-23 2 1 1 2

我自己在鸟儿前辈语句上面修改的如下：但这样前三列都正确，最后一列是不对的，做关联的时候就把数据都晒掉了，剩下的是
0。请教如何实现上述结果？

select visdate,count(1) visi ,base=(select count(1) from @a where basedate=a.visdate)
,realbase = (select count(1) from @a where basedate=a.visdate and CompName <> 'aaa')
,realvisi = ( select count(1) from @a where a.visdate=basedate and IpAddr <>'414.41.411.1')
from @a a where visdate is not null group by visdate

------解决方案--------------------

select visdate,count(1) visi ,base=(select count(1) from @a where basedate=a.visdate) 
,realbase = (select count(1) from @a where basedate=a.visdate and CompName < > 'aaa ') 
,realvisi = ( select count(1) from @a where a.visdate=visdate and IpAddr < > '414.41.411.1 ') 
from @a a where visdate is not null group by visdate


把realvisi = ( select count(1) from @a where a.visdate=basedate and IpAddr < > '414.41.411.1 ')这里的basedate改成visdate 就可以了@