复杂sql 语句，_Sql Server

复杂sql 语句，在线等....
表结构如下:
CREATE TABLE [dbo].[recordInfo](
[id] [int] IDENTITY(1,1) NOT NULL primary key ,
[name] [varchar](20) NOT NULL, --姓名
[recorddate] [varchar](20) NULL, --工作时间
[workhours] [numeric](10, 2) NULL) --工作小时

数据如下：
id name recorddate workhours
1 sa 2012-06-10 8.00
2 sa 2012-06-11 8.00
3 root 2012-06-11 9.00
4 root 2012-06-14 7.00
5 admin 2012-06-15 6.00
6 sa 2012-06-10 3.00

现在需要查询2012-06-10至2012-06-15用户的数据

查询每个人每天的总共工作小时数据，没有默认workhours为0

需要的结果

admin 2012-06-10 0.00
admin 2012-06-11 0.00
admin 2012-06-12 0.00
admin 2012-06-13 0.00
admin 2012-06-14 0.00
admin 2012-06-15 6.00
root 2012-06-10 0.00
root 2012-06-11 9.00
root 2012-06-12 0.00
root 2012-06-13 0.00
root 2012-06-14 7.00
root 2012-06-15 0.00
sa 2012-06-10 11.00
sa 2012-06-11 8.00
sa 2012-06-12 0.00
sa 2012-06-13 0.00
sa 2012-06-14 0.00
sa 2012-06-15 0.00

------解决方案--------------------

SQL code

--> 测试数据：[test]if object_id('[test]') is not null drop table [test]create table [test]([id] int,[name] varchar(5),[recorddate] date,[workhours] numeric(3,2))goinsert [test]select 1,'sa','2012-06-10',8.00 union allselect 2,'sa','2012-06-11',8.00 union allselect 3,'root','2012-06-11',9.00 union allselect 4,'root','2012-06-14',7.00 union allselect 5,'admin','2012-06-15',6.00 union allselect 6,'sa','2012-06-10',3.00godeclare @StartTime dateset @StartTime='2012-06-10';with t as(select distinct b.name,DATEADD(DD,number,@StartTime) as [recorddate]from master..spt_values across join test bwhere number between 0 and 5 and type='p')select t.name,t.recorddate,sum(isnull(m.workhours,0.00)) as workhours from t left join test mon t.recorddate=m.recorddate and t.name=m.namegroup by t.name,t.recorddateorder by t.name,t.recorddate/*name    recorddate    workhours----------------------------------------------admin    2012-06-10    0.00admin    2012-06-11    0.00admin    2012-06-12    0.00admin    2012-06-13    0.00admin    2012-06-14    0.00admin    2012-06-15    6.00root    2012-06-10    0.00root    2012-06-11    9.00root    2012-06-12    0.00root    2012-06-13    0.00root    2012-06-14    7.00root    2012-06-15    0.00sa    2012-06-10    11.00sa    2012-06-11    8.00sa    2012-06-12    0.00sa    2012-06-13    0.00sa    2012-06-14    0.00sa    2012-06-15    0.00*/
------解决方案--------------------
SQL code--> 测试数据：[test]if object_id('[test]') is not null drop table [test]create table [test]([id] int,[name] varchar(5),[recorddate] date,[workhours] numeric(3,2))goinsert [test]select 1,'sa','2012-06-10',8.00 union allselect 2,'sa','2012-06-11',8.00 union allselect 3,'root','2012-06-11',9.00 union allselect 4,'root','2012-06-14',7.00 union allselect 5,'admin','2012-06-15',6.00 union allselect 6,'sa','2012-06-10',3.00godeclare @StartTime dateset @StartTime='2012-06-10'declare @EndTime dateset @EndTime='2012-06-15';with t as(select     distinct b.name,    DATEADD(DD,number,@StartTime) as [recorddate]from     master..spt_values across join     test bwhere     number between 0 and DATEDIFF(DD,@StartTime,@EndTime)     and type='p')select     t.name,    t.recorddate,    sum(isnull(m.workhours,0.00)) as workhours from     t left join     test mon     t.recorddate=m.recorddate     and t.name=m.namegroup by     t.name,t.recorddateorder by     t.name,t.recorddate/*name    recorddate    workhours------------------------------------admin    2012-06-10    0.00admin    2012-06-11    0.00admin    2012-06-12    0.00admin    2012-06-13    0.00admin    2012-06-14    0.00admin    2012-06-15    6.00root    2012-06-10    0.00root    2012-06-11    9.00root    2012-06-12    0.00root    2012-06-13    0.00root    2012-06-14    7.00root    2012-06-15    0.00sa    2012-06-10    11.00sa    2012-06-11    8.00sa    2012-06-12    0.00sa    2012-06-13    0.00sa    2012-06-14    0.00sa    2012-06-15    0.00*/--改一下。还有什么问题？