字段 songtime jmtime1 2008-9-13 21:07:00 00:02:00
2 2008-9-13 21:09:00 00:03:30
3 2008-9-13 21:12:30 00:04:00
4 2008-9-13 21:16:30 00:05:00
.
.
.
N-1 songtime(N-1) jmtime(N-1)
N songtime(N-1)+jmtime(N-1) jmtime(N) //下条记录的songtime值为上条记录的songtime值加上jmtime值问题:如何代码实现 songtime(N)=songtime(N-1)+jmtime(N-1)
2 2008-9-13 21:09:00 00:03:30
3 2008-9-13 21:12:30 00:04:00
4 2008-9-13 21:16:30 00:05:00
.
.
.
N-1 songtime(N-1) jmtime(N-1)
N songtime(N-1)+jmtime(N-1) jmtime(N) //下条记录的songtime值为上条记录的songtime值加上jmtime值问题:如何代码实现 songtime(N)=songtime(N-1)+jmtime(N-1)
-------------------------------------
字段是文本类型的,我累加运算时把它转化为DATETIME格式在计算就是在有记录变化时(比如删除某条记录后)程序可以自动刷新数据并自动累加上述的SONGTIEM值
有没有这样的表控件,可以用鼠标拖动改变记录在表中的位置,就是可以鼠标拖动来交换记录位置?DBGRID没这样功能
像这样的需求,不需要去更新表,建一个视图就搞定啦还要其他用途都可以直接从视图里改
--建立视图
create view v_table1_newtime as
select songtime,jmtime,songtime + convert(datetime,jmtime) as newtime from table1
goselect * from v_table1_newtimesongtime jmtime newtime
2008-9-13 21:07:00 00:02:00 2008-09-13 21:09:00.000
2008-9-13 21:09:00 00:03:30 2008-09-13 21:12:30.000
2008-9-13 21:12:30 00:04:00 2008-09-13 21:16:30.000
2008-9-13 21:16:30 00:05:00 2008-09-13 21:21:30.000
as
begin tran
declare @tid int
declare @nextSongTime varchar(20)
declare @jmtime varchar(10)
select @tid=tid, @nextSongTime=songtime from deleteddeclare loop_cursor cursor for select tid, jmtime from table1 where tid>@tid order by tidopen loop_cursorfetch next from loop_cursor into @tid, @jmtimewhile @@fetch_status=0
begin
update table1 set songtime= @nextSongTime where tid=@tid
set @nextSongTime = convert(varchar(20), @nextSongTime + Convert(datetime, @jmtime) ,121)
fetch next from loop_cursor into @tid, @jmtime
endclose loop_cursor
deallocate loop_cursor commit tran
你也许要把121改成120
set @nextSongTime = convert(varchar(20), @nextSongTime + Convert(datetime, @jmtime) ,120)新增记录应该比较简单了.结贴谢谢:-)
declare @d datetime
set @d='2008-9-13 21:07:00 '
select @d+'00:02:00'
==================
我忘记说明,我用的数据库是ACCESS,你11楼的是不是针对SQL写的语句?如果是ACCESS数据库该怎么写?数据库中表名是ZF,,,谢谢帮忙再看下,答案出来马上给分!
谢谢了!!!
再次多谢,谢谢帮忙顶的兄弟们!
用DBGRIDEH 等控件,有这功能的。
=============
我用的是DBGRID,想要的效果是:
songtime(N)=songtime(N-1)+jmtime(N-1)
//下条记录的songtime值为上条记录的songtime值加上jmtime值
access不能用触发器不能在数据库里做,只有在程序里做更新了.
procedure TForm1.Button1Click(Sender: TObject);
var
newsongtime:Tdatetime; {用于保存新的songtime的临时变量}
begin
if not self.ADOQuery1.Active then
self.ADOQuery1.Active:=true;
self.ADOQuery1.Next; {比如我删除的是第二条}
self.ADOConnection1.BeginTrans; {这种情况应该用事务}
try
{被删除的songtime应该更新到下一条,所以保留}
newsongtime := self.ADOQuery1.FieldByName['songtime'].AsDateTime;
self.ADOQuery1.Delete; {删除} while not self.ADOQuery1.Eof do begin {循环对后面的每一条记录都进行更新}
self.ADOQuery1.Edit;
self.ADOQuery1.Fields[1].AsDateTime := newsongtime; {通过加jmtime得到下一条记录的songtime的值}
newsongtime := newsongtime +self.ADOQuery1.FieldbyName['jmtime'].AsDateTime;
self.ADOQuery1.Post;
self.ADOQuery1.Next;
end;
self.ADOConnection1.CommitTrans;
except
self.ADOConnection1.RollbackTrans;
end;
end;