时间累加问题
数据库中有两个字段
ID             Time
1           00:00:00
2           00:00:00
3           00:00:00
4           00:00:00


如果要把ID为1的Time更改为00:23（23秒）如何做
如果再有一个时间段02:54（两分五四秒），和原来的00:23累加
怎么做？高手帮忙




------解决方案--------------------
create table tb(id int, time varchar(10))

insert into tb values(1, '00:00:23 ')
insert into tb values(2, '00:02:54 ')
insert into tb values(3, '00:00:00 ')
insert into tb values(4, '00:00:00 ')

SELECT * ,
累加=right(convert(varchar(19),dateadd(second,cast(substring(time,4,2) as int) * 60 + cast(right(time,2) as int) , cast((SELECT TOP 1 time FROM TB WHERE id> A.id order by id) as datetime)),120),8)
FROM TB A

drop table tb

/*
id time 累加
----------- ---------- ----------------
1 00:00:23 00:03:17
2 00:02:54 00:02:54
3 00:00:00 00:00:00
4 00:00:00 NULL

（所影响的行数为 4 行）
*/
------解决方案--------------------
CREATE TABLE info
(
ID INT,
Time CHAR(8)
)
GO
INSERT INTO info VALUES(1, '00:00:00 ')
INSERT INTO info VALUES(2, '00:00:00 ')
INSERT INTO info VALUES(3, '00:00:00 ')
INSERT INTO info VALUES(4, '00:00:00 ')
GO
SELECT * FROM info
GO
/*结果
ID Time
----------- --------
1 00:00:00
2 00:00:00
3 00:00:00
4 00:00:00

(4 行受影响)
*/

--如果要把ID为1的Time更改为00:23（23秒）如何做
UPDATE info SET Time = '00:00:23 ' WHERE ID = 1
/*结果
ID Time
----------- --------
1 00:00:23
2 00:00:00
3 00:00:00
4 00:00:00

(4 行受影响)
*/

--如果再有一个时间段02:54（两分五四秒），和原来的00:23累加
CREATE FUNCTION dbo.addTime(@time1 VARCHAR(8), @time2 VARCHAR(8))--参数格式为00:00:00,取值范围00:00:00~23:59:59
RETURNS VARCHAR(8)
AS
BEGIN
DECLARE @time_hh INT,--时
@time_mi INT,--分
@time_ss INT,--秒
@time VARCHAR(8)

--累加时间
SET @time_ss = CAST(RIGHT(@time1, 2) AS INT) + CAST(RIGHT(@time2, 2) AS INT)
SET @time_mi = CAST(RIGHT(LEFT(@time1, 5), 2) AS INT) + CAST(RIGHT(LEFT(@time2, 5), 2) AS INT)
SET @time_hh = CAST(LEFT(@time1, 2) AS INT) + CAST(LEFT(@time2, 2) AS INT)

--将时间修正到正常范围
IF @time_ss > = 60--超过60秒
BEGIN
SET @time_ss = @time_ss - 60
SET @time_mi = @time_mi + 1
END
IF @time_mi > = 60--超过60分
BEGIN
SET @time_mi = @time_mi - 60
SET @time_hh = @time_hh + 1
END
IF @time_hh > = 24--超过24时
BEGIN
SET @time_hh = @time_hh - 24
END

--格式化时间
SET @time = RIGHT( '0 ' + CAST(@time_hh AS VARCHAR), 2) + ': ' +
RIGHT( '0 ' + CAST(@time_mi AS VARCHAR), 2) + ': ' +
RIGHT( '0 ' + CAST(@time_ss AS VARCHAR), 2)

RETURN @time
END

UPDATE info SET Time = dbo.addTime( '00:00:23 ', '00:02:54 ') WHERE ID = 2
/*结果
ID Time
----------- --------
1 00:00:23
2 00:03:17
3 00:00:00
4 00:00:00

(4 行受影响)
*/