oracle 三列数值相加,Oracle SQL/PLSQL:按货币拆分和求和值的分层查询

恐怕你把大家都搞糊涂了。)

虽然你的要求部分难以理解,但我认为如果我必须处理这类任务,我会做一件事。我将编写递归函数,计算从树的任何部分到叶的成本。

以下是与您类似的数据演示:

select prod.*, level, sys_connect_by_path(seq, '->') path,

calc_cost(comp) total

from prod connect by prior comp = part

start with base = 1;

SEQ PART COMP QTY COST CURR BASE AFO RFO LEVEL PATH TOTAL

------ ---- ---- ---------- ---------- ---- ---------- --- --- ------- ----------- ----------

1 A A1 5 1 1 ->1 850

2 A1 A11 3 0 B 2 ->1->2 114

3 A11 A111 4 2 EUR 0 B;D 3 ->1->2->3 8

4 A11 A112 2 15 EUR 0 3 ->1->2->4 30

5 A1 A12 8 7 EUR 0 2 ->1->5 56

11 B B1 5 1 1 ->11 870

12 B1 B11 3 0 2 ->11->12 174

13 B11 B111 4 12 GBP 0 3 ->11->12->13 48

14 B11 B112 2 5 GBP 0 3 ->11->12->14 10

柱

total

包含组件成本,例如

B1

它是

5 * (3 * (4 * 12 + 2 * 5))

哪个是

870

.

函数和示例数据如下:

create or replace function calc_cost(i_comp in varchar2) return number is

v_qty number := 0;

v_cost number := 0;

begin

select qty, cost into v_qty, v_cost from prod where comp = i_comp;

if v_cost is null then

select sum(calc_cost(comp)) into v_cost from prod where part = i_comp;

end if;

return v_qty * v_cost;

exception when no_data_found then

return 0;

end;

数据:

create table prod(seq, part, comp, qty, cost, curr, base, afo, rfo) as (

select 1, 'A', 'A1', 5, null, null, 1, null, null from dual union all

select 2, 'A1', 'A11', 3, null, null, 0, 'B', null from dual union all

select 3, 'A11', 'A111', 4, 2, 'EUR', 0, null, 'B;D' from dual union all

select 4, 'A11', 'A112', 2, 15, 'EUR', 0, null, null from dual union all

select 5, 'A1', 'A12', 8, 7, 'EUR', 0, null, null from dual union all

select 11, 'B', 'B1', 5, null, null, 1, null, null from dual union all

select 12, 'B1', 'B11', 3, null, null, 0, null, null from dual union all

select 13, 'B11', 'B111', 4, 12, 'GBP', 0, null, null from dual union all

select 14, 'B11', 'B112', 2, 5, 'GBP', 0, null, null from dual );

您没有指定相同的货币是否可以有不同的货币

part

/

component

如果是这样的话,输出会是怎样的。不管怎样,你可以找到这些货币,然后独立地计算每种货币。您需要向函数添加第二个参数,并编写类似

where part = i_comp and curr = i_curr or curr is null

.

也适用于

ask_for_option

/

remove_for_option

你也许可以在

case when

.

我看到你在这个问题上付出了很大的努力,但在目前的问题形式下,很难更好地回答。您应该提供示例数据,而不仅仅是图像,并根据用户的选择向我们准确显示您期望的输出。

但我希望这个函数可以帮助您解决这个问题。我假设如果

cost

不为空,则我们位于叶中,否则函数将递归查找子组件。

编辑:

假设seq=14是欧元而不是英镑。

update prod set curr = 'EUR' where seq = 14;

正如我所说,精确的解决方案取决于您需要的输出。如果您知道所有可能的货币,那么您可以修改函数来处理货币和显示成本,如下所示:

create or replace function calc_cost(i_comp in varchar2, i_curr in varchar2)

return number is

v_qty number := 0;

v_cost number := 0;

begin

select qty, cost into v_qty, v_cost from prod

where comp = i_comp and (curr = i_curr or curr is null);

if v_cost is null then

select sum(calc_cost(comp, i_curr)) into v_cost

from prod where part = i_comp and (curr = i_curr or curr is null);

end if;

return v_qty * nvl(v_cost, 0);

exception when no_data_found then

return 0;

end;

select seq, part, comp, qty, cost, curr,

calc_cost(comp, 'EUR') eur, calc_cost(comp, 'GBP') gbp

from prod

connect by part = prior comp

start with part = 'B';

SEQ PART COMP QTY COST CURR EUR GBP

----- ---- ---- ---------- ---------- ---- ---------- ----------

11 B B1 5 150 720

12 B1 B11 3 30 144

13 B11 B111 4 12 GBP 0 48

14 B11 B112 2 5 EUR 10 0

部分

B

成本为150欧元和720英镑。

您可以在数据的有趣部分中找到所有不同的货币,将它们与表连接起来,然后这样调用函数。结果是每个人

seq

你可以得到和不同货币一样多的行。然后你可以用

listagg()

和现值

150 EUR; 720 GBP

在一个单元格中。

您还可以创建一些类型对象并修改函数,以返回元组表(货币,成本单位为欧元)。问题是您希望如何显示数据。或者可以将值转换为通用货币,但为此需要每日的比率表。