select isnull(t1.VisDate,t2.BaseDate) time,isnull(t2.count_BaseDate,0) count_BaseDate,isnull(t1.count_visdate,0) count_visdate
from 
(
  select VisDate,count(*) count_visdate from tb where VisDate is not null
) t1
full join
(
  select BaseDate,count(*) count_BaseDate from tb where BaseDate is not null
) t2
on t1.VisDate = t2.VisDate

------解决方案--------------------
SQL code
declare @a table(VisDate           smalldatetime,          BaseDate  smalldatetime)
insert @a select null ,                       '2007-10-18'  
union all select null                        ,'2007-10-18' 
union all select null                        ,'2007-10-23' 
union all select '2007-10-18',                  null 
union all select '2007-10-18',                  null 
union all select '2007-10-18',                  null 
union all select '2007-10-23',                  null 
union all select '2007-10-23',                  null 
union all select '2007-10-23',                  null 
union all select '2007-10-23',                  null 

select visdate,count(1)a ,ba=(select count(1) from @a where basedate=a.visdate) from @a a where visdate is not null group by visdate

------解决方案--------------------
SQL code


declare @t table(id int identity,visdate varchar(10),basedate  varchar(10))
insert into @t(basedate) select '2007-10-18'
insert into @t(basedate) select '2007-10-18'
insert into @t(basedate) select '2007-10-23'
insert into @t(visdate) select '2007-10-18'
insert into @t(visdate) select '2007-10-18'
insert into @t(visdate) select '2007-10-18'
insert into @t(visdate) select '2007-10-23'
insert into @t(visdate) select '2007-10-23'
insert into @t(visdate) select '2007-10-23'
insert into @t(visdate) select '2007-10-23'
select visdate,COUNT(1)  AS vis_basedate,(select count(1) from @t b where a.visdate=b.basedate)  AS count_basedate from @t a
where visdate is not null
group by visdate

------解决方案--------------------
SQL code


declare @a table(VisDate           smalldatetime,          BaseDate  smalldatetime)
insert @a select null ,                       '2007-10-18'  
union all select null                        ,'2007-10-18' 
union all select null                        ,'2007-10-23' 
union all select '2007-10-18',                  null 
union all select '2007-10-18',                  null 
union all select '2007-10-18',                  null 
union all select '2007-10-23',                  null 
union all select '2007-10-23',                  null 
union all select '2007-10-23',                  null 
union all select '2007-10-23',                  null 
select a.visdate,sum(case when visdate is not date