关于非固定时间的求和问题

Question

如下数据，时间是非固定的，可能有很多，也可能只有两个000068 汪婉宜 07:44 07:45 08:14 22:14
000069 刘洋 07:48 07:52 07:53 
000070 廖碧香 09:38 09:39 09:42 09:44 09:51 09:52 10:26 10:27 
000072 陈应娟 07:42 07:44 08:18 
000074 杨伏 07:48 
000075 何晨阳 07:42 07:44 08:19 10:42 
000077 罗采治 00:31 00:32 00:34 04:43 04:57 07:25 07:58 08:01 08:06
想要的结果是：  000068 汪婉宜 上班时间为：14时1分钟要求
14小时1分钟是这样算的 [(07:45-07:44)+(22:14-08:14)]。而不是最后一个时间减去第一个时间出来的。
最难搞的是如果只有三个时间的话，就不知道怎么算了。在此向各位高手求救！

Answer 1

上个一样的帖子：http://topic.csdn.net/u/20100919/09/45ef629c-6d5f-4d17-a22c-5d060c90a0f7.html

Answer 2

--> 测试数据：#
if object_id('tempdb.dbo.#') is not null drop table #
create table #(id varchar(8), name varchar(10), times varchar(100))
insert into #
select '000068', '汪婉宜', '07:44 07:45 08:14 22:14' union all
select '000069', '刘洋', '07:48 07:52 07:53' union all
select '000070', '廖碧香', '09:38 09:39 09:42 09:44 09:51 09:52 10:26 10:27' union all
select '000072', '陈应娟', '07:42 07:44 08:18' union all
select '000074', '杨伏', '07:48' union all
select '000075', '何晨阳', '07:42 07:44 08:19 10:42' union all
select '000077', '罗采治', '00:31 00:32 00:34 04:43 04:57 07:25 07:58 08:01 08:06'if object_id('tempdb.dbo.#n') is not null drop table #n
select top 20 n=identity(int,0,1) into #n from syscolumns-- 奇数的时间不计算
select *, [minute]=
(
select sum(datediff(minute, left(tt,5), right(tt,5))) from
(
select substring(times,n*12+1,11)tt from #n where n<=(len(times)-len(replace(times,' ',''))-1)/2
)t
)
from #/*
id       name       times                                                 minute
-------- ---------- ----------------------------------------------------- -----------
000068   汪婉宜     07:44 07:45 08:14 22:14                               841
000069   刘洋       07:48 07:52 07:53                                     4
000070   廖碧香     09:38 09:39 09:42 09:44 09:51 09:52 10:26 10:27       5
000072   陈应娟     07:42 07:44 08:18                                     2
000074   杨伏       07:48                                                 0
000075   何晨阳     07:42 07:44 08:19 10:42                               145
000077   罗采治     00:31 00:32 00:34 04:43 04:57 07:25 07:58 08:01 08:06 401
*/

Answer 3

--此解中　将时间为奇数这样处理：
--当时间只有一个时（如000074 ,'杨伏','07:48'）显示null
--当时间大于为３，５，７...则去掉最后一个时间
use tempdb;
go
if OBJECT_ID('dbo.tb')is not null
drop table dbo.tb
create table dbo.tb
(
  id       int not null,
  name     varchar(10) not null,
  testtime varchar(4000) not null 
);
insert into dbo.tb
select 000068 ,'汪婉宜','07:44 07:45 08:14 22:14' 
union all
select 000069 ,'刘洋','07:48 07:52 07:53' 
union all
select 000070 ,'廖碧香','09:38 09:39 09:42 09:44 09:51 09:52 10:26 10:27'
union all 
select 000072 ,'陈应娟','07:42 07:44 08:18' 
union all 
select 000074 ,'杨伏','07:48' 
union all  
select 000075 ,'何晨阳','07:42 07:44 08:19 10:42'
union all
select 000077 ,'罗采治','00:31 00:32 00:34 04:43 04:57 07:25 07:58 08:01 08:06';go
create function fn_str_time
(@str varchar(4000))
returns int
as
  begin
declare @n int;
with cte as
(
select ROW_NUMBER()over(order by number)as row,
   SUBSTRING(@str,number+1,5)as test_time
from master..spt_values
where type='p'
and SUBSTRING(@str,number,1)=' '
and SUBSTRING(@str,number+1,5)<>' '
)
select @n=sum(datediff(MINUTE,CONVERT(datetime,ct1.test_time,112),CONVERT(datetime,ct2.test_time,112))*
  (case when ct1.row%2=0 then 0 else 1 end))
from cte as ct1
  inner join cte as ct2
   on ct1.row=ct2.row-1
return @n
  end
go
select id,name,
   cast(dbo.fn_str_time(testtime)/60 as varchar(10))+
       '小时'+
       cast(dbo.fn_str_time(testtime)%60 as varchar(10))+
       '分钟'as jg
from dbo.tb/**
id          name       jg
----------- ---------- ----------------------------
68          汪婉宜        14小时1分钟
69          刘洋         0小时4分钟
70          廖碧香        0小时5分钟
72          陈应娟        0小时2分钟
74          杨伏         NULL
75          何晨阳        2小时25分钟
77          罗采治        6小时41分钟(7 行受影响)
**/

Answer 4

怎么我创建函数的时候出错了？
服务器: 消息 156，级别 15，状态 1，过程 fn_str_time，行 8
在关键字 'with' 附近有语法错误。
服务器: 消息 195，级别 15，状态 1，过程 fn_str_time，行 10
'ROW_NUMBER' 不是可以识别的 函数名。

Answer 5

你用的是　sql server 2000?

Answer 6

又检查了一遍　没问题　sql server 2008use tempdb;
go
if OBJECT_ID('dbo.tb')is not null
    drop table dbo.tb
create table dbo.tb
(
  id       int not null,
  name     varchar(10) not null,
  testtime varchar(4000) not null 
);
insert into dbo.tb
select 000068 ,'汪婉宜','07:44 07:45 08:14 22:14' 
union all
select 000069 ,'刘洋','07:48 07:52 07:53' 
union all
select 000070 ,'廖碧香','09:38 09:39 09:42 09:44 09:51 09:52 10:26 10:27'
union all 
select 000072 ,'陈应娟','07:42 07:44 08:18' 
union all 
select 000074 ,'杨伏','07:48' 
union all  
select 000075 ,'何晨阳','07:42 07:44 08:19 10:42'
union all
select 000077 ,'罗采治','00:31 00:32 00:34 04:43 04:57 07:25 07:58 08:01 08:06';go
alter function fn_str_time
(@str varchar(4000))
returns int
as
  begin
        declare @n int;
        with cte as
        (
        select ROW_NUMBER()over(order by number)as row,
               SUBSTRING(@str,number+1,5)as test_time
        from master..spt_values
        where type='p'
        and SUBSTRING(@str,number,1)=' '
        and SUBSTRING(@str,number+1,5)<>' '
        )
        select @n=sum(datediff(MINUTE,CONVERT(datetime,ct1.test_time,112),CONVERT(datetime,ct2.test_time,112)))
        from cte as ct1
          inner join cte as ct2
           on ct1.row=ct2.row-1
        where ct1.row%2<>0
        return @n
  end
go
select id,name,
       cast(dbo.fn_str_time(testtime)/60 as varchar(10))+
       '小时'+
       cast(dbo.fn_str_time(testtime)%60 as varchar(10))+
       '分钟'as jg
from dbo.tb
/**
id          name       jg
----------- ---------- --------------------
68          汪婉宜        14小时1分钟
69          刘洋         0小时4分钟
70          廖碧香        0小时5分钟
72          陈应娟        0小时2分钟
74          杨伏         NULL
75          何晨阳        2小时25分钟
77          罗采治        6小时41分钟(7 行受影响)**/