请教高手解决一张oracle复杂报表

Question

请教高手(oracle 9i)
表1:投资类型表(部分数据)
 tradeId    tradeName     parentTradeId
  01        交易性金融资产              
  0101        股票              01
  0102        基金              01表2:证券库存表(部分数据)
  tradeId     productId        productName        kcNum
   0101          a01             工商银行            1000
   0102          a02             广发聚丰            2000现需要生成如下样式的报表:
   品种              库存数量
---------------------------
 交易性金融资产        3000
   股票               1000
     工商银行         1000
   基金               2000
     广发聚丰         2000注:用函数返回结果集已经实现，但我现在希望能用一张视图解决,谢谢各位帮忙

Answer 1

这个可以用SQL实现，不过你这样出来的数据人家是看不懂，看不出父子关系的（你是不是想在每级子的品种前面加班个空格?）SELECT   aa.tradename, aa.kcnum
    FROM (SELECT a.tradeid, '0000' tradeid_1, '000' productid, a.tradename,
                 bb.kcnum
            FROM table1 a,
                 (SELECT   a.parenttradeid, SUM (b.kcnum) kcnum
                      FROM table1 a, table2 b
                     WHERE a.tradeid = b.tradeid
                  GROUP BY a.parenttradeid) bb
           WHERE a.tradeid = bb.parenttradeid
          UNION ALL
          SELECT   a.parenttradeid, a.tradeid, '000', a.tradename,
                   SUM (b.kcnum)
              FROM table1 a, table2 b
             WHERE a.tradeid = b.tradeid
          GROUP BY a.parenttradeid, a.tradeid
          UNION ALL
          SELECT a.parenttradeid, a.tradeid, b.productid, b.productname,
                 SUM (b.kcnum)
            FROM table1 a, table2 b
           WHERE a.tradeid = b.tradeid) aa
ORDER BY aa.parenttradeid, aa.tradeid, aa.productid

Answer 2

上面的有错误，下面是加入测试数据的，/* Formatted on 2008/04/22 23:37 (Formatter Plus v4.8.8) */
WITH table1 AS
     (SELECT '01' tradeid, '交易性金融资产' tradename, '' parenttradeid
        FROM DUAL
      UNION ALL
      SELECT '0101', '股票', '01'
        FROM DUAL
      UNION ALL
      SELECT '0102', '基金', '01'
        FROM DUAL),
     table2 AS
     (SELECT '0101' tradeid, 'a01' productid, '工商银行' productname,
             1000 kcnum
        FROM DUAL
      UNION ALL
      SELECT '0102', 'a02', '广发聚丰', 2000
        FROM DUAL)
SELECT   aa.tradename, aa.kcnum
    FROM (SELECT a.tradeid, '0000' tradeid_1, '000' productid, a.tradename,
                 bb.kcnum
            FROM table1 a,
                 (SELECT   a.parenttradeid, SUM (b.kcnum) kcnum
                      FROM table1 a, table2 b
                     WHERE a.tradeid = b.tradeid
                  GROUP BY a.parenttradeid) bb
           WHERE a.tradeid = bb.parenttradeid
          UNION ALL
          SELECT   a.parenttradeid, a.tradeid, '000', a.tradename,
                   SUM (b.kcnum)
              FROM table1 a, table2 b
             WHERE a.tradeid = b.tradeid
          GROUP BY a.parenttradeid, a.tradeid, a.tradename
          UNION ALL
          SELECT a.parenttradeid, a.tradeid, b.productid, b.productname,
                 b.kcnum
            FROM table1 a, table2 b
           WHERE a.tradeid = b.tradeid) aa
ORDER BY aa.tradeid, aa.tradeid_1, aa.productid
结果
Row# TRADENAME KCNUM1 交易性金融资产 3000
2 股票 1000
3 工商银行 1000
4 基金 2000
5 广发聚丰 2000

Answer 3

WITH table1 AS
     (SELECT '01' tradeid, '交易性金融资产' tradename, '' parenttradeid
        FROM DUAL
      UNION ALL
      SELECT '0101', '股票', '01'
        FROM DUAL
      UNION ALL
      SELECT '0102', '基金', '01'
        FROM DUAL),
     table2 AS
     (SELECT '0101' tradeid, 'a01' productid, '工商银行' productname,
             1000 kcnum
        FROM DUAL
      UNION ALL
      SELECT '0102', 'a02', '广发聚丰', 2000
        FROM DUAL)
SELECT   aa.tradename, aa.kcnum
    FROM (SELECT a.tradeid, '0000' tradeid_1, '000' productid, a.tradename,
                 bb.kcnum
            FROM table1 a,
                 (SELECT   a.parenttradeid, SUM (b.kcnum) kcnum
                      FROM table1 a, table2 b
                     WHERE a.tradeid = b.tradeid
                  GROUP BY a.parenttradeid) bb
           WHERE a.tradeid = bb.parenttradeid
          UNION ALL
          SELECT   a.parenttradeid, a.tradeid, '000', '        '||a.tradename,
                   SUM (b.kcnum)
              FROM table1 a, table2 b
             WHERE a.tradeid = b.tradeid
          GROUP BY a.parenttradeid, a.tradeid, a.tradename
          UNION ALL
          SELECT a.parenttradeid, a.tradeid, b.productid, '                '||b.productname,
                 b.kcnum
            FROM table1 a, table2 b
           WHERE a.tradeid = b.tradeid) aa
ORDER BY aa.tradeid, aa.tradeid_1, aa.productid这个是各级子名字前加空格的
结果
Row# TRADENAME KCNUM1 交易性金融资产 3000
2         股票 1000
3                 工商银行 1000
4         基金 2000
5                 广发聚丰 2000

Answer 4

去掉测试数据，子名字前不加空格 
SELECT   aa.tradename 品种, aa.kcnum 库存数量
    FROM (SELECT a.tradeid, '0000' tradeid_1, '000' productid, a.tradename,
                 bb.kcnum
            FROM table1 a,
                 (SELECT   a.parenttradeid, SUM (b.kcnum) kcnum
                      FROM table1 a, table2 b
                     WHERE a.tradeid = b.tradeid
                  GROUP BY a.parenttradeid) bb
           WHERE a.tradeid = bb.parenttradeid
          UNION ALL
          SELECT   a.parenttradeid, a.tradeid, '000', a.tradename,
                   SUM (b.kcnum)
              FROM table1 a, table2 b
             WHERE a.tradeid = b.tradeid
          GROUP BY a.parenttradeid, a.tradeid, a.tradename
          UNION ALL
          SELECT a.parenttradeid, a.tradeid, b.productid, b.productname,
                 b.kcnum
            FROM table1 a, table2 b
           WHERE a.tradeid = b.tradeid) aa
ORDER BY aa.tradeid, aa.tradeid_1, aa.productid子名字前加空格 
SELECT   aa.tradename 品种, aa.kcnum 库存数量
    FROM (SELECT a.tradeid, '0000' tradeid_1, '000' productid, a.tradename,
                 bb.kcnum
            FROM table1 a,
                 (SELECT   a.parenttradeid, SUM (b.kcnum) kcnum
                      FROM table1 a, table2 b
                     WHERE a.tradeid = b.tradeid
                  GROUP BY a.parenttradeid) bb
           WHERE a.tradeid = bb.parenttradeid
          UNION ALL
          SELECT   a.parenttradeid, a.tradeid, '000', '        '||a.tradename,
                   SUM (b.kcnum)
              FROM table1 a, table2 b
             WHERE a.tradeid = b.tradeid
          GROUP BY a.parenttradeid, a.tradeid, a.tradename
          UNION ALL
          SELECT a.parenttradeid, a.tradeid, b.productid, '                '||b.productname,
                 b.kcnum
            FROM table1 a, table2 b
           WHERE a.tradeid = b.tradeid) aa
ORDER BY aa.tradeid, aa.tradeid_1, aa.productid