--上月日期部分不用from dual,直接可用

select C.num_buy, C.cod_fact, f.nam_fact, C.cod_paym, D.content
  from buym c, CODD D, fact f
 where c.cod_paym <>
       (select cod_paym
          from (select A.NUM_BUY, A.COD_FACT, A.COD_PAYM, A.DAT_BUY
                  FROM BUYM A
                  LEFT JOIN (SELECT COD_FACT,
                                   MAX(DAT_BUY) DAT_BUY,
                                   max(num_buy) num_buy
                              FROM BUYM
                             where dat_buy <=
                                   to_char(last_day(add_months(sysdate, -1)),
                                           'yyyyMMdd')
                             GROUP BY COD_FACT) B ON B.DAT_BUY = A.DAT_BUY
                                                 AND B.COD_FACT = A.COD_FACT
                 WHERE B.DAT_BUY = A.DAT_BUY
                   AND B.COD_FACT = A.COD_FACT
                   and b.num_buy = a.num_buy) t
         where c.cod_fact = t.cod_fact)
   and c.dat_buy > to_char(last_day(add_months(sysdate, -1)), 'yyyyMMdd')
   and C.cod_paym = d.code
   and c.cod_fact = f.cod_fact;

Name    Null?    Type
CODE_ID    NOT NULL    VARCHAR2(10)
CODE    NOT NULL    VARCHAR2(20)
CONTENT        VARCHAR2(2000)
CVALUE        VARCHAR2(100)
EDIT_USER        VARCHAR2(1)
CVALUE2        VARCHAR2(100)
CVALUE3        VARCHAR2(20)
OX_OPT_DB_LOC        VARCHAR2(8)
OX_UPDATED_TIME        VARCHAR2(6)
OX_AREA        VARCHAR2(8)
NUM_CNTL        VARCHAR2(16)
DPT_CTL        VARCHAR2(8)
OX_CODEGRP        VARCHAR2(20)
OX_UPDATED_DATE        DATE
OX_CODECLS        VARCHAR2(10)
OX_DATEND        VARCHAR2(8)
CVALUE4        VARCHAR2(20)
CVALUE5        VARCHAR2(20)
CVALUE6        VARCHAR2(20)
CVALUE7        VARCHAR2(20)
CVALUE8        VARCHAR2(20)

select t.num_buy,
       t.cod_fact,
       f.nam_fact,
       t.acod_paym,
       t.bcod_paym,
       D.content
  from (select a.cod_paym acod_paym,
               a.cod_fact,
               a.num_buy,
               b.cod_paym bcod_paym,
               row_number() over(order by(a.dat_buy - b.dat_buy)) rn
          from buym a, buym b
         where a.cod_paym <> b.cod_paym
           and substr(a.dat_buy, 1, 6) = to_char(sysdate, 'yyyymm')
           and substr(b.dat_buy, 1, 6) =
               to_char(add_months(sysdate, -1), 'yyyymm')) t,
       CODD D,
       fact f
 where t.cod_paym = d.code
   and t.cod_fact = f.cod_fact
   and t.rn = 1;

select C.num_buy, C.cod_fact, f.nam_fact, C.cod_paym, D.content ,
    
    (select t.cod_paym oldpaym
          from (select A.NUM_BUY, A.COD_FACT, A.COD_PAYM, A.DAT_BUY
                  FROM BUYM A
                  LEFT JOIN (SELECT COD_FACT,
                                   MAX(DAT_BUY) DAT_BUY,
                                   max(num_buy) num_buy
                              FROM BUYM
                             where dat_buy <=
                                   to_char(last_day(add_months(sysdate, -1)),
                                           'yyyyMMdd')
                             GROUP BY COD_FACT) B ON B.DAT_BUY = A.DAT_BUY
                                                 AND B.COD_FACT = A.COD_FACT
                 WHERE B.DAT_BUY = A.DAT_BUY
                   AND B.COD_FACT = A.COD_FACT
                   and b.num_buy = a.num_buy) t ,buym c ,codd e
         where c.cod_fact = t.cod_fact
         and c.dat_buy > to_char(last_day(add_months(sysdate, -1)), 'yyyyMMdd')
         and c.cod_paym<>t.cod_paym
         and t.cod_paym = e.code)oldpaym ,

    (select e.content
          from (select A.NUM_BUY, A.COD_FACT, A.COD_PAYM, A.DAT_BUY
                  FROM BUYM A
                  LEFT JOIN (SELECT COD_FACT,
                                   MAX(DAT_BUY) DAT_BUY,
                                   max(num_buy) num_buy
                              FROM BUYM
                             where dat_buy <=
                                   to_char(last_day(add_months(sysdate, -1)),
                                           'yyyyMMdd')
                             GROUP BY COD_FACT) B ON B.DAT_BUY = A.DAT_BUY
                                                 AND B.COD_FACT = A.COD_FACT
                 WHERE B.DAT_BUY = A.DAT_BUY
                   AND B.COD_FACT = A.COD_FACT
                   and b.num_buy = a.num_buy) t ,buym c ,codd e
         where c.cod_fact = t.cod_fact
         and c.dat_buy > to_char(last_day(add_months(sysdate, -1)), 'yyyyMMdd')
         and c.cod_paym<>t.cod_paym
         and t.cod_paym = e.code) oldcontent

  from buym c, CODD D, fact f
 where c.cod_paym <>
       (select cod_paym
          from (select A.NUM_BUY, A.COD_FACT, A.COD_PAYM, A.DAT_BUY
                  FROM BUYM A
                  LEFT JOIN (SELECT COD_FACT,
                                   MAX(DAT_BUY) DAT_BUY,
                                   max(num_buy) num_buy
                              FROM BUYM
                             where dat_buy <=
                                   to_char(last_day(add_months(sysdate, -1)),
                                           'yyyyMMdd')
                             GROUP BY COD_FACT) B ON B.DAT_BUY = A.DAT_BUY
                                                 AND B.COD_FACT = A.COD_FACT
                 WHERE B.DAT_BUY = A.DAT_BUY
                   AND B.COD_FACT = A.COD_FACT
                   and b.num_buy = a.num_buy) t
         where c.cod_fact = t.cod_fact)
   and c.dat_buy > to_char(last_day(add_months(sysdate, -1)), 'yyyyMMdd')
   and C.cod_paym = d.code
   and c.cod_fact = f.cod_fact;

select t.num_buy,
       t.cod_fact,
       f.nam_fact,
       t.cod_paym,
       t.bcod_paym,
       D.content
  from (select a.cod_paym cod_paym,
               a.cod_fact,
               a.num_buy,
               b.cod_paym bcod_paym,
               row_number() over(order by(a.dat_buy - b.dat_buy)) rn
          from buym a, buym b
         where a.cod_paym <> b.cod_paym
           and substr(a.dat_buy, 1, 6) = to_char(sysdate, 'yyyymm')
           and substr(b.dat_buy, 1, 6) =
               to_char(add_months(sysdate, -1), 'yyyymm')) t,
       CODD D,
       fact f
 where t.cod_paym = d.code
   and t.cod_fact = f.cod_fact
   and t.rn = 1;