----------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2011-09-27 09:56:33
-- Verstion:
--      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) 
--    Apr 22 2011 11:57:00 
--    Copyright (c) Microsoft Corporation
--    Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([thedate] datetime,[name] varchar(5),[hitTimes] int)
insert [tb]
select '2011-9-26','tom',2 union all
select '2011-9-24','kitty',1 union all
select '2011-9-24','tom',2 union all
select '2011-9-24','tom',3 union all
select '2011-9-23','kitty',3 union all
select '2011-9-23','tom',1 union all
select '2011-9-23','marry',1
--------------开始查询--------------------------
declare @startdate datetime,@enddate datetime
set @startdate='2011-09-23'
set @enddate='2011-09-26'
;with f as
(
select
    convert(varchar(10),dateadd(day,number,@startdate),120) as dtime,b.name 
from
    master..spt_values ,(select distinct name from tb) b
where 
    datediff(day,dateadd(day,number,@startdate), @enddate)>=0
and
    number>=0 
and
    type='p'
)

select a.dtime,a.name,ISNULL(b.hitTimes,0) as hitTimes from f a left join tb b on a.dtime=b.thedate and a.name=b.name order by 1 desc
----------------结果----------------------------
/* dtime      name  hitTimes
---------- ----- -----------
2011-09-26 kitty 0
2011-09-26 marry 0
2011-09-26 tom   2
2011-09-25 marry 0
2011-09-25 tom   0
2011-09-25 kitty 0
2011-09-24 marry 0
2011-09-24 kitty 1
2011-09-24 tom   2
2011-09-24 tom   3
2011-09-23 kitty 3
2011-09-23 marry 1
2011-09-23 tom   1

(13 行受影响)

*/