求教数据查询语句(100分)

J

jbas

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-01-19

• #1

表的结构如下：paradox数据库
fieldname type size key
lycs N
lytime A 20
lynr A 40

kk1:=datetostr(now);
oldss:='asdf';
diarydata.tx.SQL.add('insert into ly(lycs,lytime,lynr) select (max(lycs)+1),'''+kk1+''', '''+oldss+''' from ly');
这句sql语句可以用来执行将当前数据表中添加一条记录，lytime是当前时间。lynr是字符型。lycs是把以前记录的最大值+1来取代
这句sql执行成功。但我想要把lycs用当前记录的最大值+1替换还要一个where lytime=kk1的限制，不知怎么用。请指教！
我这样总是错误，在执行时出错，编译通过！
diarydata.tx.SQL.add('insert into ly(lycs,lytime,lynr) select (max(lycs)+1) where lytime=kk1,'''+kk1+''', '''+oldss+''' from ly');

 

轩

轩辕散光

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-01-19

• #2

diarydata.tx.SQL.add('insert into ly(lycs,lytime,lynr) select (max(lycs)+1) where lytime='''+kk1+''','''+kk1+''', '''+oldss+''' from ly');

 

Y

yhaochuan

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-01-19

• #3

sql.add('insert into ly(lycs,lytime,lynr) select (max(lycs)+1) where lytime='''+kk1+''''+','''+kk2+''', '''+oldss+''' from ly')

 

J

jbas

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-01-19

• #4

to 轩辕散光, 好象不行，出现invalid use of keyword token where 错误！
再提点建议吧，我再试试！

 

Y

yhaochuan

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-01-19

• #5

kk1也要注意日期格式。

 

J

jbas

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-01-19

• #6

to yhaochuan, 大侠，也不行出现错误跟“轩辕散光”出现的错误一样！
lytime改成了字符型了！
kk1:=datetostr(now);
oldss:='asd';
diarydata.tx.Close;
diarydata.tx.SQL.Clear;
diarydata.tx.sql.add('insert into ly(lycs,lytime,lynr) select (max(lycs)+1) where lytime='''+kk1+''''+','''+kk1+''', '''+oldss+''' from ly');
diarydata.tx.Prepare;
diarydata.tx.ExecSQL;

 

Y

yhaochuan

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-01-19

• #7

把SQL用showmessage出来看看，对不对。
'insert into ly(lycs,lytime,lynr) select (max(lycs)+1) where lytime='''+kk1+''''+','''+kk2+''', '''+oldss+''' from ly'
我的showmessage出来是：kk1:='kk1'.....
insert into ly(lycs,lytime,lynr) select (max(lycs)+1) where lytime='kk1','kk2', 'oldss' from ly
再把此句放到sql explorer试试就知道问题所在了。

 

轩

轩辕散光

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-01-19

• #8

diarydata.tx.sql.add('insert into ly(lycs,lytime,lynr) select (max(lycs)+1) ,'''+kk1+''', '''+oldss+''' from ly where lytime='''+kk1+'''');
肯定行了

 

Y

yhaochuan

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-01-19

• #9

是的是的，要把where条件放在最后面：）

 

J

jbas

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-01-19

• #10

diarydata.tx.SQL.SaveToFile('d:/debug.txt');
不执行execsql的结果，执行exesql就出现错误。
debug.txt内容:
insert into ly(lycs,lytime,lynr) select (max(lycs)+1) where lytime='2002-1-19','2002-1-19', 'D:/delphi/zy1/music/2002-1-19.1.wav' from ly
换成这样就编译不通过了！
diarydata.tx.SQL.Add('insert into ly(lycs,lytime,lynr) select (max(lycs)+1) where lytime='kk1','kk1','oldss' from ly');

 

Y

yhaochuan

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-01-19

• #11

my sql string:
diarydata.tx.SQL.Add('insert into ly(lycs,lytime,lynr) select (max(lycs)+1) ,''' + kk2 + ''', '''+oldss+''' from ly where lytime=''' + kk1+'''')

 

Y

yhaochuan

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-01-19

• #12

insert into ly(lycs,lytime,lynr) select (max(lycs)+1) where lytime='2002-1-19','2002-1-19', 'D:/delphi/zy1/music/2002-1-19.1.wav' from ly
是错误的，
要这样才对：
insert into ly(lycs,lytime,lynr) select (max(lycs)+1) ,'2002-1-19', 'D:/delphi/zy1/music/2002-1-19.1.wav' from ly where lytime='2002-1-19'
即：where语句要放在from ly的后面。

 

J

jbas

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-01-19

• #13

谢谢yhaochuan大侠，轩辕散光大侠，我都不知道怎样感谢你们了。我这是个笔记本软件，弄完后公布源代码的，真的非常
感谢两位高手，谢谢了！
测试通过！
diarydata.tx.Close;
diarydata.tx.SQL.Clear;
diarydata.tx.sql.add('insert into ly(lycs,lytime,lynr) select (max(lycs)+1) ,'''+kk1+''', '''+oldss+''' from ly where lytime='''+kk1+'''');
try
diarydata.tx.Prepare;
diarydata.tx.ExecSQL;

 

J

jbas

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-01-19

• #14

谢谢了！