求一个SQL语句？？？？？？？？？？？？？？？？？？？？
表如下：
UserName ProName TaskName StartDate EndDate
张三 项目1 任务1 2007-3-1 2007-3-5
张三 项目1 任务2 2007-3-5 2007-3-8
李四 项目2 任务1 2007-3-5 2007-3-8
用SQL查询出结果如下：
UserName ProName WorkTime
张三 项目1 56
李四 项目2 24
其实就是统计出某个人在某个项目中的总工时数．
工时计算：天数＊８
[解决办法]
select UserName, ProName ,((datediff( dd,StartDate,EndDate))*8) as WorkTime from 表
[解决办法]
select UserName, ProName , Sum(WorkTime) as WorkTime From
(
select UserName, ProName ,((datediff( dd,StartDate,EndDate))*8) as WorkTime from 表
） T
Group By UserName, ProName
[解决办法]
create table #t
(
UserName varchar(50),
ProName varchar(50),
TaskName varchar(50),
StartDate datetime,
EndDate datetime
)
insert #t
select '张三','项目1','任务1','2007-3-1','2007-3-5'
union all
select '张三','项目1','任务2','2007-3-5','2007-3-8'
union all
select '李四','项目2','任务1','2007-3-1','2007-3-5'
select UserName,ProName,sum(datediff(day,StartDate,EndDate))+1 WorkTime
from #t group by UserName,ProName
运行结果:
/*
张三项目18
李四项目25
*/
[解决办法]
select UserName, ProName ,sum(((datediff( dd,StartDate,EndDate))*8)) from 表
group by UserName, ProName
[解决办法]
沙发 的 简单 加一个where 就成了
[解决办法]
SELECT UserName,ProName,sum(WorkTime) AS WorkTime
FROM
(SELECT UserName,ProName,datediff(day,StartDate,EndDate)*8 AS WorkTime
FROM P) P1
GROUP BY UserName,ProName
[解决办法]
select UserName,ProName,sum(datediff(day,StartDate,EndDate))+1 AS WorkTime
from 表 group by UserName,ProName
[解决办法]
select UserName, ProName ,sum(((datediff( dd,StartDate,EndDate))*8)) from 表
group by UserName, ProName
正解！
[解决办法]
都说得很清楚了!!
分多就是爽啊!
我都没有分!
给点分我吧!!!!!!
[解决办法]
create table #t ( UserName varchar(50), ProName varchar(50), TaskName varchar(50), StartDate datetime, EndDate datetime ) insert #t select '张三 ', '项目1 ', '任务1 ', '2007-3-1 ', '2007-3-5 ' union all select '张三 ', '项目1 ', '任务2 ', '2007-3-5 ', '2007-3-8 ' union all select '李四 ', '项目2 ', '任务1 ', '2007-3-5 ', '2007-3-8 ' select username,ProName,(select datediff(dd,min(StartDate),max(a.EndDate)) from #t where username=a.username)*8 WorkTime from #t a group by username,ProName结果：李四 项目2 24张三 项目1 56
[解决办法]
select UserName, ProName ,sum(((datediff( dd,StartDate,EndDate))*8)) from 表
group by UserName, ProName
