一个sql问题，怎样才能出这样的显示结果

Question

不知道我理解的对不对，如：
11:58:40 SQL> desc t;
 名称                                      空?      类型
 ----------------------------------------- -------- ---------------------------- A                                                  NUMBER(38)
 B                                                  NUMBER(38)
 C                                                  NUMBER(38)
 D                                                  NUMBER(38)
 E                                                  NUMBER(38)
 F                                                  NUMBER(38)
 G                                                  NUMBER(38)11:59:01 SQL> select * from t;
  1          2          3          4          5          6   7
  2          3          4          5          6          7   8已用时间:  00: 00: 00.00
11:59:04 SQL> select a,b||chr(10)||c||chr(10)||d col1,e||chr(10)||f col2,g from
t;
  1 2          5            7
    3          6
    4  2 3          6            8
    4          7
    5
已用时间:  00: 00: 00.00

Answer 1

不太对啊  
比如一条记录是
    FLD_PERIOD_ID FLD_RESOURCE_ID FLD_TRADE_QUANTITY FLD_FINANCE_QUANTITY FLD_EST_QUANTITY FLD_ZT_QUANTITY FLD_CY_QUANTITY FLD_REMARKS
1 200411 02HYDGGJ0070HKG(F0218)-2-19 0.000000 -6032.000000 0.000000 18928.000000 0.000000 ？我替换了一下
select nn.fld_resource_id,
       nn.fld_trade_quantity || chr(10) || nn.fld_zt_quantity || chr(10) ||
       nn.fld_cy_quantity col1,
       nn.fld_finance_quantity || chr(10) || nn.fld_est_quantity col2,
       nn.fld_res
  from f.t_gl_rpt_stock_balance nn
  where nn.fld_res is not null执行结果是
FLD_RESOURCE_ID            COL1              COL2          FLD_REMARKS
02HYDGGJ0070HKG(F0218)-2-19   0 18928 0     -6032 0 ？我要的是
FLD_RESOURCE_ID           COL1  COL2 FLD_REMARKS
02HYDGGJ0070HKG(F0218)-2-19  0 -60320 ？
                   18928 0
                             0

Answer 2

我写了一个 执行单条语句时候是可以的
select max(fld_trade_quantity) col1, sum(fld_finance_quantity) col2
  from (select rownum,
               t.fld_trade_quantity,
               t.fld_finance_quantity,
               t.fld_resource_id,
               t.fld_res
          from f.t_gl_rpt_stock_balance t
         where t.fld_res is not null and
               t.fld_resource_id = '02HYDGGJ0070HKG(F0218)-2-19'
        union all
        select rownum,
               t.fld_zt_quantity,
               t.fld_est_quantity,
               t.fld_resource_id,
               t.fld_res
          from f.t_gl_rpt_stock_balance t
         where t.fld_res is not null and
               t.fld_resource_id = '02HYDGGJ0070HKG(F0218)-2-19'
        union all
        select rownum,
               t.fld_cy_quantity,
               0,
               t.fld_resource_id,
               t.fld_res
          from f.t_gl_rpt_stock_balance t
         where t.fld_res is not null and
               t.fld_resource_id = '02HYDGGJ0070HKG(F0218)-2-19') nn1
 group by rownum
执行结果是
     COL1 COL2
1 0 -6032
2 18928 0
3 0 0
但是问题是如果是多条的记录
结果就混乱了select max(fld_trade_quantity) col1, sum(fld_finance_quantity) col2
  from (select rownum,
               t.fld_trade_quantity,
               t.fld_finance_quantity,
               t.fld_resource_id,
               t.fld_res
          from f.t_gl_rpt_stock_balance t
         where t.fld_res is not null 
        union all
        select rownum,
               t.fld_zt_quantity,
               t.fld_est_quantity,
               t.fld_resource_id,
               t.fld_res
          from f.t_gl_rpt_stock_balance t
         where t.fld_res is not null 
        union all
        select rownum,
               t.fld_cy_quantity,
               0,
               t.fld_resource_id,
               t.fld_res
          from f.t_gl_rpt_stock_balance t
         where t.fld_res is not null ) nn1
 group by rownum
执行结果就成了
    COL1 COL2
1 0 -10
2 0 -5
3 0 -36
4 0 -100
5 0 -14400
6 0 -60
7 0 -10

Answer 3

你这个只是显示的问题，可以用col设置一下显示格式

Answer 4

for example:
col FLD_RESOURCE_ID for a30
col COL1 for a10
col COL2 for 999999.99
col FLD_REMARKS for a10

Answer 5

解决了,我的sql如下,还能不能有改进的地方,请指正一下  ^_^ 谢谢了 各位
select fld_resource_id,
       aa 名称,
       fld_trade_quantity 数值,
       bb 名称,
       fld_finance_quantity 数值,
       fld_res
  from (select rownum,
               '业务量' aa,
               Nvl(t.fld_trade_quantity,0) fld_trade_quantity,
               '财务量' bb,
               Nvl(t.fld_finance_quantity,0) fld_finance_quantity,
               t.fld_resource_id,
               t.fld_res
          from f.t_gl_rpt_stock_balance t
         where t.fld_res is not null
        union all
        select rownum,
               '在途量' aa,
               Nvl(t.fld_zt_quantity,0) fld_trade_quantity,
               '暂估量' bb,
               Nvl(t.fld_est_quantity,0) fld_finance_quantity, 
               t.fld_resource_id,
               t.fld_res
          from f.t_gl_rpt_stock_balance t
         where t.fld_res is not null
        union all
        select rownum,
               '出库未开票量' aa,
               Nvl(t.fld_cy_quantity,0) fld_trade_quantity,
               '   ' bb,
               0 fld_finance_quantity,
               t.fld_resource_id,
               t.fld_res
          from f.t_gl_rpt_stock_balance t
         where t.fld_res is not null)
 order by fld_resource_id