Oracle sql累计结果
D.VL_SALDOTOTAL等于452,00。我希望SALDOTOTAL的累积效果如下图所示:66,80 + 482,00 = 548,80 - > 107,00 + 548,80 = 655,80,但它总计SALDODIA + 482,00每排。这是可能的简单的SQL或我需要一个存储过程?Oracle sql累计结果
select B.NR_CTAPES, A.DS_TITULAR, B.TP_MANUTENCAO as DS_MANUTENCAO, B.DT_MOVIM, B.TP_DOCUMENTO as DS_DOCUMENTO, B.TP_OPERACAO as DS_OPERACAO, B.NR_NF, B.VL_LANCTO, B.CD_HISTORICO as DS_HISTORICO, (SUM (DECODE(B.TP_OPERACAO,'C', DECODE(
B.TP_DOCUMENTO, '3', B.VL_LANCTO, 0),0)) - SUM (DECODE(B.TP_OPERACAO,'D', DECODE(
B.TP_DOCUMENTO, '3', B.VL_LANCTO, 0),0)) - SUM (DECODE(B.TP_OPERACAO,'D', DECODE(B.TP_DOCUMENTO, '9', B.VL_LANCTO, 0),0))) as VL_SALDODIA, COALESCE(D.VL_SALDOTOTAL + (SUM (DECODE(B.TP_OPERACAO,'C', DECODE(B.TP_DOCUMENTO, '3', B.VL_LANCTO,
0),0)) - SUM (DECODE(B.TP_OPERACAO,'D', DECODE(
B.TP_DOCUMENTO, '3', B.VL_LANCTO, 0),0)) - SUM (DECODE(B.TP_OPERACAO,'D', DECODE(B.TP_DOCUMENTO, '9', B.VL_LANCTO, 0),0))),0) as VL_SALDOTOTAL
FROM VR_FCC_CTAPES A
LEFT OUTER JOIN VR_FCC_MOVLIQ B ON
B.NR_CTAPES = A.NR_CTAPES
AND B.TP_MANUTENCAO = A.TP_MANUTENCAO
AND B.CD_EMPRESA = A.CD_EMPRESA
LEFT OUTER JOIN (
select (SUM (DECODE(C.TP_OPERACAO,'C', DECODE(C.TP_DOCUMENTO, '3', C.VL_LANCTO, 0),0)) - SUM (DECODE(C.TP_OPERACAO,'D', DECODE(C.TP_DOCUMENTO, '3', C.VL_LANCTO, 0),0)) - SUM (DECODE(C.TP_OPERACAO,'D', DECODE(C.TP_DOCUMENTO, '9',
C.VL_LANCTO, 0),0))) as
VL_SALDOTOTAL, C.NR_CTAPES, C.TP_DOCUMENTO, C.TP_MANUTENCAO, C.CD_EMPRESA, C.DT_MOVIM, C.CD_HISTORICO
FROM VR_FCC_MOVLIQ C
WHERE C.IN_ESTORNO ='F'
GROUP BY C.NR_CTAPES, C.TP_DOCUMENTO, C.TP_MANUTENCAO, C.CD_EMPRESA, C.DT_MOVIM, C.CD_HISTORICO
) D ON
D.NR_CTAPES = B.NR_CTAPES
AND D.TP_DOCUMENTO = B.TP_DOCUMENTO
AND D.TP_MANUTENCAO = B.TP_MANUTENCAO
AND D.CD_EMPRESA = B.CD_EMPRESA
AND D.DT_MOVIM = TO_DATE(@DT_INICIAL) - 1
AND D.CD_HISTORICO = B.CD_HISTORICO
WHERE
B.TP_MANUTENCAO IN ('4','6')
AND B.TP_DOCUMENTO IN ('3','9')
AND B.IN_ESTORNO ='F'
AND B.CD_HISTORICO NOT IN ('77','835','836','840','75','857','837','830','855','856','833','832','838','1074','1073')
AND B.CD_EMPRESA IN ('1','2')
AND B.DT_MOVIM between @DT_INICIAL and @DT_FINAL
GROUP BY B.NR_CTAPES, A.DS_TITULAR, B.TP_MANUTENCAO, B.DT_MOVIM, B.TP_DOCUMENTO, B.TP_OPERACAO, B.NR_NF, B.VL_LANCTO, B.CD_HISTORICO, D.VL_SALDOTOTAL
ORDER BY B.DT_MOVIM, B.TP_OPERACAO
这是一个有点厚读的,但我想你想要什么
sum(sum_column) over ([PARTITION BY group_column(s)] [order by order_column(s)]).
我想你应该停止试图累计现有查询里面,然后将其作为子查询括在圆括号中。选择从巨大的子查询所需的列,下面列添加要求你保护的运行总计您的列中挨着:
sum(COLUMN_OF_VALUES_THAT_NEED_TO_BE_CUMULATIVELY_TOTALED) over
(order by DT_MOVIM, TP_OPERACAO) as VL_SALDOTOTAL
我认为,即使这个特殊的表达是不行的,这是你正在寻找的功能。研究Oracle分析查询以获取更多帮助。祝你好运。 -Tom
SUM(SUM(D.VL_SALDOTOTAL + DECODE(B.TP_OPERACAO,' C',DECODE( B.TP_DOCUMENTO,'3',B.VL_LANCTO,0),0)) - SUM(DECODE(B. (B.TP_OPERACAO,'D',DECODE(B.TP_DOCUMENTO,'9',0)) - SUM(DECODE(B.TP_OPERACAO,'D',DECODE(D.TP_OPERACAO,'D' B.VL_LANCTO,0),0)))和VL_SALDOTOTAL一样,它实际上是累积VL_SALDOTOTAL,但是这个问题也是你提到的子查询的D.VL_SALDOTOTAL是482的固定值,并且每一行都被再次添加。 – kelsen
Are you sure 66,80 + 452,00 = 548,80 ??在什么基地? – mathguy
然后:请阅读并按照说明进行操作。 https://*.com/help/mcve如果你的问题是关于某一列的累计总和,为什么我们需要阅读一个四十行查询,有三个连接的表,谁知道有多少列?如果您需要帮助,请先做好自己的工作。 – mathguy
@mathguy抱歉,其482,00而不是452,00。 我不知道我怎么可以更清楚我的怀疑没有发布整个SQL查询。 – kelsen