复杂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
1sa2012-06-108.00
2sa2012-06-118.00
3root2012-06-119.00
4root2012-06-147.00
5admin2012-06-156.00
6sa2012-06-103.00
现在需要查询2012-06-10至2012-06-15用户的数据
查询每个人每天的总共工作小时数据，没有默认workhours为0
需要的结果
admin2012-06-100.00
admin2012-06-110.00
admin2012-06-120.00
admin2012-06-130.00
admin2012-06-140.00
admin2012-06-156.00
root2012-06-100.00
root2012-06-119.00
root2012-06-120.00
root2012-06-130.00
root2012-06-147.00
root2012-06-150.00
sa2012-06-1011.00
sa2012-06-118.00
sa2012-06-120.00
sa2012-06-130.00
sa2012-06-140.00
sa2012-06-150.00
[解决办法]
--> 测试数据：[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*/
[解决办法]
--> 测试数据：[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*/--改一下。还有什么问题？