请教一条SQL语句的实现.(Oracle)

Question

9张表 (ABADJUSTMENT a, ABADJUSTMENTDETAIL b, ABINVOICE c, ABINVOICEITEM d, ABCREDITNOTE e,
ABOFFLINERECEIPT f, TMORG g, TMORGPROFILEBRANCH h, TMORGBRANCH i )a表的主键AB_ADJUSTMENT_ID, b表的主键为(AB_ADJUSTMENT_DETAIL_ID 和 AB_ADJUSTMENT_ID) a表和b表为一对多关系.
e表的主键为ab_creditnote_id . f表的主键为AB_RECEIPT_ID.
b表中的ADJUSTMENTSRC_ID 对应e表ab_creditnote_id 或者 f表AB_RECEIPT_ID 需要: 选出不重复的a表AB_ADJUSTMENT_ID,对应一条或者多条b表中的b.ADJUSTMENTSRC_ID, 根据b.ADJUSTMENTSRC_ID 从e表取得e.ab_creditnote_num(一对一)
或者b.ADJUSTMENTSRC_ID 从f表中取得f.RECEIPT_NUM(一对一), 一条ADJUSTMENTSRC_ID只会对应e表或者f表中的一条记录.不可能同时存在.(原则上至少存在一个)
那么要求将a表中的一打ab_adjustment_id 对应的一条或者多条的b.ADJUSTMENTSRC_ID中对应在e.ab_creditnote_num或者f.RECEIPT_NUM叠加成一个String字段中间用逗号分隔.烦请达人帮忙.多谢了!!!参考的SQL(不能实现). SELECT * FROM (SELECT a.AB_ADJUSTMENT_ID,a.PARENT_TYPE,a.PARENT_ID,a.ORG_NUM,
a.ADJUSTMENT_DATE,a.STATUS,a.ADJUSTMENT_NUM,
b.AB_ADJUSTMENT_DETAIL_ID,b.ADJUSTMENTSRC_TYPE,b.ADJUSTMENTSRC_ID,
b.ADJUSTMENT_AMT,
c.bizunit_key, c.costcentre_key,d.item_desc, 
e.ab_creditnote_num,f.RECEIPT_NUM,(此处的两个字段需要叠加,)
g.reg_name, g.reg_num, g.reg_type, 
row_number() over (partition by a.AB_ADJUSTMENT_ID order by a.AB_ADJUSTMENT_ID) row_number 
FROM ABADJUSTMENT a, ABADJUSTMENTDETAIL b, ABINVOICE c, ABINVOICEITEM d, ABCREDITNOTE e,
ABOFFLINERECEIPT f, TMORG g, TMORGPROFILEBRANCH h, TMORGBRANCH i 
WHERE a.AB_ADJUSTMENT_ID = b.AB_ADJUSTMENT_ID AND a.parent_id = c.INVOICE_ID 
AND c.INVOICE_ID = d.INVOICE_ID AND (b.ADJUSTMENTSRC_ID = e.AB_CREDITNOTE_ID OR b.ADJUSTMENTSRC_ID = f.AB_RECEIPT_ID )
AND a.org_num = h.pb_id AND h.branch_id = i.branch_id AND i.org_id = g.org_id  
order by a.AB_ADJUSTMENT_ID) a 
WHERE a.row_number = 1

Answer 1

http://tangshd.blog.sohu.com/96035768.html   参考一下   这是多条语句的合并    你上边的关系挺清晰的  你只要 把几个表联合到一起 就差合并了  呵呵  

Answer 2

-- JUST TRY IT ..
SELECT AB_ADJUSTMENT_ID, WMSYS.WM_CONCAT(DISTINCT MYNUMS) NEW_NUMS
  FROM (SELECT A.AB_ADJUSTMENT_ID  AB_ADJUSTMENT_ID,
               E.AB_CREDITNOTE_NUM MYNUMS
          FROM ABADJUSTMENT       A,
               ABADJUSTMENTDETAIL B,
               ABINVOICE          C,
               ABINVOICEITEM      D,
               ABCREDITNOTE       E,
               TMORG              G,
               TMORGPROFILEBRANCH H,
               TMORGBRANCH        I
         WHERE A.AB_ADJUSTMENT_ID = B.AB_ADJUSTMENT_ID
           AND A.PARENT_ID = C.INVOICE_ID
           AND C.INVOICE_ID = D.INVOICE_ID
           AND B.ADJUSTMENTSRC_ID = E.AB_CREDITNOTE_ID
           AND A.ORG_NUM = H.PB_ID
           AND H.BRANCH_ID = I.BRANCH_ID
           AND I.ORG_ID = G.ORG_ID
        UNION
        SELECT A.AB_ADJUSTMENT_ID AB_ADJUSTMENT_ID, F.RECEIPT_NUM MYNUMS
          FROM ABADJUSTMENT       A,
               ABADJUSTMENTDETAIL B,
               ABINVOICE          C,
               ABINVOICEITEM      D,
               ABOFFLINERECEIPT   F,
               TMORG              G,
               TMORGPROFILEBRANCH H,
               TMORGBRANCH        I
         WHERE A.AB_ADJUSTMENT_ID = B.AB_ADJUSTMENT_ID
           AND A.PARENT_ID = C.INVOICE_ID
           AND C.INVOICE_ID = D.INVOICE_ID
           AND B.ADJUSTMENTSRC_ID = F.AB_RECEIPT_ID
           AND A.ORG_NUM = H.PB_ID
           AND H.BRANCH_ID = I.BRANCH_ID
           AND I.ORG_ID = G.ORG_ID) TT
 GROUP BY AB_ADJUSTMENT_ID;

Answer 3

粗略解决. 楼上的给我的帮助很大.
下边贴出我傻瓜式更改后的SQL语句: 大家看看能不能简化下: 我觉得太冗余了.
----------------------
SELECT * FROM (SELECT AB_ADJUSTMENT_ID, PARENT_TYPE,PARENT_ID,ORG_NUM,ADJUSTMENT_DATE,
STATUS,ADJUSTMENT_NUM,SUM(AMT) AS ADJUSTMENT_AMT,
bizunit_key,costcentre_key,item_desc,reg_name,reg_num,reg_type,
WMSYS.WM_CONCAT(DISTINCT MYNUMS) ADJ_SOURCE_NUM ,
row_number() over (partition by AB_ADJUSTMENT_ID order by AB_ADJUSTMENT_ID) row_number 
  FROM (SELECT A.AB_ADJUSTMENT_ID  AB_ADJUSTMENT_ID,
   A.PARENT_TYPE PARENT_TYPE,
   A.PARENT_ID PARENT_ID,
   A.ORG_NUM ORG_NUM,
   A.ADJUSTMENT_DATE ADJUSTMENT_DATE,
   A.STATUS STATUS,
   A.ADJUSTMENT_NUM ADJUSTMENT_NUM,
   B.ADJUSTMENT_AMT AMT,
   B.AB_ADJUSTMENT_DETAIL_ID AB_ADJUSTMENT_DETAIL_ID,
   B.ADJUSTMENTSRC_TYPE ADJUSTMENTSRC_TYPE,
   C.bizunit_key bizunit_key,
   C.costcentre_key costcentre_key,
   D.item_desc item_desc,
   G.reg_name reg_name,
   G.reg_num  reg_num,
   G.reg_type reg_type,
               E.AB_CREDITNOTE_NUM MYNUMS
          FROM ABADJUSTMENT       A,
               ABADJUSTMENTDETAIL B,
               ABINVOICE          C,
               ABINVOICEITEM      D,
               ABCREDITNOTE       E,
               TMORG              G,
               TMORGPROFILEBRANCH H,
               TMORGBRANCH        I
         WHERE A.AB_ADJUSTMENT_ID = B.AB_ADJUSTMENT_ID
           AND A.PARENT_ID = C.INVOICE_ID
           AND C.INVOICE_ID = D.INVOICE_ID
           AND B.ADJUSTMENTSRC_ID = E.AB_CREDITNOTE_ID
           AND A.ORG_NUM = H.PB_ID
           AND H.BRANCH_ID = I.BRANCH_ID
           AND I.ORG_ID = G.ORG_ID
        UNION
        SELECT A.AB_ADJUSTMENT_ID AB_ADJUSTMENT_ID, 
   A.PARENT_TYPE PARENT_TYPE,
   A.PARENT_ID PARENT_ID,
   A.ORG_NUM ORG_NUM,
   A.ADJUSTMENT_DATE ADJUSTMENT_DATE,
   A.STATUS STATUS,
   A.ADJUSTMENT_NUM ADJUSTMENT_NUM,
   B.ADJUSTMENT_AMT AMT,
   B.AB_ADJUSTMENT_DETAIL_ID AB_ADJUSTMENT_DETAIL_ID,
   B.ADJUSTMENTSRC_TYPE ADJUSTMENTSRC_TYPE,
   c.bizunit_key bizunit_key,
   C.costcentre_key costcentre_key,
   D.item_desc item_desc,
   G.reg_name reg_name,
   G.reg_num  reg_num,
   G.reg_type reg_type,
   F.RECEIPT_NUM MYNUMS
          FROM ABADJUSTMENT       A,
               ABADJUSTMENTDETAIL B,
               ABINVOICE          C,
               ABINVOICEITEM      D,
               ABOFFLINERECEIPT   F,
               TMORG              G,
               TMORGPROFILEBRANCH H,
               TMORGBRANCH        I
         WHERE A.AB_ADJUSTMENT_ID = B.AB_ADJUSTMENT_ID
           AND A.PARENT_ID = C.INVOICE_ID
           AND C.INVOICE_ID = D.INVOICE_ID
           AND B.ADJUSTMENTSRC_ID = F.AB_RECEIPT_ID
           AND A.ORG_NUM = H.PB_ID
           AND H.BRANCH_ID = I.BRANCH_ID
           AND I.ORG_ID = G.ORG_ID) TT  
 GROUP BY AB_ADJUSTMENT_ID,PARENT_ID,PARENT_TYPE,ORG_NUM,ADJUSTMENT_DATE,STATUS,ADJUSTMENT_NUM,bizunit_key,
 costcentre_key,item_desc,reg_name,reg_num,reg_type) a where a.row_number = 1 ;
-----------------------------------------------------------------谢谢楼上两位~!!!!!

Answer 4

重发下方便大家观看.
SELECT * FROM (SELECT AB_ADJUSTMENT_ID, PARENT_TYPE,PARENT_ID,ORG_NUM,ADJUSTMENT_DATE, 
STATUS,ADJUSTMENT_NUM,SUM(AMT) AS ADJUSTMENT_AMT, 
bizunit_key,costcentre_key,item_desc,reg_name,reg_num,reg_type, 
WMSYS.WM_CONCAT(DISTINCT MYNUMS) ADJ_SOURCE_NUM , 
row_number() over (partition by AB_ADJUSTMENT_ID order by AB_ADJUSTMENT_ID) row_number 
  FROM (SELECT A.AB_ADJUSTMENT_ID  AB_ADJUSTMENT_ID, 
  A.PARENT_TYPE PARENT_TYPE, 
  A.PARENT_ID PARENT_ID, 
  A.ORG_NUM ORG_NUM, 
  A.ADJUSTMENT_DATE ADJUSTMENT_DATE, 
  A.STATUS STATUS, 
  A.ADJUSTMENT_NUM ADJUSTMENT_NUM, 
  B.ADJUSTMENT_AMT AMT, 
  B.AB_ADJUSTMENT_DETAIL_ID AB_ADJUSTMENT_DETAIL_ID, 
  B.ADJUSTMENTSRC_TYPE ADJUSTMENTSRC_TYPE, 
  C.bizunit_key bizunit_key, 
  C.costcentre_key costcentre_key, 
  D.item_desc item_desc, 
  G.reg_name reg_name, 
  G.reg_num  reg_num, 
  G.reg_type reg_type, 
              E.AB_CREDITNOTE_NUM MYNUMS 
          FROM ABADJUSTMENT      A, 
              ABADJUSTMENTDETAIL B, 
              ABINVOICE          C, 
              ABINVOICEITEM      D, 
              ABCREDITNOTE      E, 
              TMORG              G, 
              TMORGPROFILEBRANCH H, 
              TMORGBRANCH        I 
        WHERE A.AB_ADJUSTMENT_ID = B.AB_ADJUSTMENT_ID 
          AND A.PARENT_ID = C.INVOICE_ID 
          AND C.INVOICE_ID = D.INVOICE_ID 
          AND B.ADJUSTMENTSRC_ID = E.AB_CREDITNOTE_ID 
          AND A.ORG_NUM = H.PB_ID 
          AND H.BRANCH_ID = I.BRANCH_ID 
          AND I.ORG_ID = G.ORG_ID 
        UNION 
        SELECT A.AB_ADJUSTMENT_ID AB_ADJUSTMENT_ID, 
  A.PARENT_TYPE PARENT_TYPE, 
  A.PARENT_ID PARENT_ID, 
  A.ORG_NUM ORG_NUM, 
  A.ADJUSTMENT_DATE ADJUSTMENT_DATE, 
  A.STATUS STATUS, 
  A.ADJUSTMENT_NUM ADJUSTMENT_NUM, 
  B.ADJUSTMENT_AMT AMT, 
  B.AB_ADJUSTMENT_DETAIL_ID AB_ADJUSTMENT_DETAIL_ID, 
  B.ADJUSTMENTSRC_TYPE ADJUSTMENTSRC_TYPE, 
  c.bizunit_key bizunit_key, 
  C.costcentre_key costcentre_key, 
  D.item_desc item_desc, 
  G.reg_name reg_name, 
  G.reg_num  reg_num, 
  G.reg_type reg_type, 
  F.RECEIPT_NUM MYNUMS 
          FROM ABADJUSTMENT      A, 
              ABADJUSTMENTDETAIL B, 
              ABINVOICE          C, 
              ABINVOICEITEM      D, 
              ABOFFLINERECEIPT  F, 
              TMORG              G, 
              TMORGPROFILEBRANCH H, 
              TMORGBRANCH        I 
        WHERE A.AB_ADJUSTMENT_ID = B.AB_ADJUSTMENT_ID 
          AND A.PARENT_ID = C.INVOICE_ID 
          AND C.INVOICE_ID = D.INVOICE_ID 
          AND B.ADJUSTMENTSRC_ID = F.AB_RECEIPT_ID 
          AND A.ORG_NUM = H.PB_ID 
          AND H.BRANCH_ID = I.BRANCH_ID 
          AND I.ORG_ID = G.ORG_ID) TT  
GROUP BY AB_ADJUSTMENT_ID,PARENT_ID,PARENT_TYPE,ORG_NUM,ADJUSTMENT_DATE,STATUS,ADJUSTMENT_NUM,bizunit_key, 
costcentre_key,item_desc,reg_name,reg_num,reg_type) a where a.row_number = 1 ; 

Answer 5

--分步查询：
--1.先查union两端的两个表；
--2.再union;
--3.再group by;
--4. ......--另：
SELECT *
  FROM (SELECT AB_ADJUSTMENT_ID,
               PARENT_TYPE,
               PARENT_ID,
               ORG_NUM,
               ADJUSTMENT_DATE, ... --这一层的字段要不要加上a.  如：a.AB_ADJUSTMENT_ID, 等?

Answer 6

--试一下：SELECT *
  FROM (SELECT tt.AB_ADJUSTMENT_ID,
               tt.PARENT_TYPE,
               tt.PARENT_ID,
               tt.ORG_NUM,
               tt.ADJUSTMENT_DATE,
               tt.STATUS,
               tt.ADJUSTMENT_NUM,
               tt.SUM(AMT) AS ADJUSTMENT_AMT,
               tt.bizunit_key,
               tt.costcentre_key,
               tt.item_desc,
               tt.reg_name,
               tt.reg_num,
               tt.reg_type,
               WMSYS.WM_CONCAT(DISTINCT tt.MYNUMS) ADJ_SOURCE_NUM,
               row_number() over(partition by tt.AB_ADJUSTMENT_ID order by tt.AB_ADJUSTMENT_ID) row_number
          FROM (SELECT A.AB_ADJUSTMENT_ID        AB_ADJUSTMENT_ID,
                       A.PARENT_TYPE             PARENT_TYPE,
                       A.PARENT_ID               PARENT_ID,
                       A.ORG_NUM                 ORG_NUM,
                       A.ADJUSTMENT_DATE         ADJUSTMENT_DATE,
                       A.STATUS                  STATUS,
                       A.ADJUSTMENT_NUM          ADJUSTMENT_NUM,
                       B.ADJUSTMENT_AMT          AMT,
                       B.AB_ADJUSTMENT_DETAIL_ID AB_ADJUSTMENT_DETAIL_ID,
                       B.ADJUSTMENTSRC_TYPE      ADJUSTMENTSRC_TYPE,
                       C.bizunit_key             bizunit_key,
                       C.costcentre_key          costcentre_key,
                       D.item_desc               item_desc,
                       G.reg_name                reg_name,
                       G.reg_num                 reg_num,
                       G.reg_type                reg_type,
                       E.AB_CREDITNOTE_NUM       MYNUMS
                  FROM ABADJUSTMENT       A,
                       ABADJUSTMENTDETAIL B,
                       ABINVOICE          C,
                       ABINVOICEITEM      D,
                       ABCREDITNOTE       E,
                       TMORG              G,
                       TMORGPROFILEBRANCH H,
                       TMORGBRANCH        I
                 WHERE A.AB_ADJUSTMENT_ID = B.AB_ADJUSTMENT_ID
                   AND A.PARENT_ID = C.INVOICE_ID
                   AND C.INVOICE_ID = D.INVOICE_ID
                   AND B.ADJUSTMENTSRC_ID = E.AB_CREDITNOTE_ID
                   AND A.ORG_NUM = H.PB_ID
                   AND H.BRANCH_ID = I.BRANCH_ID
                   AND I.ORG_ID = G.ORG_ID
                UNION
                SELECT A.AB_ADJUSTMENT_ID        AB_ADJUSTMENT_ID,
                       A.PARENT_TYPE             PARENT_TYPE,
                       A.PARENT_ID               PARENT_ID,
                       A.ORG_NUM                 ORG_NUM,
                       A.ADJUSTMENT_DATE         ADJUSTMENT_DATE,
                       A.STATUS                  STATUS,
                       A.ADJUSTMENT_NUM          ADJUSTMENT_NUM,
                       B.ADJUSTMENT_AMT          AMT,
                       B.AB_ADJUSTMENT_DETAIL_ID AB_ADJUSTMENT_DETAIL_ID,
                       B.ADJUSTMENTSRC_TYPE      ADJUSTMENTSRC_TYPE,
                       c.bizunit_key             bizunit_key,
                       C.costcentre_key          costcentre_key,
                       D.item_desc               item_desc,
                       G.reg_name                reg_name,
                       G.reg_num                 reg_num,
                       G.reg_type                reg_type,
                       F.RECEIPT_NUM             MYNUMS
                  FROM ABADJUSTMENT       A,
                       ABADJUSTMENTDETAIL B,
                       ABINVOICE          C,
                       ABINVOICEITEM      D,
                       ABOFFLINERECEIPT   F,
                       TMORG              G,
                       TMORGPROFILEBRANCH H,
                       TMORGBRANCH        I
                 WHERE A.AB_ADJUSTMENT_ID = B.AB_ADJUSTMENT_ID
                   AND A.PARENT_ID = C.INVOICE_ID
                   AND C.INVOICE_ID = D.INVOICE_ID
                   AND B.ADJUSTMENTSRC_ID = F.AB_RECEIPT_ID
                   AND A.ORG_NUM = H.PB_ID
                   AND H.BRANCH_ID = I.BRANCH_ID
                   AND I.ORG_ID = G.ORG_ID) TT
         GROUP BY tt.AB_ADJUSTMENT_ID,
                  tt.PARENT_ID,
                  tt.PARENT_TYPE,
                  tt.ORG_NUM,
                  tt.ADJUSTMENT_DATE,
                  tt.STATUS,
                  tt.ADJUSTMENT_NUM,
                  tt.bizunit_key,
                  tt.costcentre_key,
                  tt.item_desc,
                  tt.reg_name,
                  tt.reg_num,
                  tt.reg_type) tab
 where tab.row_number = 1;

Answer 7

我看楼主的实体关系以及业务约束已经描述得够清晰.在此基础上,我看大量的group by和union完全是不必要的.下面的语句在给定的业务约束下,是完全等效的:        SELECT 
            A.AB_ADJUSTMENT_ID, 
            A.PARENT_TYPE, 
            A.PARENT_ID, 
            A.ORG_NUM, 
            A.ADJUSTMENT_DATE, 
            A.STATUS, 
            A.ADJUSTMENT_NUM, 
            B.AMT, 
            c.bizunit_key, 
            C.costcentre_key, 
            D.item_desc, 
            G.reg_name, 
            G.reg_num, 
            G.reg_type, 
            B.MYNUMS 
          FROM ABADJUSTMENT      A
          JOIN ABINVOICE          C ON A.PARENT_ID = C.INVOICE_ID 
          JOIN ABINVOICEITEM      D ON C.INVOICE_ID = D.INVOICE_ID
          JOIN TMORGPROFILEBRANCH H ON A.ORG_NUM = H.PB_ID
          JOIN TMORGBRANCH        I ON H.BRANCH_ID = I.BRANCH_ID
          JOIN TMORG              G ON I.ORG_ID = G.ORG_ID  
          JOIN 
          (
              SELECT B.AB_ADJUSTMENT_ID,SUM(ADJUSTMENT_AMT) AMT,WMSYS.WM_CONCAT(E.AB_CREDITNOTE_NUM || E.RECEIPT_NUM) MYNUMS
              FROM ABADJUSTMENTDETAIL B 
              LEFT JOIN ABCREDITNOTE      E ON B.ADJUSTMENTSRC_ID = E.AB_CREDITNOTE_ID
              LEFT JOIN ABOFFLINERECEIPT  F ON B.ADJUSTMENTSRC_ID = F.AB_RECEIPT_ID
              GROUP BY B.AB_ADJUSTMENT_ID
          ) B ON A.AB_ADJUSTMENT_ID=B.AB_ADJUSTMENT_ID

Answer 8

感谢楼上各位.
同样借助各位的帮忙 .
解决了另外一个SQL.SELECT  * FROM (
SELECT AB_WRITEOFF_ID,WRITEOFFSRC_TYPE,WRITEOFFSRC_ID,WRITEOFF_DATE,WRITEOFF_AMT,
   STATUS,WRITEOFF_NUM,reg_name,reg_num,reg_type, 
   WMSYS.WM_CONCAT(DISTINCT MYBIZUNIT) bizunit_key,
   WMSYS.WM_CONCAT(DISTINCT MYCOSTCENTRE) costcentre_key,
   WMSYS.WM_CONCAT(DISTINCT MYITEMDESC) item_desc,
   WMSYS.WM_CONCAT(DISTINCT MYORGNUM) ORG_NUM,
   WMSYS.WM_CONCAT(DISTINCT MYSOURCENUM) writeoff_src_num,
   row_number() over (partition by AB_WRITEOFF_ID order by AB_WRITEOFF_ID) row_number 
     FROM (SELECT A.AB_WRITEOFF_ID     AB_WRITEOFF_ID,
  A.WRITEOFFSRC_TYPE   WRITEOFFSRC_TYPE,
  A.WRITEOFFSRC_ID     WRITEOFFSRC_ID,
  A.WRITEOFF_DATE      WRITEOFF_DATE,
  A.WRITEOFF_AMT       WRITEOFF_AMT,
  A.WRITEOFF_NUM       WRITEOFF_NUM,
  A.STATUS             STATUS,
  G.reg_name           reg_name, 
                  G.reg_num            reg_num, 
                  G.reg_type           reg_type,
  to_number(B.ORG_NUM) MYORGNUM,
  B.BIZUNIT_KEY        MYBIZUNIT,
  B.costcentre_key     MYCOSTCENTRE,
  C.ITEM_DESC          MYITEMDESC,
  B.invoice_num        MYSOURCENUM 
  FROM   ABWRITEOFF           A,
  ABINVOICE    B,
ABINVOICEITEM        C,
TMORG                G, 
                  TMORGPROFILEBRANCH   H, 
                  TMORGBRANCH          I 
   WHERE A.WRITEOFFSRC_ID = B.INVOICE_ID 
     AND B.INVOICE_ID     = C.INVOICE_ID 
 AND B.ORG_NUM        = H.PB_ID 
 AND H.BRANCH_ID      = I.BRANCH_ID  
             AND I.ORG_ID         = G.ORG_ID  
  UNION
           SELECT A.AB_WRITEOFF_ID     AB_WRITEOFF_ID,
          A.WRITEOFFSRC_TYPE   WRITEOFFSRC_TYPE,
  A.WRITEOFFSRC_ID     WRITEOFFSRC_ID,
      A.WRITEOFF_DATE      WRITEOFF_DATE,
  A.WRITEOFF_AMT       WRITEOFF_AMT,
  A.WRITEOFF_NUM       WRITEOFF_NUM,
  A.STATUS             STATUS,
  G.reg_name           reg_name, 
                  G.reg_num            reg_num, 
                  G.reg_type           reg_type,
  J.ORG_ID             MYORGNUM,
  NULL        MYBIZUNIT,
  NULL      MYCOSTCENTRE,
  'Refund Request'          MYITEMDESC,
      J.RF_RECEIVABLE_CODE MYSOURCENUM
   FROM   ABWRITEOFF           A,
  ABINVOICE    B,
          RFRECEIVABLEREQUEST  J,
  ABINVOICEITEM        C,
  TMORG                G, 
                  TMORGPROFILEBRANCH   H, 
                  TMORGBRANCH          I
   WHERE A.WRITEOFFSRC_ID  = J.RF_RECEIVABLE_ID 
     AND J.ORG_ID          = H.PB_ID 
 AND H.BRANCH_ID      = I.BRANCH_ID 
             AND I.ORG_ID         = G.ORG_ID) TT
 GROUP BY AB_WRITEOFF_ID,WRITEOFFSRC_TYPE,WRITEOFFSRC_ID,WRITEOFF_DATE,WRITEOFF_AMT,WRITEOFF_NUM ,STATUS
,reg_name,reg_num,reg_type) a where a.row_number = 1  ; 
结帖. 谢谢大家!