界面中有一个用于选择“生产部门”的cmbDepartment,一个button,需要根据cmbDepartment.text查询数据记录
共有t1,t2,t3,t4,t5五个表,其中各表字段都有关联,例如:
t1.FDepartment(部门代码) --> t3.FInterID(部门代码) --> t3.FName(生产部门名称)
我的需求是:选择“生产部门名称”,查询出t1里属于该部门的所有记录,
但以下语句执行出的结果却是把T1所有记录的生产部门都显示成cmbDepartment.text的内容了,
应该就是红色部分出问题了,请问正确的该怎么写
(可能描述得不太清楚,在线关注)
先谢谢各位了。
代码如下://按生产部门查询
procedure TQueryForm.btnDepartmentClick(Sender: TObject); var sqlstr: string ;
var finished1 : string ;
begintry
begin
if chbisFinished1.Checked then
finished1 := '(Fstatus = 3)'
else
finished1 := ' (Fstatus = 1)' ; sqlstr := 'SELECT FBillNo AS 生产任务单,t3.FName AS 部门, T4.FNumber AS 物料代码, t5.FModel AS 规格型号, Fqty AS 批量, FcheckDate AS 开单日期, FPlanFinishDate AS 预计入库日期, Sum(工序一) AS 工序一, Sum(工序二) AS 工序二,';
sqlstr :=sqlstr + ' Sum(工序三) AS 工序三, Sum(工序四) AS 工序四,Sum(工序五) AS 工序五,Sum(工序六) AS 工序六,';
sqlstr := sqlstr + ' SUM(入库数量) AS 已入库数量,(t1.Fqty-SUM(t2.入库数量)) AS 待入库数量, MAX(t1.Fstatus) AS 是否结单, t1.Fnote AS 备注,t1.Fmargin AS 今日出货差数 FROM ICMO t1 ';
sqlstr := sqlstr + ' LEFT JOIN T_UpdateTemp t2 ON t1.FBillNo = t2.生产任务单 ';
sqlstr := sqlstr + ' INNER JOIN t_SubMessage t3 ON t3.FName LIKE ''%' + cmbDepartment.text +'%''';
sqlstr := sqlstr + ' LEFT JOIN t_Item t4 ON t1.FItemID = t4.FItemID ';
sqlstr := sqlstr + ' LEFT JOIN t_ICItem t5 ON t1.FItemID = t5.FItemID ';
sqlstr := sqlstr + ' WHERE ' + finished1 + ' GROUP BY FBillNo, t3.FName, T4.FNumber,t5.FModel,Fqty,FcheckDate,FPlanFinishDate,t1.Fnote,Fmargin';
showmessage(sqlstr); datamd.ADOPO_NO.Close ;
datamd.ADOPO_NO.SQL.Clear;
datamd.ADOPO_NO.sql.add(sqlstr);
datamd.ADOPO_NO.open;
statusbar2.Panels[0].Text:='总记录:'+inttostr(datamd.ADOPO_NO.RecordCount ) ; end
EXCEPT
begin
showmessage('操作出错') ;
exit;
end;
end;
end;
共有t1,t2,t3,t4,t5五个表,其中各表字段都有关联,例如:
t1.FDepartment(部门代码) --> t3.FInterID(部门代码) --> t3.FName(生产部门名称)
我的需求是:选择“生产部门名称”,查询出t1里属于该部门的所有记录,
但以下语句执行出的结果却是把T1所有记录的生产部门都显示成cmbDepartment.text的内容了,
应该就是红色部分出问题了,请问正确的该怎么写
(可能描述得不太清楚,在线关注)
先谢谢各位了。
代码如下://按生产部门查询
procedure TQueryForm.btnDepartmentClick(Sender: TObject); var sqlstr: string ;
var finished1 : string ;
begintry
begin
if chbisFinished1.Checked then
finished1 := '(Fstatus = 3)'
else
finished1 := ' (Fstatus = 1)' ; sqlstr := 'SELECT FBillNo AS 生产任务单,t3.FName AS 部门, T4.FNumber AS 物料代码, t5.FModel AS 规格型号, Fqty AS 批量, FcheckDate AS 开单日期, FPlanFinishDate AS 预计入库日期, Sum(工序一) AS 工序一, Sum(工序二) AS 工序二,';
sqlstr :=sqlstr + ' Sum(工序三) AS 工序三, Sum(工序四) AS 工序四,Sum(工序五) AS 工序五,Sum(工序六) AS 工序六,';
sqlstr := sqlstr + ' SUM(入库数量) AS 已入库数量,(t1.Fqty-SUM(t2.入库数量)) AS 待入库数量, MAX(t1.Fstatus) AS 是否结单, t1.Fnote AS 备注,t1.Fmargin AS 今日出货差数 FROM ICMO t1 ';
sqlstr := sqlstr + ' LEFT JOIN T_UpdateTemp t2 ON t1.FBillNo = t2.生产任务单 ';
sqlstr := sqlstr + ' INNER JOIN t_SubMessage t3 ON t3.FName LIKE ''%' + cmbDepartment.text +'%''';
sqlstr := sqlstr + ' LEFT JOIN t_Item t4 ON t1.FItemID = t4.FItemID ';
sqlstr := sqlstr + ' LEFT JOIN t_ICItem t5 ON t1.FItemID = t5.FItemID ';
sqlstr := sqlstr + ' WHERE ' + finished1 + ' GROUP BY FBillNo, t3.FName, T4.FNumber,t5.FModel,Fqty,FcheckDate,FPlanFinishDate,t1.Fnote,Fmargin';
showmessage(sqlstr); datamd.ADOPO_NO.Close ;
datamd.ADOPO_NO.SQL.Clear;
datamd.ADOPO_NO.sql.add(sqlstr);
datamd.ADOPO_NO.open;
statusbar2.Panels[0].Text:='总记录:'+inttostr(datamd.ADOPO_NO.RecordCount ) ; end
EXCEPT
begin
showmessage('操作出错') ;
exit;
end;
end;
end;
解决方案 »
- delphi 收缩控件和隐藏控件的名字
- 高分悬赏--在程序中实现文件上传和下载的功能
- 关于日期时间函数的奇怪问题!
- 数据库操作中的一个问题?
- 串口编程实践问题,读地磅称重量数据
- 有谁对spooling技术或者共享打印机技术比较了解的,请指点小弟一下!100分在线等待!!!
- 我不知道某个操作将要耗多长时间,但我希望用ProgressBar来显示过程,怎么办到??
- 如何循环一个链接?
- 有关DBnavigator的操作!
- 又大了一岁了(^_^)!!呵呵,高兴呀!! 散分了,来着都有份(^_^)!!
- 如何将richedit的内容输出到画布上?
- 大家能帮我解答一下有关delphi7中的第三方控件安装的问题吗?
再加到程序中,有些问题是逻辑问题......
仔细找找吧
执行出的结果却是把T1所有记录的生产部门都显示成cmbDepartment.text的内容了sqlstr := sqlstr + ' INNER JOIN t_SubMessage t3 ON t3.FName LIKE ''%' + cmbDepartment.text +'%''';确实是逻辑问题,我不知道这个限制条件该怎么写,根据T3的字段内容来查询T1记录
sqlstr := sqlstr + ' INNER JOIN t_SubMessage t3 ON t1.FDepartment=t3.FInterID and t3.FName LIKE ''%' + cmbDepartment.text +'%''';
这里应该多加一个条件和t1关联吧
比如
t1.FDepartment(部门代码) --> t3.FInterID(部门代码) --> t3.FName(生产部门名称)
select t1.FDepartment,t3.FinterId,t3.FName from 表1 as t1
left join 表2 as t3 on t3.FInterId=t1.FDepartment
where t3.FName LIKE ''%' + cmbDepartment.text +'%''';关键
where t3.FName LIKE ''%' + cmbDepartment.text +'%''';
INNER JOIN t_SubMessage t3 ON t1.FDepartment=t3.FInterID and t3.FName LIKE ''%' + cmbDepartment.text +'
你的代码:
sqlstr := sqlstr + ' INNER JOIN t_SubMessage t3 ON t3.FName LIKE ''%' + cmbDepartment.text +'%''';
应该修改为:
sqlstr := sqlstr + ' INNER JOIN t_SubMessage t3 ON t3.FName LIKE %'+ quotedstr(
trim(cmbDepartment.text)) +'%';