如何用SQL语句删除空记录(200分)

D

Danseuse

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-04-01

• #1

数据库DB.dbf的字段有：A1，A2，A3，A4，A5，A6

主要元件有DataSource1，DBGrid1，Query1，Query2，Button1

元件Query1用于查询记录

元件Query2用于删除记录，SQL语句为：
Delete From DB.dbf Where A1 = :A1 and A2 = :A2 and A3 = :A3

DataSource1.DateSet = Query1

DBGrid1.DataSource = DataSource1

按钮Button1用于删除记录
procedure TForm1.Button1Click(Sender: TObject);
begin
with Query2 do
begin
ParamByName('A1').Value := Query1.FieldValues['A1'];
ParamByName('A2').Value := Query1.FieldValues['A2'];
ParamByName('A3').Value := Query1.FieldValues['A3'];
ExecSQL;
end;
end;

当前记录的A1、A2、A3都不为空时，按按钮Button1时，能删除当前记录；
当前记录的A1或A2或A3为空时，按按钮Button1时，不能删除当前记录，
这时该如何用SQL语句删除当前记录？

 

C

cheka

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-04-01

• #2

if A1!='') and A2!='')andA3!='')
Delete From DB.dbf Where A1 = :A1 and A2 = :A2 and A3 = :A3

 

L

Lambert

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-04-01

• #3

问：不知字段A1、A2、A3的数据类型是否有限制？

 

C

cheka

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-04-01

• #4

理解有些错误，你是希望A1,A2,A3 即使为空也能删除纪录？

 

D

Danseuse

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-04-01

• #5

To Lambert:
A1和A2为字符型
A3为日期型

To cheka:
A1、A2、A3即使为空也能删除纪录

 

L

Li zhaoyang

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-04-01

• #6

加一个判断
每次先判断其值是否为空，
如是，则执行
Delete From DB.dbf Where A1 is null and A2 is null and A3 is null
不是则执行原来的。

 

L

lhxu

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-04-01

• #7

如Li zhaoyang 说
把为空的情况判断掉,单独处理

 

C

CJ

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-04-01

• #8

if Query1.FieldValues['A1'] <> '' then
ParamByName('A1').Value := Query1.FieldValues['A1']
else
ParamByName('A1').Value := nil;
2/3 too

 

C

chimney

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-04-02

• #9

同意Li zhaoyang

 

M

Mutex

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-04-03

• #10

将Sql改成：
Delete From DB.dbf
Where
((A1 = :A1) or (A1 is Null)) and
((A2 = :A2) or (A2 is Null)) and
((A3 = :A3) or (A3 is Null))

然后
for i:=0 to Params.Count-1 do Params:=nil;
if Query1.FieldValues['A1'] <> '' then
ParamByName('A1').Value := Query1.FieldValues['A1'];
if Query1.FieldValues['A2'] <> '' then
ParamByName('A2').Value := Query1.FieldValues['A2'];
if Query1.FieldValues['A3'] <> '' then
ParamByName('A3').Value := Query1.FieldValues['A3'];

 

L

liuchuanbo

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-04-04

• #11

F7 调试：
if A1!='') and A2!='')andA3!='') then
Delete From DB.dbf Where A1 = :A1 and A2 = :A2 and A3 = :A3
若空仍执行 delete...
则是判断语句的问题

 

千

千中元

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-04-04

• #12

lizhaoyang,
>> 当前记录的A1或A2或A3为空时，按按钮Button1时，不能删除当前记录
----是”或”

 

W

wjiachun

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-09-09

• #13

多人接受答案了。