当前位置: 代码迷 >> ASP.NET >> SQL语句?
  详细解决方案

SQL语句?

热度:859   发布时间:2013-02-25 00:00:00.0
求一个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

其实就是统计出某个人在某个项目中的总工时数.
工时计算:天数*8

------解决方案--------------------------------------------------------
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
运行结果:
/*
张三 项目1 8
李四 项目2 5

*/
------解决方案--------------------------------------------------------
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

正解!
------解决方案--------------------------------------------------------
都说得很清楚了!!



分多就是爽啊!
我都没有分!

给点分我吧!!!!!!
------解决方案--------------------------------------------------------
SQL code
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 
  相关解决方案