表结构如下:
FNO FN FJD FMoney Fcode
1 1 J 500 6111
1 2 J 300 6111
1 3 D 800 1011
2 1 J 500 1011
2 2 J 500 1012
2 3 D 1000 6111
3 1 J 500 6111
3 2 D 500 1011
4 1 J 300 1002
4 1 D 300 4001
已知:FNO 为每组数的编号,FN为分录号;每组FMoney 按J=D持平;
要求:将每组数中Fcode 为6111的借贷方向改变;即将6111的FJD方向 改为 D,同组非6111的改为J ;
每组数中没有6111的不变,如第4组,不变;如题求解;期待大侠的解答...
FNO FN FJD FMoney Fcode
1 1 J 500 6111
1 2 J 300 6111
1 3 D 800 1011
2 1 J 500 1011
2 2 J 500 1012
2 3 D 1000 6111
3 1 J 500 6111
3 2 D 500 1011
4 1 J 300 1002
4 1 D 300 4001
已知:FNO 为每组数的编号,FN为分录号;每组FMoney 按J=D持平;
要求:将每组数中Fcode 为6111的借贷方向改变;即将6111的FJD方向 改为 D,同组非6111的改为J ;
每组数中没有6111的不变,如第4组,不变;如题求解;期待大侠的解答...
(
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 400110 rows selected.
*/
[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
---------- ---------- - ---------- ----------
1 1 D 500 6111
1 2 D 300 6111
1 3 J 800 1011
2 1 D 500 1011
2 2 D 500 1012
2 3 J 1000 6111
3 1 D 500 6111
3 2 J 500 1011
4 1 J 300 1002
4 1 D 300 4001已选择10行。
select a.fno,fn,decode(sum_fcode,0,fjd ,decode(fcode,'6111','D','J')) fmoney,fcode
from table_a a,(select fno,sum( decode(fcode,'6111',1,0)) sum_fcode from table_a group by fno )a1
where a.fno=a1.fno(+) order by fno,fn
select FNO,FN,FJD,FMoney,decode(Fcode,'6111','D','J') as Fcode
from tab where FNO in (select distinct FNO from tab where Fcode='6111')
union
select *
from tab where FNO not in (select distinct FNO from tab where Fcode='6111')
select FNO,FN,FJD,FMoney,decode(Fcode,'6111','D','J') as Fcode
from tab where FNO in (select distinct FNO from tab where Fcode='6111')
union
select *
from tab where FNO not in (select distinct FNO from tab where Fcode='6111')
order by 1,2
--with t 是测试数据,你不用管
SELECT FNO,
FN,
CASE
WHEN FJD = 'J' AND FLAG = 1 THEN
'D'
WHEN FJD = 'D' AND FLAG = 1 THEN
'J'
ELSE FJD
END FJD,
FMONEY,
FCODE
FROM (SELECT FNO,
FN,
FJD,
FMONEY,
FCODE,
MAX(DECODE(FCODE, 6111, 1, 0)) OVER(PARTITION BY FNO) FLAG
FROM T)--这个T改成你自己的名表就行了。
ORDER BY FNO,FN;
select FNO,FN,decode(Fcode,'6111','D','J') as FJD,FMoney,Fcode
from tab where FNO in (select distinct FNO from tab where Fcode='6111')
union
select *
from tab where FNO not in (select distinct FNO from tab where Fcode='6111')
order by 1,2