thedate name hitTimes
2011-9-26 tom 2
2011-9-24 kitty 1
2011-9-24 tom 2
2011-9-24 tom 3
2011-9-23 kitty 3
2011-9-23 tom 1
2011-9-23 marry 1
如何得出以下结果,
2011-09-26 tom 2
2011-09-26 kitty 0
2011-09-26 marry 0
2011-09-25 tom 0
2011-09-25 kitty 0
2011-09-25 marry 0
2011-09-24 tom 5
2011-09-24 kitty 1
2011-09-24 marry 0
2011-09-23 tom 1
2011-09-23 kitty 0
2011-09-23 marry 1
------解决方案--------------------
- SQL code
------------------------------ 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 allselect '2011-9-24','kitty',1 union allselect '2011-9-24','tom',2 union allselect '2011-9-24','tom',3 union allselect '2011-9-23','kitty',3 union allselect '2011-9-23','tom',1 union allselect '2011-9-23','marry',1--------------开始查询--------------------------declare @startdate datetime,@enddate datetimeset @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) bwhere datediff(day,dateadd(day,number,@startdate), @enddate)>=0and 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 02011-09-26 marry 02011-09-26 tom 22011-09-25 marry 02011-09-25 tom 02011-09-25 kitty 02011-09-24 marry 02011-09-24 kitty 12011-09-24 tom 22011-09-24 tom 32011-09-23 kitty 32011-09-23 marry 12011-09-23 tom 1(13 行受影响)*/