当前位置: 代码迷 >> Sql Server >> 关于一个 SQL 计算 时间的有关问题
  详细解决方案

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

热度:494   发布时间:2016-04-27 13:41:15.0
关于一个 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:472011-08-01 12:45:082011-08-01 12:48:402011-08-01 12:53:042011-08-01 14:48:172011-08-01 15:46:052011-08-01 15:47:112011-08-01 17:44:382011-08-01 17:49:322011-08-01 18:19:422011-08-01 18:30:422011-08-02 10:32:522011-08-02 12:02:272011-08-02 15:48:382011-08-02 15:52:092011-08-03 10:13:572011-08-03 10:24:502011-08-03 10:35:002011-08-03 11:24:372011-08-03 14:55:302011-08-03 15:22:012011-08-03 17:19:332011-08-03 17:35:082011-08-03 17:39:222011-08-03 17:42:182011-08-03 18:34:142011-08-04 10:42:152011-08-04 11:40:332011-08-04 11:57:202011-08-04 12:08:262011-08-04 15:05:042011-08-04 16:19:072011-08-04 16:38:342011-08-06 17:36:212011-08-06 18:15:152011-08-07 10:43:572011-08-07 10:51:002011-08-07 18:40:532011-08-07 18:45:132011-08-08 10:57:192011-08-08 11:52:082011-08-08 14:47:522011-08-08 17:17:562011-08-08 17:19:342011-08-08 17:35:092011-08-08 17:41:172011-08-08 17:42:232011-08-08 18:00:302011-08-08 18:05:002011-08-08 18:19:092011-08-08 18:30:542011-08-08 18:32:372011-08-09 10:29:252011-08-09 12:10:242011-08-09 12:24:502011-08-09 15:01:162011-08-09 15:13:332011-08-09 15:24:132011-08-09 15:47:512011-08-09 17:28:212011-08-09 18:29:042011-08-09 18:56:352011-08-10 12:05:082011-08-10 13:09:012011-08-10 14:13:572011-08-10 14:38:002011-08-10 16:07:232011-08-10 18:21:382011-08-11 10:36:052011-08-11 11:44:182011-08-11 12:05:132011-08-11 15:18:122011-08-11 17:42:152011-08-11 18:27:532011-08-12 10:41:342011-08-12 11:23:542011-08-12 12:32:312011-08-12 12:36:172011-08-12 12:40:282011-08-12 12:44:192011-08-12 13:00:342011-08-12 13:01:332011-08-12 13:10:492011-08-12 13:18:482011-08-12 14:15:072011-08-12 14:46:422011-08-12 16:04:402011-08-12 16:19:072011-08-12 17:41:282011-08-12 17:42:462011-08-12 17:45:292011-08-12 17:49:062011-08-13 16:22:122011-08-13 17:22:132011-08-13 18:35:522011-08-15 11:00:112011-08-15 11:39:322011-08-15 11:44:242011-08-15 13:14:032011-08-15 13:20:322011-08-15 14:21:572011-08-15 15:12:582011-08-15 15:26:492011-08-15 16:45:172011-08-15 18:54:222011-08-16 11:46:002011-08-16 14:10:432011-08-16 14:57:172011-08-16 15:02:042011-08-16 15:06:522011-08-16 15:07:372011-08-16 15:22:572011-08-16 15:23:432011-08-16 18:33:352011-08-17 11:43:482011-08-17 11:49:482011-08-17 11:58:052011-08-17 12:50:192011-08-17 13:12:342011-08-17 15:22:312011-08-17 15:26:512011-08-17 15:57:482011-08-17 16:01:022011-08-17 16:05:522011-08-17 16:12:102011-08-17 16:18:202011-08-17 16:21:142011-08-17 17:01:432011-08-17 17:08:462011-08-17 17:09:552011-08-17 17:12:502011-08-17 17:14:572011-08-17 17:17:262011-08-17 17:19:482011-08-17 17:29:492011-08-17 17:32:242011-08-17 17:41:222011-08-17 17:41:422011-08-17 17:43:192011-08-17 17:46:252011-08-17 17:57:242011-08-17 18:15:302011-08-17 18:15:582011-08-17 18:25:482011-08-17 18:32:11*/


------解决方案--------------------
SQL code
;with f as(select datediff(mi,a.ftime,b.ftime)  as ftimefrom (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
------解决方案--------------------
首先,不知道你的记录是上班的登录记录,还是休息记录,你给的数据不明确。
其次,不知道你的表的构造,无法帮你写语句解决。
最后,给你一个思路,就是先分组,计算每一日的时间,然后整体相加。
  相关解决方案