如何把从多个表查询得到的数据插入临时表中

代码：
SELECT fpfldmb.mc,
         jzje = sum(isnull(fljsb.jzje, 0)),
         zfje = sum(fljsb.je - isnull(fljsb.jzje, 0)),
         je = sum(fljsb.je),
         fpfldmb.fldm
    FROM fljsb,
         jc_fpb,
         fpfldmb,
         jsb
   WHERE ( jc_fpb.jcfldm = fljsb.fldm ) and
         ( fpfldmb.fldm = jc_fpb.fpfldm ) and
         ( fljsb.fph = jsb.fph ) and
         ( ( jsb.czydm = :czydm ) and
  ( jsb.blrq between :kssj and :jssj ) and
  ( jsb.zfbz = 'F' )
         )
GROUP BY fpfldmb.fldm,
         fpfldmb.mc
ORDER BY fpfldmb.fldm ASC

在sql下没有问题，有数据

6 个解决方案

#1

我是这样写的，但只插入了一条数据，共有30条数据

string ls_mc,ls_fldm
dec d_zfje,d_je

connect using sqlca;
//
SELECT fpfldmb.mc,
         //jzje = sum(isnull(fljsb.jzje, 0)),
         zfje = sum(fljsb.je - isnull(fljsb.jzje, 0)),
         je = sum(fljsb.je),
         fpfldmb.fldm
into :ls_mc,:d_zfje,:d_je,:ls_fldm
    FROM fljsb,
         jc_fpb,
         fpfldmb,
         jsb
   WHERE ( jc_fpb.jcfldm = fljsb.fldm ) and
         ( fpfldmb.fldm = jc_fpb.fpfldm ) and
         ( fljsb.fph = jsb.fph ) and
         ( ( jsb.czydm = '081' ) and
  ( jsb.blrq between '2009.10.04 17:13:57' and '2009.10.08 16:40:42' ) and
  ( jsb.zfbz = 'F' )
         )
GROUP BY fpfldmb.fldm,
         fpfldmb.mc;

connect using isrc_netupp2;
insert into jkbb (mc, zfje,je,fldm)
   values(:ls_mc,:d_zfje,:d_je,:ls_fldm) using isrc_netupp2;

#2

http://topic.csdn.net/u/20091126/20/d5577e1e-0e0c-45bb-8e67-d4fdd55609ea.html?51476

#3

大致做法是:

STRING  Is_Sql , Is_Syntax , Is_Error

Is_sql = "" + &
"SELECT fpfldmb.mc,  " + &
"        jzje = sum(isnull(fljsb.jzje, 0)),  " + &
"        zfje = sum(fljsb.je - isnull(fljsb.jzje, 0)),  " + &
"        je = sum(fljsb.je),  " + &
"        fpfldmb.fldm  " + &
"    FROM fljsb,  " + &
"        jc_fpb,  " + &
"        fpfldmb,  " + &
"        jsb  " + &
"  WHERE ( jc_fpb.jcfldm = fljsb.fldm ) and  " + &
"        ( fpfldmb.fldm = jc_fpb.fpfldm ) and  " + &
"        ( fljsb.fph = jsb.fph ) and  " + &
"        ( ( jsb.czydm = :czydm ) and  " + &  --这里的变量需要转换，你自己加入。
"  ( jsb.blrq between :kssj and :jssj ) and  " + &  --这里的变量需要转换，你自己加入。
"  ( jsb.zfbz = 'F' )  " + &
"        )  " + &
"GROUP BY fpfldmb.fldm,  " + &
"        fpfldmb.mc  " + &
"ORDER BY fpfldmb.fldm ASC "

Is_Syntax = SQLCA.SyntaxFromSql(Is_Sql , "Style(Type=Grid)" , Is_Error)
IF LEN(TRIM(Is_Error)) > 0 THEN
   MessageBox("系统提示！" , "创建数据窗口语法有错！")
RETURN -1
END IF

dw_1.Create(Is_Syntax , Is_Error)
IF LEN(TRIM(Is_Error)) > 0 THEN
   MessageBox("系统提示！" , "创建数据窗口出错！")
RETURN -1
END IF

dw_1.SetTransObject(sqlca)
dw_1.Retrieve()

long i = 0
for i = 1 to dw_1.rowcount()
    ls_mc = dw_1.getitemstring(动态DW中的对应的字段的获取)
    其他变量的获取
    insert into jkbb (mc, zfje,je,fldm) values(:ls_mc,:d_zfje,:d_je,:ls_fldm) ;
end for

#4

这个问题是数据库的还是PB的？

#5

那样肯定只有一条数据的，用游标吧

#6

该回复于2011-01-25 08:36:58被版主删除

#1

#2