比較兩個運行總計并在sql中更改一個的值

我已經調查了這里提出的所有問題/問題，但找不到類似的問題。我使用 sql developer，并且擁有 select 陳述句權限，無權訪問游標或回圈，也無權創建函式。

我有一個需要運行總計的兩串列：

with my_table as
(
select 673 as customer, to_date('30.06.2021','dd.mm.yyyy') as report_date,210 as fee,210 as commission from dual union all
select 673 as customer, to_date('31.07.2021','dd.mm.yyyy') as report_date,210 as fee,0 as commission from dual union all
select 673 as customer, to_date('31.08.2021','dd.mm.yyyy') as report_date,210 as fee,210 as commission from dual union all
select 673 as customer, to_date('31.10.2021','dd.mm.yyyy') as report_date,210 as fee,310 as commission from dual union all
select 673 as customer, to_date('30.11.2021','dd.mm.yyyy') as report_date,210 as fee,210 as commission from dual union all
select 673 as customer, to_date('31.12.2021','dd.mm.yyyy') as report_date,210 as fee,0  as commission from dual union all
select 673 as customer, to_date('31.01.2022','dd.mm.yyyy') as report_date,210 as fee, 943.08 as commission from dual union all
select 673 as customer, to_date('28.02.2022','dd.mm.yyyy') as report_date,320 as fee,236.6 as commission from dual union all
select 673 as customer, to_date('31.03.2022','dd.mm.yyyy') as report_date,320 as fee,0 as commission from dual union all
select 673 as customer, to_date('30.04.2022','dd.mm.yyyy') as report_date,320 as fee,0 as commission from dual union all
select 673 as customer, to_date('31.05.2022','dd.mm.yyyy') as report_date,320 as fee,0 as commission from dual union all
select 673 as customer, to_date('30.06.2022','dd.mm.yyyy') as report_date,320 as fee,0 as commission from dual union all
select 673 as customer, to_date('31.07.2022','dd.mm.yyyy') as report_date,320 as fee,0 as commission from dual union all
select 673 as customer, to_date('31.08.2022','dd.mm.yyyy') as report_date,320 as fee,0 as commission from dual
)

我必須計算費用和傭金列的總和。對于費用欄，沒有規則或條件。與磁區函式的基本總和就足夠了。但是，在傭金方面，我必須注意總費用。

每個 running_com 值都必須與 running_fee 的值進行比較。如果 running_com 超過 running_fee，則應在該行替換為 running_fee，對于下一行，傭金的累計總額應從該值開始。這是表格和預期結果：

我已經使用延遲放置了之前的傭金值，然后嘗試將傭金值和之前的傭金相加但無法做到，可以說是回圈部分。它只是總結而沒有運行部分。

感謝幫助。

uj5u.com熱心網友回復：

我不認為這只能用視窗函式來完成。無論如何，我們需要對資料集進行某種迭代，以便我們可以就累積傭金做出正確的決定。

在 SQL 中，這通常通過遞回查詢來完成。這是我們可以遵循的邏輯：

with  
    dat (customer, report_date, fee, commission, seq, r_fee) as (
        select customer, report_date, fee, commission,
            row_number() over(partition by customer order by report_date),
            sum(fee)     over(partition by customer order by report_date)
        from my_table
    ),
    rec (customer, report_date, fee, commission, seq, r_fee, r_commission) as (
        select d.customer, d.report_date, d.fee, d.commission, d.seq, d.r_fee, commission 
        from dat d 
        where seq = 1
        union all
        select d.customer, d.report_date, d.fee, d.commission, d.seq, d.r_fee, 
            least(r.r_commission   d.commission, d.r_fee) 
        from rec r
        inner join dat d on d.customer = r.customer and d.seq = r.seq   1 
    )
select * from rec order by customer, report_date

第一個公用表運算式（dat）只是列舉每個客戶的行（seq），并直接計算運行費用（r_fee）。

第二個 CTErec進行迭代，使用seq來從一步跳到下一步；在每一步，運行傭金都會根據業務規則進行更新。

這是一個關于 DB Fiddle 的演示，其中包含您的示例資料，它產生：

標籤：sql甲骨文窗函数递归查询累积和

上一篇：有人可以幫我處理oraclesql嗎，我遇到了一個錯誤

下一篇：如何創建一個oracle程序以從重復和更新中找出不同的記錄