我想把所求得的amt(数量)求和,但用我这种方法解决不了,请各位高手帮我解决下下:再下多谢了!
var
sSQL,sSQL1: String; i:integer;
begin
ssql:='';
ssql1:='';
with whdata.kc_Query2 do
begin
close;
SQL.Clear;
SQL.Add('Select whouse from kc_kc2 group by whouse order by whouse');
open;
for i:=1 to whdata.kc_query2.recordcount do
begin
if i<> whdata.kc_query2.recordcount then
sSQL:= sSQL + '(select amt from kc_kc2 where prod_no=g.prod_no and whouse='''+FieldByName('whouse').asstring+''') as '+FieldByName('whouse').asstring+','
else
sSQL:= sSQL + '(select amt from kc_kc2 where prod_no=g.prod_no and whouse='''+FieldByName('whouse').asstring+''') as '+FieldByName('whouse').asstring;
next;
end;
sSQL1:= sSQL1+ '(select sum(amt) from kc_kc2 where prod_no=g.prod_no and whouse='''+FieldByName('whouse').asstring+''') as '+FieldByName('whouse').asstring;
end;
with whdata.kc_query1 do
begin
close;
sql.clear;
sql.add('select DISTINCT (k.prod_no) as 商品编号,old_type as 原型号,new_type as 新型号,'+ssql+','+ssql1+' FROM kc_kc2 k,gg_spbm g where k.prod_no=g.prod_no ORDER BY k.prod_no');
whdata.kc_query1.ExecSQL;
whdata.kc_query1.Active:=true;
var
sSQL,sSQL1: String; i:integer;
begin
ssql:='';
ssql1:='';
with whdata.kc_Query2 do
begin
close;
SQL.Clear;
SQL.Add('Select whouse from kc_kc2 group by whouse order by whouse');
open;
for i:=1 to whdata.kc_query2.recordcount do
begin
if i<> whdata.kc_query2.recordcount then
sSQL:= sSQL + '(select amt from kc_kc2 where prod_no=g.prod_no and whouse='''+FieldByName('whouse').asstring+''') as '+FieldByName('whouse').asstring+','
else
sSQL:= sSQL + '(select amt from kc_kc2 where prod_no=g.prod_no and whouse='''+FieldByName('whouse').asstring+''') as '+FieldByName('whouse').asstring;
next;
end;
sSQL1:= sSQL1+ '(select sum(amt) from kc_kc2 where prod_no=g.prod_no and whouse='''+FieldByName('whouse').asstring+''') as '+FieldByName('whouse').asstring;
end;
with whdata.kc_query1 do
begin
close;
sql.clear;
sql.add('select DISTINCT (k.prod_no) as 商品编号,old_type as 原型号,new_type as 新型号,'+ssql+','+ssql1+' FROM kc_kc2 k,gg_spbm g where k.prod_no=g.prod_no ORDER BY k.prod_no');
whdata.kc_query1.ExecSQL;
whdata.kc_query1.Active:=true;
解决方案 »
- 如何在编辑dataset当前行的数据时,找到需要的其他行,并更改他的数据
- 两个image一个画线,一个显示图片,怎么把它们保存在一个bmp里?
- 问一个关于图片的问题
- 如果获得QUERY中的SQL语句。
- 有人用过ActiveX控件TPicShowEx吗?
- 感觉DBGrid功能太弱,帮忙推荐几个好的免费Grid组件!
- TControl的难题。。。我怎么也想不通。。。怎么办。。。 我的可用分也不多了。。。
- 紧急求救!!DBCtrlGrid中当记录数少于DBCtrlGrid.RowCount时,如何。。。
- 如何写统计的sql语句?
- 可怜虫
- 如何模拟时好时坏的网络环境?
- 高手请进,请教条SQL语句,怎样将table1的数据插到table2中.
何必这么麻烦呢,建一个临时中间表不就轻松搞定吗
union
select sum (field2) as k from table2
应该为sSQL:= sSQL + format('(select amt from kc_kc2 where prod_no=g.prod_no and whouse=''%s'') as %s ,',[FieldByName('whouse').asstring,FieldByName('whouse').asstring])sql.add('select DISTINCT (k.prod_no) as 商品编号,old_type as 原型号,new_type as 新型号,'+ssql+','+ssql1+' FROM kc_kc2 k,gg_spbm g where k.prod_no=g.prod_no ORDER BY k.prod_no');sql.addformat(('select DISTINCT (k.prod_no) as 商品编号,old_type as 原型号,new_type as 新型号,%s %s FROM kc_kc2 k,gg_spbm g where k.prod_no=g.prod_no ORDER BY k.prod_no'),[ssql,ssql1]);
用format语句条理会清晰些。
我主要想实现的是将下面程序中的ssql1字段相加。
for i:=1 to whdata.kc_query2.recordcount do
begin
if i<> whdata.kc_query2.recordcount then
begin
sSQL:= sSQL + '(select amt from kc_kc2 where prod_no=g.prod_no and whouse='''+FieldByName('whouse').asstring+''') as '+FieldByName('whouse').asstring+',';
sSQL1:= sSQL1+ '(select amt from kc_kc2 where prod_no=g.prod_no and whouse='''+FieldByName('whouse').asstring+''')as '+FieldByName('whouse').asstring+'+';
end
else
begin
sSQL:= sSQL + '(select amt from kc_kc2 where prod_no=g.prod_no and whouse='''+FieldByName('whouse').asstring+''') as '+FieldByName('whouse').asstring;
sSQL1:=sSQL1+ '(select amt from kc_kc2 where prod_no=g.prod_no and whouse=trim('''+FieldByName('whouse').asstring+''') as '+FieldByName('whouse').asstring;
end;
next;
end;
end;
在sql语句中用sum(ssql1),请看下面:
with whdata.kc_query1 do
begin
close;
sql.clear;
if edit1.Text='' then
begin
sql.add('select DISTINCT (k.prod_no) as 商品编号,old_type as 原型号,new_type as 新型号,'+ssql+',sum('+ssql1+') as 总库存 FROM kc_kc2 k,gg_spbm g where k.prod_no=g.prod_no group by k.prod_no,old_type,new_type,'+ssql1+' ORDER BY k.prod_no');
whdata.kc_query1.ExecSQL;
whdata.kc_query1.Active:=true;
end
总是提示:,号附近有语句错误或者as附近有语句错误,是不是我的语句写的有错误,请各位高手帮我解决一下吧!问题解决之后,马上给分!
里面有个全角括号!
sSQL:= sSQL + '(select amt from kc_kc2 where prod_no=g.prod_no and whouse='''+FieldByName('whouse').asstring+''') as '+FieldByName('whouse').asstring;
这句后里没加‘,’
我主要是想实现库存数量汇总,不是全角括号的错误,那可能是我刚才的误操作。
那应该怎么写呀!谢了!
with whdata.kc_query1 do
begin
close;
sql.clear;
if edit1.Text='' then
begin
sql.add('select DISTINCT (k.prod_no) as 商品编号,old_type as 原型号,new_type as 新型号,'+ssql+',sum('+ssql1+') as 总库存 FROM kc_kc2 k,gg_spbm g where k.prod_no=g.prod_no group by k.prod_no,old_type,new_type,'+ssql1+' ORDER BY k.prod_no');
whdata.kc_query1.ExecSQL;
whdata.kc_query1.Active:=true;
end
首先应该说:whdata.kc_quer1.execsql//改成open;
另外你的程序实在太长,可以在form上放一个memo控件,然后在
whdata.kc_query1.open前面加上如下语句:
memo1.lines.clear;
memo1.lines.add('select DISTINCT (k.prod_no) as 商品编号,old_type as 原型号,new_type as 新型号,'+ssql+',sum('+ssql1+') as 总库存 FROM kc_kc2 k,gg_spbm g where k.prod_no=g.prod_no group by k.prod_no,old_type,new_type,'+ssql1+' ORDER BY k.prod_no');
如此一来你要的Sql语句不就在memo中有显示,再看看memo中的语句,这样找错误应该方便很多了呀!
strSql : String;
strSql :='??????';
sql.add(StrSq);
只要你的Sql语言没有问题,这样很好解决问题,同时注意
with query do
close;
sql.clear;
sql.add(strSql);
open;
first; //注意这里,如果没有,可能会少计算一些数据的
end;
close;
SQL.Clear;
SQL.Add('Select whouse from kc_kc2 group by whouse order by whouse');
open;
for i:=1 to whdata.kc_query2.recordcount do
begin
if i<> whdata.kc_query2.recordcount then
begin
sSQL:= sSQL + '(select amt from kc_kc2 where prod_no=g.prod_no and whouse='''+FieldByName('whouse').asstring+''') as '+FieldByName('whouse').asstring+',';
sSQL1:= sSQL1+ '(select amt from kc_kc2 where prod_no=g.prod_no and whouse='''+FieldByName('whouse').asstring+''')as '+FieldByName('whouse').asstring+'+';
end
else
begin
sSQL:= sSQL + '(select amt from kc_kc2 where prod_no=g.prod_no and whouse='''+FieldByName('whouse').asstring+''') as '+FieldByName('whouse').asstring;
sSQL1:=sSQL1+ '(select amt from kc_kc2 where prod_no=g.prod_no and whouse='''+FieldByName('whouse').asstring+''') as '+FieldByName('whouse').asstring;
end;
next;
sSQL1:='(select sum('+ssql1+') from kc_kc2 where prod_no=g.prod_no group by prod_no)';
end;
end;
现在的问题就剩下两个了:
1、我用select查出来的结果作为另一个select查询的字段,而最后是想把上面代码中的用第一个select查出来的字段累加起来求和。
2、在我用求和语句sum('+ssql1+')时,把变量作为字段名,这么写是否不对?具体应该怎么写?
还望高手多加指点!
'select DISTINCT (k.prod_no) as 商品编号,old_type as 原型号,new_type as 新型号,'+ssql+',sum('+ssql1+') as 总库存 FROM kc_kc2 k,gg_spbm g where k.prod_no=g.prod_no group by k.prod_no,old_type,new_type,'+ssql1+' ORDER BY k.prod_no'
//这里怎么是ssql1呢, 应该是ssql吧
这么长叫我怎么看!!!???
ssql也不行,因为是这样的格式sum(字段1,字段2,字段3,...),这样不可能求和的。ssql1应该是这样的:sum(字段1+字段2+字段3+...),但不管怎么总是有错误出现。
这是正确的源程序:
procedure Tfrm_tkccx.BitBtn1Click(Sender: TObject);
var
sSQL: String; i:integer;
begin
ssql:='';
with whdata.kc_Query2 do
begin
close;
SQL.Clear;
SQL.Add('Select whouse from kc_kc2 group by whouse order by whouse');
open;
for i:=1 to whdata.kc_query2.recordcount do
begin
if i<> whdata.kc_query2.recordcount then
sSQL:= sSQL + '(select amt from kc_kc2 where prod_no=g.prod_no and whouse='''+FieldByName('whouse').asstring+''') as '+FieldByName('whouse').asstring+','
else
sSQL:= sSQL + '(select amt from kc_kc2 where prod_no=g.prod_no and whouse='''+FieldByName('whouse').asstring+''') as '+FieldByName('whouse').asstring;
next;
end;
end;
with whdata.kc_query1 do
begin
close;
sql.clear;
if edit1.Text='' then
begin
sql.add('select DISTINCT (k.prod_no) as 商品编号,old_type as 原型号,new_type as 新型号,'+ssql+' FROM kc_kc2 k,gg_spbm g where k.prod_no=g.prod_no ORDER BY k.prod_no');
whdata.kc_query1.ExecSQL;
whdata.kc_query1.Active:=true;
end
else if RadioButton1.Checked=true then
begin
sql.add('select DISTINCT (k.prod_no) as 商品编号,old_type as 原型号,new_type as 新型号,'+ssql+' FROM kc_kc2 k,gg_spbm g where k.prod_no=g.prod_no and old_type like '''+edit1.Text+'%'+''' ORDER BY k.prod_no');
whdata.kc_query1.ExecSQL;
whdata.kc_query1.Active:=true;
end
else
sql.add('select DISTINCT (k.prod_no) as 商品编号,old_type as 原型号,new_type as 新型号,'+ssql+' FROM kc_kc2 k,gg_spbm g where k.prod_no=g.prod_no and new_type like '''+edit1.Text+'%'+''' ORDER BY k.prod_no');
whdata.kc_query1.ExecSQL;
whdata.kc_query1.Active:=true;
end;
end;
把你的这句
'select DISTINCT (k.prod_no) as 商品编号,old_type as 原型号,new_type as 新型号,'+ssql+' FROM kc_kc2 k,gg_spbm g where k.prod_no=g.prod_no and new_type like '''+edit1.Text+'%'+''' ORDER BY k.prod_no'
改为
'select DISTINCT (k.prod_no) as 商品编号,old_type as 原型号,new_type as 新型号,'+ssql+' ,(SELECT SUM(k.amt) FROM kc_kc2 k WHERE k.prod_no = g.prod_no) AS 总计 FROM kc_kc2 k,gg_spbm g where k.prod_no=g.prod_no and new_type like '''+edit1.Text+'%'+''' ORDER BY k.prod_no'