日期:2014-05-18  浏览次数:20599 次

关于一个 SQL 计算 时间的问题
SQL code


select ftime from v_log 
where datediff(m,getdate(),ftime)=0 
and fuser=95 
order by ftime

/*  这是一个月的 上班记录, 上班时间早上8:00 到晚上20:00  ,  我想计算 截至 今天  到底 工作了多少小时,就用下面的 数据 算出来  ...........
2011-08-01 12:43:47
2011-08-01 12:45:08
2011-08-01 12:48:40
2011-08-01 12:53:04
2011-08-01 14:48:17
2011-08-01 15:46:05
2011-08-01 15:47:11
2011-08-01 17:44:38
2011-08-01 17:49:32
2011-08-01 18:19:42
2011-08-01 18:30:42
2011-08-02 10:32:52
2011-08-02 12:02:27
2011-08-02 15:48:38
2011-08-02 15:52:09
2011-08-03 10:13:57
2011-08-03 10:24:50
2011-08-03 10:35:00
2011-08-03 11:24:37
2011-08-03 14:55:30
2011-08-03 15:22:01
2011-08-03 17:19:33
2011-08-03 17:35:08
2011-08-03 17:39:22
2011-08-03 17:42:18
2011-08-03 18:34:14
2011-08-04 10:42:15
2011-08-04 11:40:33
2011-08-04 11:57:20
2011-08-04 12:08:26
2011-08-04 15:05:04
2011-08-04 16:19:07
2011-08-04 16:38:34
2011-08-06 17:36:21
2011-08-06 18:15:15
2011-08-07 10:43:57
2011-08-07 10:51:00
2011-08-07 18:40:53
2011-08-07 18:45:13
2011-08-08 10:57:19
2011-08-08 11:52:08
2011-08-08 14:47:52
2011-08-08 17:17:56
2011-08-08 17:19:34
2011-08-08 17:35:09
2011-08-08 17:41:17
2011-08-08 17:42:23
2011-08-08 18:00:30
2011-08-08 18:05:00
2011-08-08 18:19:09
2011-08-08 18:30:54
2011-08-08 18:32:37
2011-08-09 10:29:25
2011-08-09 12:10:24
2011-08-09 12:24:50
2011-08-09 15:01:16
2011-08-09 15:13:33
2011-08-09 15:24:13
2011-08-09 15:47:51
2011-08-09 17:28:21
2011-08-09 18:29:04
2011-08-09 18:56:35
2011-08-10 12:05:08
2011-08-10 13:09:01
2011-08-10 14:13:57
2011-08-10 14:38:00
2011-08-10 16:07:23
2011-08-10 18:21:38
2011-08-11 10:36:05
2011-08-11 11:44:18
2011-08-11 12:05:13
2011-08-11 15:18:12
2011-08-11 17:42:15
2011-08-11 18:27:53
2011-08-12 10:41:34
2011-08-12 11:23:54
2011-08-12 12:32:31
2011-08-12 12:36:17
2011-08-12 12:40:28
2011-08-12 12:44:19
2011-08-12 13:00:34
2011-08-12 13:01:33
2011-08-12 13:10:49
2011-08-12 13:18:48
2011-08-12 14:15:07
2011-08-12 14:46:42
2011-08-12 16:04:40
2011-08-12 16:19:07
2011-08-12 17:41:28
2011-08-12 17:42:46
2011-08-12 17:45:29
2011-08-12 17:49:06
2011-08-13 16:22:12
2011-08-13 17:22:13
2011-08-13 18:35:52
2011-08-15 11:00:11
2011-08-15 11:39:32
2011-08-15 11:44:24
2011-08-15 13:14:03
2011-08-15 13:20:32
2011-08-15 14:21:57
2011-08-15 15:12:58
2011-08-15 15:26:49
2011-08-15 16:45:17
2011-08-15 18:54:22
2011-08-16 11:46:00
2011-08-16 14:10:43
2011-08-16 14:57:17
2011-08-16 15:02:04
2011-08-16 15:06:52
2011-08-16 15:07:37
2011-08-16 15:22:57
2011-08-16 15:23:43
2011-08-16 18:33:35
2011-08-17 11:43:48
2011-08-17 11:49:48
2011-08-17 11:58:05
2011-08-17 12:50:19
2011-08-17 13:12:34
2011-08-17 15:22:31
2011-08-17 15:26:51
2011-08-17 15:57:48
2011-08-17 16:01:02
2011-08-17 16:05:52
2011-08-17 16:12:10
2011-08-17 16:18:20
2011-08-17 16:21:14
2011-08-17 17:01:43
2011-08-17 17:08:46
2011-08-17 17:09:55
2011-08-17 17:12:50
2011-08-17 17:14:57
2011-08-17 17:17:26
2011-08-17 17:19:48
2011-08-17 17:29:49
2011-08-17 17:32:24
2011-08-17 17:41:22
2011-08-17 17:41:42
2011-08-17 17:43:19
2011-08-17 17:46:25
2011-08-17 17:57:24
2011-08-17 18:15:30
2011-08-17 18:15:58
2011-08-17 18:25:48
2011-08-17 18:32:11
*/



------解决方案--------------------
SQL code
;with f as
(
select
 datediff(mi,a.ftime,b.ftime)  as ftime
from
 (select id=row_number()over(order by getdate()),* from v_log) a,
 (select id=row_number()over(order by getdate()),* from v_log) b 
where  
 a.id=b.id-1
)
select sum(ftime) as ftime from f

------解决方案--------------------
首先,不知道你的记录是上班的登录记录,还是休息记录,你给的数据不明确。
其次,不知道你的表的构造,无法帮你写语句解决。
最后,给你一个思路,就是先分组,计算每一日的时间,然后整体相加。