注册 登录
编程论坛 PowerBuilder

pb union all 后如何把不是0的数字弄在同一行显示

jlswcy 发布于 2015-03-09 10:25, 4225 次点击
这个是代码:
SELECT salrcd.dptid as dptid,
         (select dptdes from dpt where dptid =salrcd.dptid) as dptdes,   
         sum(((- 1 )+sign(salrcd.qty))/2 * salrcd.sttamt) as thje,   
         count(distinct salrcd.scd) as kds,   
         sum(salrcd.sttamt) as ssje,   
         sum(round(salrcd.qty * salrcd.rtlprc,2)) as ysje,
        0 as patamt,   
        0 as cstamt,
        0 as rvnamt,
        0 as mlamt,
        0 as mll
         
    FROM salrcd  
   WHERE ( salrcd.trddtm >= (:as_bdt+'000000') ) AND  
         ( salrcd.trddtm <= (:as_edt+'235900') ) AND  
         ( salrcd.dptid like :as_fd )   
GROUP BY salrcd.dptid

UNION ALL

select
        bizdep.savdptid as dptid,
       (select dptdes from dpt where bizdep.savdptid = dpt.dptid) as dptdes,
        0 as thje,   
        0 as kds,
        0 as ssje,
        0 as ysje,
       round(sum(patamt),2) as patamt,
       round(sum(cstamt*(1+taxrto)),2) as cstamt,
       round(sum(rvnamt),2) as rvnamt,
       round((round(sum(patamt),2) - round(sum(cstamt*(1+taxrto)),2)),2) as mlamt,
       round((round(sum(patamt),2) - round(sum(cstamt*(1+taxrto)),2)) / round(sum(patamt),2),4) as mll
  from scst,bizdep
 where scst.depid = bizdep.depid and
       scst.dptid = bizdep.dptid and
       bizdep.savdptid not like '6%' and            
       bizdep.savdptid like :as_fd and
       scst.actdat >= :as_bdt and
       scst.actdat <= :as_edt   
group by bizdep.savdptid

执行以后显示的结果如下:请大家 帮忙 怎么才能让他显示的结果 在一行呢。谢谢了

001   542.17   6559  521.65   583.55    0    0    0    0    0
001    0    0    0    0      910    442.04  75.43    468.22  0.37

我想要的结果是这样
001   542.17   6559  521.65   583.55  910    442.04  75.43    468.22  0.37
2 回复
#2
jlswcy2015-03-09 13:43
求那个大神 看一下  有没有 大神知道的啊!!
#3
pbdev2015-07-26 17:35
回复 2楼 jlswcy
最简单的办法就是把你的所有SQL语句()起来,在外面用SUM一下。

SELECT DPTID,SUM(**) AS  ** FROM (你的SQL) GROUP BY DPTID


[ 本帖最后由 pbdev 于 2015-7-26 17:37 编辑 ]
1