输出同一时间两个字段的count值？

[ClsData]

我现在可以输出如下形式
VisDate BaseDate
null 2007-10-18
null 2007-10-18
null 2007-10-23
2007-10-18 null
2007-10-18 null
2007-10-18 null
2007-10-23 null
2007-10-23 null
2007-10-23 null
2007-10-23 null

暂且叫#a

当VisDate和basedate不为空而且相等的时候分别的count值，输出完了应该是这样的
time count_basedate count_visdate
2007-10-18 2 3
2007-10-23 1 4

[chuifengde]

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

[dawugui]

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

[chuifengde]

--result
/*
visdate                                                a           ba          
------------------------------------------------------ ----------- ----------- 
2007-10-18 00:00:00                                    3           2
2007-10-23 00:00:00                                    4           1

（所影响的行数为 2 行）

*/

[ojuju10]

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

[ClsData]

id VisDate BaseDate
1 null 2007-10-18
2 null 2007-10-18
3 null 2007-10-23
4 2007-10-18 null
5 2007-10-18 null
6 2007-10-18 null
7 2007-10-23 null
8 2007-10-23 null
9 2007-10-23 null
10 2007-10-23 null

龟龟，这是一个表的两列

[wuxinyuzhu]

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 null then 1 end) as 'count_visdate',b.count_basedate
		
from @a a inner join (select basedate, sum(case when basedate is not null then 1 end) 'count_basedate' from @a group by  basedate) b
on a.visdate=b.basedate 
group by a.visdate,b.count_basedate

[ClsData]

谢谢楼上各位，鸟儿的我已经采用
谢谢参与