SQL 取两日期的记录

SELECT TOP 1000 [ID]
      ,[SourcePageID]
      ,[PlatformType]
      ,[CreateTime]
  FROM [home_sendorder].[dbo].[ApplyUser]
where [PlatformType]=2 and [CreateTime]>='2015-10-28' and [CreateTime]<='2015-11-1'
注意这样写，只是从28号0点查到1号0点

----------------------------------------------------------------------------------------------

当条件为下时，跟datediff()查询的结果一样

where [PlatformType]=2 and [CreateTime]>='2015-10-28' and [CreateTime]<'2015-11-2')

SELECT TOP 1000 [ID]
      ,[SourcePageID]
      ,[PlatformType]
      ,[CreateTime]
  FROM [home_sendorder].[dbo].[ApplyUser]
where [PlatformType]=2 and datediff(day,'11/1/2015',[CreateTime])<=0 and datediff(day,'10/28/2015',[CreateTime])>=0

------------------------------------------------------------------------------------------------------------------------------

select [SourcePageID]，count(*) from [ApplyUser]
where [PlatformType]=2 and datediff(day,'11/1/2015',[CreateTime])<=0 and datediff(day,'10/28/2015',[CreateTime])>=0
group by [SourcePageID]
注意：以上写法是ApplyUser内有的信息为依据，查询出的结果。如图：当SourcePageID=5时候，ApplyUser表里面没有，所以不显示。

 

--------------------------------------------------------------------------------------------------

所以想查询到SourcePageID的全部数据，就要找到  有SourcePageID的全部数据  的表 作为基本表

select b.[SourcePageID], b.[SourcePageName],count(a.id) from [SourceUserPageConfig] b--基本表
 left  join [ApplyUser]a---或inner join
 on  (b.[PlatformType]=a.[PlatformType]  and b.[SourcePageID]=a.[SourcePageID])
where b.[PlatformType]=2
 and datediff(day,'11/1/2015',a.[CreateTime])<=0
 and datediff(day,'10/28/2015',a.[CreateTime])>=0
group by b.[SourcePageID], b.[SourcePageName]

order by  b.[SourcePageID] asc

 

还是没有5，感觉应该是不满足where条件，被删除

但是怎样才能把5这行数据显示出来，有5---0这一行。

请高手指点