有一表U
NUM_CHGM MNY_BALN DORC
5CH07120009 525 C
5CH07120009 720 D
5CH07120010 100 D
5CH08020138 5306.1 C
5CH08020138 5306 D我需要得到這樣的結果
NUM_CHGM MNY_BALN
5CH07120009 195
5CH07120010 100
5CH08020138 -0.1即同一個num_chgm中D的MNY_BALN減去C的MNY_BALN。(注:有些NUM_CHGM的DORC只有D或者C,但要求是需要得到D減去C的MNY_BALN)
求助了,謝謝!
NUM_CHGM MNY_BALN DORC
5CH07120009 525 C
5CH07120009 720 D
5CH07120010 100 D
5CH08020138 5306.1 C
5CH08020138 5306 D我需要得到這樣的結果
NUM_CHGM MNY_BALN
5CH07120009 195
5CH07120010 100
5CH08020138 -0.1即同一個num_chgm中D的MNY_BALN減去C的MNY_BALN。(注:有些NUM_CHGM的DORC只有D或者C,但要求是需要得到D減去C的MNY_BALN)
求助了,謝謝!
from
(select num_chgm,sum(mny_baln) mny_baln from U where mny_baln='D' group by num_chgm)A
left join
(select num_chgm,sum(mny_baln) mny_baln from U where mny_baln='C' group by num_chgm)B
on A.num_chgm=B.num_chgm
SELECT NUM_CHGM,MAX(MNY_BALN)-MIN(MNY_BALN) MNY_RCV FROM
(SELECT NUM_CHGM,SUM(MNY_BALN) MNY_BALN FROM UACHGD
GROUP BY NUM_CHGM,DORC) UACHGD
GROUP BY NUM_CHGM
上面講的那個U表是通過這個sql才查詢得到的,那怎么再合并起來?我沒弄成,謝謝!
SELECT NUM_CHGM, SUM( DECODE(DORC,'D', MNY_BALN,-1*MNY_BALN)) MNY_BALN FROM U
GROUP BY NUM_CHGM
改进一下:
SQL> select * from u;NUM_CHGM MNY_BALN DORC
------------------------------ ---------- -----
5CH07120009 525 C
5CH07120009 720 D
5CH07120010 100 D
5CH07120038 5306.1 C
5CH07120038 5306 D
5CH07120039 1000 C已选择6行。SQL> SELECT NUM_CHGM, SUM( DECODE(DORC,'D', MNY_BALN,-1*MNY_BALN)) MNY_BALN,count(*) FROM U
2 GROUP BY NUM_CHGM having count(*)>1
3 union all
4 SELECT NUM_CHGM,max(MNY_BALN) MNY_BALN,count(*) FROM U
5 GROUP BY NUM_CHGM having count(*)=1;NUM_CHGM MNY_BALN COUNT(*)
------------------------------ ---------- ----------
5CH07120038 -.1 2
5CH07120009 195 2
5CH07120010 100 1
5CH07120039 1000 1