日期:2014-05-18 浏览次数:20615 次
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 */
;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
------解决方案--------------------
首先,不知道你的记录是上班的登录记录,还是休息记录,你给的数据不明确。
其次,不知道你的表的构造,无法帮你写语句解决。
最后,给你一个思路,就是先分组,计算每一日的时间,然后整体相加。