我也知道一条SQL是不可能的,我试过很多方法都不好,谁能帮想一个好一点的办法
解决方案 »
- 用ADOQuery写的函数,改用ClientDataSet会报错
- delphi2006的编译问题?
- 新手问题,很简单的
- 一个简单的MIDAS程序,但都有问题,WHY??(急)
- 我写了一个类似于跑马灯的的程序,想让它运行在电脑的最上方,其它所有窗体都排列在此程式下面,请问怎样实现?分不够再加!
- 还是关于Datetime格式输入的问题!
- 请教这句比较简单的SQL语句(只要稍微点拨一下即可),非常感谢!
- 文件操作的一个问题为什么不能删除空目录啊文件都已经可以删除拉啊
- 三层的服务在注册表中如何可以删除干净
- 问个很菜的问题:DELPHI怎么实现文本框内容的自动联想输入?
- 哪位高手知道动态生成表及增加字段?!!!1
- BDE与的ACCESS问题!!应该是小问题,给高分!
set err = 'true'
where card_id in
( select tab1.card_id
from table1 tab1
where tab1.card_id = table1.card_id
and tab1.pdate = table1.pdate
and tab1.ptime+10 > table1.ptime
and tabl1.ptime <> table1.ptime
)
update table1
set err = 'true'
where exists
( select tab1.card_id
from table1 tab1
where tab1.card_id = table1.card_id
and tab1.pdate = table1.pdate
and tab1.ptime+10 > table1.ptime
and tabl1.ptime <> table1.ptime
)
用table控件循环 每次用query查找号码和日期与当前table记录相同的数据集然后query数据集循环 看时间差是多少
while not table.eof
query.sql
while not query.eof
if .....
....
end
end数据量大的话.在想别的办法
set err = 'true'
where exists
( select tab1.card_id
from table1 tab1
where tab1.card_id = table1.card_id
and tab1.pdate = table1.pdate
and table1.ptime - tab1.ptime < 10分钟 (这个条件可以用数据库内置的日期函数来判断)
and tab1.ptime < table1.ptime
)
我在sql server上作了模拟测试,好象还行把pdate, ptime合成一个(small)datetime类型的列 ndate,
然后用下式判断
and datediff(minute,tab1.ndate,table1.ndate) < 10先试试这句SQL, 看看对不对select * from table1
where exists
( select tab1.card_id
from table1 tab1
where tab1.card_id = table1.card_id
and datediff(minute, tab1.ndate, table1.ndate) < 10 (这个条件可以用数据库内置的日期函数来判断)
and tab1.ndate < table1.ndate ) 我测试过,满足gaden007的要求,是否有其它含义?
如果第二条跟第一条相差十分钟之内的(就是第二也是错),那第三条就跟第一条比,
否则第三条跟第二条比,SQL.Add('update TPANA set err = true where exists '+
'( select TPN.CARD_ID from TPANA TPN '+
'where (TPN.card_id = TPANA.card_id) '+
'and (TPN.pdate = TPANA.pdate) '+
'and (TPN.ptime between TPANA.PTIME and TPANA.PTIME+0.0069473) '+
'and (TPN.ptime <> TPANA.ptime) )');
用程序可以吗?
001 01-12-09 12:23:00 false
002 01-12-09 12:30:00 false
003 01-12-09 12:42:00 false
004 01-12-09 12:50:00 false
005 01-12-09 13:10:00 false
006 01-12-09 13:14:00 false
结果是????给我一个例子,让我直观的思考
001 01-12-09 12:23:00 false
002 01-12-09 12:30:00 false
003 01-12-09 12:42:00 false
004 01-12-09 12:50:00 false
005 01-12-09 13:10:00 false
006 01-12-09 13:14:00 false
但如果是这样:
001 01-12-09 12:23:00 false
001 01-12-09 12:30:00 false
001 01-12-09 12:31:00 false
002 01-12-09 12:23:00 false
003 01-12-09 12:42:00 false
004 01-12-09 12:55:00 false
004 01-12-09 13:01:00 false
004 01-12-09 16:25:00 false
004 01-12-09 16:28:00 false
005 01-12-09 13:10:00 false
006 01-12-09 13:14:00 false
得到的结果应该是:
001 01-12-09 12:23:00 false
001 01-12-09 12:30:00 true //号码和日期相同,但时间只相差7分钟
001 01-12-09 12:31:00 true //号码和日期相同,但时间只相差8分钟
002 01-12-09 12:23:00 false
003 01-12-09 12:42:00 false
004 01-12-09 12:55:00 false
004 01-12-09 13:01:00 true //号码和日期相同,但时间只相差6分钟
004 01-12-09 16:25:00 false
004 01-12-09 16:28:00 true //号码和日期相同,但时间只相差3分钟
005 01-12-09 13:10:00 false
006 01-12-09 13:14:00 false
'( select TPN.CARD_ID from TPANA TPN '+
'where (TPN.card_id = TPANA.card_id) '+
'and (TPN.pdate = TPANA.pdate) '+
'and (TPANA.ptime between TPN.PTIME and TPN.PTIME+0.0069473) '+
'and (TPN.ptime < TPANA.ptime) )');注意是
(TPANA.ptime between TPN.PTIME and TPN.PTIME+0.0069473)
~~~~~ ~~~ ~~~和 (TPN.ptime < TPANA.ptime)
~~~
where
EXISTS(
SELECT *
FROM Table1
WHERE CARD_ID = A.CARD_ID AND
PTIME< A.PTIME AND
PDATE = A.PDATE AND
(EXTRACT(HOUR FROM A.PTIME) * 60 +
EXTRACT(MINUTE FROM A.PTIME)) -
(EXTRACT(HOUR FROM PTIME) * 60 +
EXTRACT(MINUTE FROM PTIME)) <=7)
嘻嘻嘻测试成功嘻嘻嘻嘻
嘻嘻嘻嘻嘻嘻嘻嘻嘻嘻嘻/*for ParadoxVII*/
update Table1 AS A set err = True
where
EXISTS(
SELECT *
FROM Table1
WHERE CARD_ID = A.CARD_ID AND
PTIME< A.PTIME AND
PDATE = A.PDATE AND
(EXTRACT(HOUR FROM A.PTIME) * 60 +
EXTRACT(MINUTE FROM A.PTIME)) -
(EXTRACT(HOUR FROM PTIME) * 60 +
EXTRACT(MINUTE FROM PTIME)) <=7)
// ~~~~~
begin
Close;
SQL.Clear;
SQL.Add('update TPANA set err = true' where Exists '+
'( select TPN.CARD_ID from TPANA as TPN '+
'where (TPN.card_id = TPANA.card_id) '+
'and (TPN.pdate = TPANA.pdate) '+
'and ((EXTRACT(hour From TPANA.PTIME)*60 + '+
'EXTRACT(minute From TPANA.PTIME))-'+
'(EXTRACT(hour From TPN.PTIME)*60 +'+
'EXTRACT(minute From TPN.PTIME))<=10))');
ExecSQL;
end;
嘻嘻嘻无语言嘻嘻嘻嘻
嘻嘻嘻嘻嘻嘻嘻嘻嘻嘻procedure TForm1.Button1Click(Sender: TObject);
begin
with QR do try
Close;
SQL.Text :=
'UPDATE TPANA AS A SET ERR = TRUE'#13#10 +
'WHERE EXISTS('#13#10 +
' SELECT *'#13#10 +
' FROM TPANA'#13#10 +
' WHERE CARD_ID = A.CARD_ID AND'#13#10 +
' PTIME< A.PTIME AND'#13#10 +
' PDATE = A.PDATE AND'#13#10 +
' (EXTRACT(HOUR FROM A.PTIME) * 60 +'#13#10 +
' EXTRACT(MINUTE FROM A.PTIME)) -'#13#10 +
' (EXTRACT(HOUR FROM PTIME) * 60 +'#13#10 +
' EXTRACT(MINUTE FROM PTIME)) <=7)'#13#10;
ExecSQL;
except
ShowMessage('我错了');
end;
end;
//把SQL拷贝到Memo1.Text执行看看错不错
//是你自己组装的问题procedure TForm1.Button1Click(Sender: TObject);
begin
with QR do try
Close;
SQL.Text := Memo1.Text;
ExecSQL;
except
ShowMessage('我调试用');
end;
end;