with t as
(
select 1 fno , 1 fn , 'J' fjd , 500  fmoney , 6111 fcode from dual union all
select 1 fno , 2 fn , 'J' fjd , 300  fmoney , 6111 fcode from dual union all
select 1 fno , 3 fn , 'D' fjd , 800  fmoney , 1011 fcode from dual union all
select 2 fno , 1 fn , 'J' fjd , 500  fmoney , 1011 fcode from dual union all
select 2 fno , 2 fn , 'J' fjd , 500  fmoney , 1012 fcode from dual union all
select 2 fno , 3 fn , 'D' fjd , 1000 fmoney , 6111 fcode from dual union all
select 3 fno , 1 fn , 'J' fjd , 500  fmoney , 6111 fcode from dual union all
select 3 fno , 2 fn , 'D' fjd , 500  fmoney , 1011 fcode from dual union all
select 4 fno , 1 fn , 'J' fjd , 300  fmoney , 1002 fcode from dual union all
select 4 fno , 1 fn , 'D' fjd , 300  fmoney , 4001 fcode from dual
)
select fno , fn , 'D' fid , fmoney , fcode from t where fcode = 6111
union all
select fno , fn , 'J' fid , fmoney , fcode from t where fcode <> 6111 and exists(select 1 from t m where m.fcode = 6111 and m.fno = t.fno)
union all
select t.* from t where not exists(select 1 from t m where m.fcode = 6111 and m.fno = t.fno)
order by fno , fn

/*
       FNO         FN F     FMONEY      FCODE
---------- ---------- - ---------- ----------
         1          1 D        500       6111
         1          2 D        300       6111
         1          3 J        800       1011
         2          1 J        500       1011
         2          2 J        500       1012
         2          3 D       1000       6111
         3          1 D        500       6111
         3          2 J        500       1011
         4          1 J        300       1002
         4          1 D        300       4001

10 rows selected.
*/

------解决方案--------------------
是要求的查询的结果中把借贷修改过来，还是要修改实际的数据？

------解决方案--------------------

SQL code

[SYS@myoracle] SQL>with t as
  2  (
  3  select 1 fno , 1 fn , 'J' fjd , 500  fmoney , 6111 fcode from dual union all
  4  select 1 fno , 2 fn , 'J' fjd , 300  fmoney , 6111 fcode from dual union all
  5  select 1 fno , 3 fn , 'D' fjd , 800  fmoney , 1011 fcode from dual union all
  6  select 2 fno , 1 fn , 'J' fjd , 500  fmoney , 1011 fcode from dual union all
  7  select 2 fno , 2 fn , 'J' fjd , 500  fmoney , 1012 fcode from dual union all
  8  select 2 fno , 3 fn , 'D' fjd , 1000 fmoney , 6111 fcode from dual union all
  9  select 3 fno , 1 fn , 'J' fjd , 500  fmoney , 6111 fcode from dual union all
 10  select 3 fno , 2 fn , 'D' fjd , 500  fmoney , 1011 fcode from dual union all
 11  select 4 fno , 1 fn , 'J' fjd , 300  fmoney , 1002 fcode from dual union all
 12  select 4 fno , 1 fn , 'D' fjd , 300  fmoney , 4001 fcode from dual
 13  )
 14  SELECT FNO,
 15         FN,
 16         CASE
 17           WHEN FJD = 'J' AND FLAG = 1 THEN
 18            'D'
 19           WHEN FJD = 'D' AND FLAG = 1 THEN
 20            'J'
 21           ELSE FJD
 22         END FJD,
 23         FMONEY,
 24         FCODE
 25    FROM (SELECT FNO,
 26                 FN,
 27                 FJD,
 28                 FMONEY,
 29                 FCODE,
 30                 MAX(DECODE(FCODE, 6111, 1, 0)) OVER(PARTITION BY FNO) FLAG
 31            FROM T)
 32   ORDER BY FNO,FN;

       FNO         FN F     FMONEY      FCODE
----