create table visit
(
    int Id,
    datetime VisitTime
)
insert into visit
select 1, '2012-1-1 18:00'
union all
select 2, '2012-1-1 19:00'
union all
select 3, '2012-1-3 00:00'
union all
select 4, '2012-1-4 00:00'
union all
select 5, '2012-1-5 00:00'

create table logon
(
    int Id,
    varchar(50) User,
    datetime LogonTime
)
insert into logon
select 1, '张三', '2012-1-1 18:00'
union all
select 2, '张三', '2012-1-1 19:00'
union all
select 3, '张三', '2012-1-3 00:00'
union all
select 4, '张三', '2012-1-4 00:00'
union all
select 5, '李四' '2012-1-4 00:00'

/*
期望结果
日期    访问次数    登录人数(过滤同一人的重复登录)
2012-1-1    2    1
2012-1-2    0    0
2012-1-3    1    1
2012-1-4    1    2
2012-1-5    1    0
*/

create table visit
(
    Id int,
    VisitTime datetime 
)

insert into visit
select 1, '2012-1-1 18:00'
union all
select 2, '2012-1-1 19:00'
union all
select 3, '2012-1-3 00:00'
union all
select 4, '2012-1-4 00:00'
union all
select 5, '2012-1-5 00:00'

create table logon
(
    Id int,
    Users varchar(50),
    LogonTime datetime 
)

insert into logon
select 1, '张三', '2012-1-1 18:00'
union all
select 2, '张三', '2012-1-1 19:00'
union all
select 3, '张三', '2012-1-3 00:00'
union all
select 4, '张三', '2012-1-4 00:00'
union all
select 5, '李四', '2012-1-4 00:00'


select dateadd(d,number,'2012-01-01') '日期',
(select count(*) from visit 
where convert(varchar,VisitTime,111)=convert(varchar,dateadd(d,number,'2012-01-01'),111)) '访问次数',
(select count(distinct Users) from logon 
where convert(varchar,LogonTime,111)=convert(varchar,dateadd(d,number,'2012-01-01'),111)) '登录人数'
from master.dbo.spt_values
where [type]='P' and number between 0 and 4

日期                      访问次数        登录人数
----------------------- ----------- -----------
2012-01-01 00:00:00.000   2           1
2012-01-02 00:00:00.000   0           0
2012-01-03 00:00:00.000   1           1
2012-01-04 00:00:00.000   1           2
2012-01-05 00:00:00.000   1           0

(5 row(s) affected)

------解决方案--------------------
可以不可以用BETWEEN...AND啊？

------解决方案--------------------
select IA.VisitTime '日期',IA.VisitNumer '访问次数',
ISNULL(IB.LoginTimes,0)  '登录人数'  
from (
	select CAST(visitTime as date) 'VisitTime'
	,COUNT(0) 'VisitNumer'  
	from visit
	group by CAST(visitTime as date)
)  IA   left join  
(
	select LogonTime,COUNT([USER]) 'LoginTimes' from  (
	select  distinct cast(LogonTime as date) 'LogonTime',[USER]  
	from logon) IC
	group by LogonTime
)  IB
on IA.visitTime = IB.LogonTime