query1.close;
query1.SQL.Clear;
query1.SQL.Add('sELECT xm 姓名,pzmc 品种名称,bb 班别,sum(hjcl) 下机产量,sum(zbcl) 折标产量,sum(cl1) 疵布产量,(SUM(hjcl) - SUM(cl1)) AS 一等产量,(SUM(hjcl) - SUM(cl1))/(sum(hjcl)+0.01) as 一等品率 FROM ');
query1.SQL.Add('(SELECT xm,pzmc,bb, SUM(hjcl) hjcl,sum(zbcl) zbcl, 0 cl1,0 ydcl,0 ydpl,0 bz,0 df FROM zyxjk where gbrq>='''+datetostr(rq1.date)+''' and gbrq<='''+datetostr(rq2.date)+''' GROUP BY xm,pzmc,bb');
query1.SQL.Add('UNION SELECT xm,pzmc,bb, 0 hjcl, 0 zbcl,SUM(cl1) cl1,0 ydcl,0 ydpl,0 bz,0 df FROM zycbk where rq>='''+datetostr(rq1.date)+''' and rq<='''+datetostr(rq2.date)+''' GROUP BY xm,pzmc,bb) as tmp GROUP BY xm,pzmc,bb ORDER BY bb' );
query1.Open;
//计算各品种的一等品率
query2.close;
query2.SQL.Clear;
query2.SQL.Add('sELECT pzmc 品种名称,sum(hjcl) 下机产量,sum(cbcl) 疵布产量,(SUM(hjcl) - SUM(cbcl)) AS 一等产量,(SUM(hjcl) - SUM(cbcl))/(sum(hjcl)+0.01) as 平均一等品率 FROM ');
query2.SQL.Add('(SELECT pzmc, SUM(hjcl) hjcl, 0 cbcl,0 ydcl,0 ydpl FROM zyxjk where gbrq>='''+datetostr(rq1.date)+''' and gbrq<='''+datetostr(rq2.date)+''' GROUP BY pzmc');
query2.SQL.Add('UNION SELECT pzmc, 0 hjcl ,SUM(hjcl) cbcl,0 ydcl,0 ydpl FROM zycbk where rq>='''+datetostr(rq1.date)+''' and rq<='''+datetostr(rq2.date)+''' GROUP by pzmc) as tmp GROUP BY pzmc ' );
query2.Open;
end;
query1.SQL.Clear;
query1.SQL.Add('sELECT xm 姓名,pzmc 品种名称,bb 班别,sum(hjcl) 下机产量,sum(zbcl) 折标产量,sum(cl1) 疵布产量,(SUM(hjcl) - SUM(cl1)) AS 一等产量,(SUM(hjcl) - SUM(cl1))/(sum(hjcl)+0.01) as 一等品率 FROM ');
query1.SQL.Add('(SELECT xm,pzmc,bb, SUM(hjcl) hjcl,sum(zbcl) zbcl, 0 cl1,0 ydcl,0 ydpl,0 bz,0 df FROM zyxjk where gbrq>='''+datetostr(rq1.date)+''' and gbrq<='''+datetostr(rq2.date)+''' GROUP BY xm,pzmc,bb');
query1.SQL.Add('UNION SELECT xm,pzmc,bb, 0 hjcl, 0 zbcl,SUM(cl1) cl1,0 ydcl,0 ydpl,0 bz,0 df FROM zycbk where rq>='''+datetostr(rq1.date)+''' and rq<='''+datetostr(rq2.date)+''' GROUP BY xm,pzmc,bb) as tmp GROUP BY xm,pzmc,bb ORDER BY bb' );
query1.Open;
//计算各品种的一等品率
query2.close;
query2.SQL.Clear;
query2.SQL.Add('sELECT pzmc 品种名称,sum(hjcl) 下机产量,sum(cbcl) 疵布产量,(SUM(hjcl) - SUM(cbcl)) AS 一等产量,(SUM(hjcl) - SUM(cbcl))/(sum(hjcl)+0.01) as 平均一等品率 FROM ');
query2.SQL.Add('(SELECT pzmc, SUM(hjcl) hjcl, 0 cbcl,0 ydcl,0 ydpl FROM zyxjk where gbrq>='''+datetostr(rq1.date)+''' and gbrq<='''+datetostr(rq2.date)+''' GROUP BY pzmc');
query2.SQL.Add('UNION SELECT pzmc, 0 hjcl ,SUM(hjcl) cbcl,0 ydcl,0 ydpl FROM zycbk where rq>='''+datetostr(rq1.date)+''' and rq<='''+datetostr(rq2.date)+''' GROUP by pzmc) as tmp GROUP BY pzmc ' );
query2.Open;
end;
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货