我写多几个数据吧:
Id1 Id2 date1 date2
1 0 2002-1-1 2002-1-2
2 1 2002-2-2 2002-1-3
3 1 2002-1-20 2002-1-4
4 1 2002-1-3 2002-2-3
5 0 2002-2-1 2002-1-5
6 5 2002-2-2 2002-1-6
7 5 2002-2-3 2002-1-7
变成:
Id1 Id2 date1 date2
1 0 2002-2-3 2002-1-2
2 1 2002-2-3 2002-1-3
3 1 2002-2-3 2002-1-4
4 1 2002-2-3 2002-1-5
5 0 2002-1-7 2002-1-5
6 5 2002-1-7 2002-1-6
7 5 2002-1-7 2002-1-7
Id1 Id2 date1 date2
1 0 2002-1-1 2002-1-2
2 1 2002-2-2 2002-1-3
3 1 2002-1-20 2002-1-4
4 1 2002-1-3 2002-2-3
5 0 2002-2-1 2002-1-5
6 5 2002-2-2 2002-1-6
7 5 2002-2-3 2002-1-7
变成:
Id1 Id2 date1 date2
1 0 2002-2-3 2002-1-2
2 1 2002-2-3 2002-1-3
3 1 2002-2-3 2002-1-4
4 1 2002-2-3 2002-1-5
5 0 2002-1-7 2002-1-5
6 5 2002-1-7 2002-1-6
7 5 2002-1-7 2002-1-7
帮你改成标准SQL应该会快点
TIdate = record
Id:integer;
Date:Tdatetime;
end;
var Idd :array of Tidate;{$R *.DFM}procedure TForm1.Button1Click(Sender: TObject);
var
id,i:integer;
sql :string;
begin
with dst do
begin
close;
CommandText :='select * from tbl where id2=0';
open;
setlength(idd,RecordCount);
i:=0;
While not eof do
begin
id := Fields[0].AsInteger ;
with dst1 do
begin
close;
CommandText := 'select max(date2) from tbl where id1= '+inttostr(id)+' or id2= '+inttostr(id);
open;
idd[i].Id := id;
idd[i].Date := dst1.Fields[0].AsDateTime;
close;
end;
next;
inc(i);
end;
close
end;
for i:=low(Idd) to high(idd) do
begin
dst2.Parameters[0].Value := idd[i].Date;
dst2.Parameters[1].Value := idd[i].Id;
dst2.Parameters[2].Value := idd[i].Id;
dst2.ExecSQL; end;
{ sql := 'update tbl set date1 = '+ formatdatetime('yy-mm-dd',idd[i].date) +
' where id1= '+inttostr(idd[i].id)+' or id2= '+inttostr(idd[i].id);
ADOConnection1.Execute(sql); }
end;
dst2 是 adoquery
sql:
update tbl set date1 =:date
where id1=:id1 or id2=:id2
要指明 参数类型
select a.id1,a.id2,b.date1,a.date2
from a,(select max(date2) date1 from a where id1=1 or id2=1) b;
不过好像ACCESS不支持这种语法,但是不是可以用
select max(date2) into b from a where id1=1 or id2=1
先生成表b,然后再进行处理呢?