表结构如下：
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组，不变；
如题求解；期待大侠的解答...
------解决方案--------------------
- SQL code
with t as(select 1 fno , 1 fn , 'J' fjd , 500 fmoney , 6111 fcode from dual union allselect 1 fno , 2 fn , 'J' fjd , 300 fmoney , 6111 fcode from dual union allselect 1 fno , 3 fn , 'D' fjd , 800 fmoney , 1011 fcode from dual union allselect 2 fno , 1 fn , 'J' fjd , 500 fmoney , 1011 fcode from dual union allselect 2 fno , 2 fn , 'J' fjd , 500 fmoney , 1012 fcode from dual union allselect 2 fno , 3 fn , 'D' fjd , 1000 fmoney , 6111 fcode from dual union allselect 3 fno , 1 fn , 'J' fjd , 500 fmoney , 6111 fcode from dual union allselect 3 fno , 2 fn , 'D' fjd , 500 fmoney , 1011 fcode from dual union allselect 4 fno , 1 fn , 'J' fjd , 300 fmoney , 1002 fcode from dual union allselect 4 fno , 1 fn , 'D' fjd , 300 fmoney , 4001 fcode from dual)select fno , fn , 'D' fid , fmoney , fcode from t where fcode = 6111union allselect 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 allselect 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.*/
------解决方案--------------------
是要求的查询的结果中把借贷修改过来，还是要修改实际的数据？
------解决方案--------------------
- 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---------- ---------- - ---------- ---------- 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行。
------解决方案--------------------
table_a 你的那个表
- SQL code
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 )a1where a.fno=a1.fno(+) order by fno,fn
------解决方案--------------------
- SQL code
select FNO,FN,FJD,FMoney,decode(Fcode,'6111','D','J') as Fcodefrom 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')
------解决方案--------------------
- SQL code
select FNO,FN,FJD,FMoney,decode(Fcode,'6111','D','J') as Fcodefrom 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
------解决方案--------------------
- SQL code
--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;
------解决方案--------------------
- SQL code
select FNO,FN,decode(Fcode,'6111','D','J') as FJD,FMoney,Fcodefrom 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