我想用下面的语句得到SQL的对应数据并求平均值,但是不知Edit1.Text+'=LP_TYPE.TYP_DESC,这段哪里出错了,就是不能运行。我输入条件“A59809C00HM"去查找对应的数据,但是报错”Incorrect syntax near 'A59809C00HM'. 求专家帮忙看看我哪里写错了,还是不能用Tedit做输入条件?
谢谢
procedure TFrmMain.SpeedButton2Click(Sender: TObject);
var
StartDate,
EndDate:string;
SQLStr:string;begin
StartDate := DateToStr(DateTimePicker1.Date)+' 00:00:00';
EndDate := DateToStr(DateTimePicker2.Date)+' 23:59:59';
SQLStr :='SELECT CONVERT(VARCHAR(30), VPE.DATUMUHRZEIT,111) AS DATE, LP_TYPE.TYP_DESC as type,AVG(LP_DATA.lichtstrom) AS AVG_LUMEN '+
' FROM LP_DATA INNER JOIN VPE ON LP_DATA.ID_VPE=VPE.ID_VPE '+
' INNER JOIN LP_TYPE ON LP_DATA.ID_TYP=LP_TYPE.ID_TYP '+
' WHERE VPE.DATUMUHRZEIT BETWEEN '+QuotedStr(StartDate)+
' and '+ QuotedStr(EndDate) + Edit1.Text+'=LP_TYPE.TYP_DESC,'+
' CONVERT(VARCHAR(30),VPE.DATUMUHRZEIT,111)'; Memo1.Lines.Add(SQLStr); ADODataSet1.Active := False;
ADODataSet1.CommandText := SQLStr;
ADODataSet1.Active := True;
end;
谢谢
procedure TFrmMain.SpeedButton2Click(Sender: TObject);
var
StartDate,
EndDate:string;
SQLStr:string;begin
StartDate := DateToStr(DateTimePicker1.Date)+' 00:00:00';
EndDate := DateToStr(DateTimePicker2.Date)+' 23:59:59';
SQLStr :='SELECT CONVERT(VARCHAR(30), VPE.DATUMUHRZEIT,111) AS DATE, LP_TYPE.TYP_DESC as type,AVG(LP_DATA.lichtstrom) AS AVG_LUMEN '+
' FROM LP_DATA INNER JOIN VPE ON LP_DATA.ID_VPE=VPE.ID_VPE '+
' INNER JOIN LP_TYPE ON LP_DATA.ID_TYP=LP_TYPE.ID_TYP '+
' WHERE VPE.DATUMUHRZEIT BETWEEN '+QuotedStr(StartDate)+
' and '+ QuotedStr(EndDate) + Edit1.Text+'=LP_TYPE.TYP_DESC,'+
' CONVERT(VARCHAR(30),VPE.DATUMUHRZEIT,111)'; Memo1.Lines.Add(SQLStr); ADODataSet1.Active := False;
ADODataSet1.CommandText := SQLStr;
ADODataSet1.Active := True;
end;
ADODataSet1.Active := False;
ADODataSet1.CommandText := SQLStr;
showmessage(SQLStr);
ADODataSet1.Active := True;
如果我的sql语句出现问题,我都是这么找毛病的。
有message 导出的语句看'A59809C00HM 少一半冒号。 不知这要怎么纠正?
Memo1
SELECT CONVERT(VARCHAR(30), VPE.DATUMUHRZEIT,111) AS DATE, LP_TYPE.TYP_DESC as type,AVG(LP_DATA.lichtstrom) AS AVG_LUMEN FROM LP_DATA INNER JOIN VPE ON LP_DATA.ID_VPE=VPE.ID_VPE INNER JOIN LP_TYPE ON LP_DATA.ID_TYP=LP_TYPE.ID_TYP WHERE VPE.DATUMUHRZEIT BETWEEN '2013/1/1 00:00:00' and '2013/1/1 23:59:59''A59809C00HM=LP_TYPE.TYP_DESC
1、 字段=查询条件,你这个 'A59809C00HM' 本应是是作为查询条件,却跑到 = 前面去了,应该是: LP_TYPE.TYP_DESC='A59809C00HM';
2、 where 后面应该是两个条件,两个条件最好各自括起来,再and连接 ,即 Where (条件1) and (条件2)
你试一下这个:
SQLStr :='SELECT CONVERT(VARCHAR(30), VPE.DATUMUHRZEIT,111) AS DATE, LP_TYPE.TYP_DESC as type,AVG(LP_DATA.lichtstrom) AS AVG_LUMEN '+
' FROM LP_DATA INNER JOIN VPE ON LP_DATA.ID_VPE=VPE.ID_VPE '+
' INNER JOIN LP_TYPE ON LP_DATA.ID_TYP=LP_TYPE.ID_TYP '+
' WHERE (VPE.DATUMUHRZEIT BETWEEN '+QuotedStr(StartDate)+
' and '+ QuotedStr(EndDate) +') and ('+'LP_TYPE.TYP_DESC= '''+Edit1.Text+''')';
//你最后这句是干嘛用的? ' CONVERT(VARCHAR(30),VPE.DATUMUHRZEIT,111)';