我现在可以输出如下形式
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
------解决方案--------------------
- SQL code
select isnull(t1.VisDate,t2.BaseDate) time,isnull(t2.count_BaseDate,0) count_BaseDate,isnull(t1.count_visdate,0) count_visdatefrom ( select VisDate,count(*) count_visdate from tb where VisDate is not null) t1full join( select BaseDate,count(*) count_BaseDate from tb where BaseDate is not null) t2on 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 awhere visdate is not nullgroup 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