界面中有一个用于选择“生产部门”的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;
解决方案 »
- 请问ServerSocket如何给指定客户发送数据
- 晕了,要问了,我程序里调用一个CMD模式下的程序叫DFC.EXE,他有一个返回值,怎么在我的程序里接收这个返回值呢?
- 请各位大侠指点一下!
- 怎样实现任何数据类型和字符串类型之间的互相转换?up有分!!!!
- 如何利用 TApplicationEvents 来捕捉线程中的 Exception ?
- Dbexpress执行MSSQL存储过程问题,高分相送,UP有分
- 搞不清treeview1.Items.AddObject与treeview1.Items.Add的区别?
- 如何实现??想在Form1的DBGIRD里,当双击某一行数据时(Options.dgrowselect=True),会自动弹出一个Form2来,Form2里有几个Edit,依次显
- 这样写修改一条记录错在那里
- delphi 实现指定网站 搜索整站中指定的关键字(100分)
- 如何将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)) +'%';