我有桌子t1
ID NAME AGE GENDER BALANCE ----- ----- --------- ----- --------- 1001 John 10 M 10 1002 Meena 5 F 0 1003 Nikh 11 M 0 1004 divs 7 F 0 1005 neha 4 F 0
从第二行开始，如果性别为M，则余额（第二行）应为age（2）+ balance（1）
否则Balance（1）-age（2）
最终结构应该像
ID NAME AGE GENDER BALANCE ----- ----- --------- ----- --------- 1001 John 10 M 10 1002 Meena 5 F 5 1003 Nikh 11 M 16 1004 divs 7 F 9 1005 neha 4 F 5
请帮助我查询/过程
这样的事情怎么样？
with sample_data as (select 1001 id, 'John' name, 10 age, 'M' gender, 10 balance from dual union all select 1002 id, 'Meena' name, 5 age, 'F' gender, 0 balance from dual union all select 1003 id, 'Nikh' name, 11 age, 'M' gender, 0 balance from dual union all select 1004 id, 'divs' name, 7 age, 'F' gender, 0 balance from dual union all select 1005 id, 'neha' name, 4 age, 'F' gender, 0 balance from dual) select id, name, age, gender, sum(case when gender = 'F' then -1 * age else age end) over (order by id) balance from sample_data; ID NAME AGE GENDER BALANCE ---------- ----- ---------- ------ ---------- 1001 John 10 M 10 1002 Meena 5 F 5 1003 Nikh 11 M 16 1004 divs 7 F 9 1005 neha 4 F 5
我猜想第一行的余额（这里是id的顺序）是10，因为那是John的年龄，他是男性，而不是一个任意数字。
预计到达时间：这是上述解决方案的替代方案。我 强烈 建议您针对类似生产的数据量测试所有内容（在这里我使用了with子句来模拟一个具有5行的名为sample_data的表，您只需要使用您的表即可）。这样，您可以获得的时间安排应突出显示您的方案中性能最高的方法。希望您的经理不会对事实视而不见（如果他是事实，请逃之fast。
1）没有解析功能的SQL语句：
with sample_data as (select 1001 id, 'John' name, 10 age, 'M' gender, 10 balance from dual union all select 1002 id, 'Meena' name, 5 age, 'F' gender, 0 balance from dual union all select 1003 id, 'Nikh' name, 11 age, 'M' gender, 0 balance from dual union all select 1004 id, 'divs' name, 7 age, 'F' gender, 0 balance from dual union all select 1005 id, 'neha' name, 4 age, 'F' gender, 0 balance from dual) select sd1.id, sd1.name, sd1.age, sd1.gender, sum(case when sd2.gender = 'F' then -1 * sd2.age else sd2.age end) balance from sample_data sd1 inner join sample_data sd2 on (sd1.id >= sd2.id) group by sd1.id, sd1.name, sd1.age, sd1.gender order by id; ID NAME AGE GENDER BALANCE ---------- ----- ---------- ------ ---------- 1001 John 10 M 10 1002 Meena 5 F 5 1003 Nikh 11 M 16 1004 divs 7 F 9 1005 neha 4 F 5
2）程序性方法（慢速逐行{打哈欠}）（不推荐）：
create or replace procedure calc_balance1 as v_balance number := 0; cursor cur is with sample_data as (select 1001 id, 'John' name, 10 age, 'M' gender, 10 balance from dual union all select 1002 id, 'Meena' name, 5 age, 'F' gender, 0 balance from dual union all select 1003 id, 'Nikh' name, 11 age, 'M' gender, 0 balance from dual union all select 1004 id, 'divs' name, 7 age, 'F' gender, 0 balance from dual union all select 1005 id, 'neha' name, 4 age, 'F' gender, 0 balance from dual) select id, name, age, gender, balance from sample_data; begin for rec in cur loop v_balance := v_balance + case when rec.gender = 'F' then -1 * rec.age else rec.age end; dbms_output.put_line('id = '||rec.id||', name = '||rec.name||', age = '||rec.age||', gender = '||rec.gender||', balance = '||v_balance); end loop; end calc_balance1; / begin calc_balance; end; / id = 1001, name = John, age = 10, gender = M, balance = 10 id = 1002, name = Meena, age = 5, gender = F, balance = 5 id = 1003, name = Nikh, age = 11, gender = M, balance = 16 id = 1004, name = divs, age = 7, gender = F, balance = 9 id = 1005, name = neha, age = 4, gender = F, balance = 5
但是，如果您必须为此提供一个过程，则可以将查询与分析函数一起使用，并将其粘贴在ref游标中，例如：
create or replace procedure calc_balance2 (p_refcur out sys_refcursor) as begin open p_refcur for with sample_data as (select 1001 id, 'John' name, 10 age, 'M' gender, 10 balance from dual union all select 1002 id, 'Meena' name, 5 age, 'F' gender, 0 balance from dual union all select 1003 id, 'Nikh' name, 11 age, 'M' gender, 0 balance from dual union all select 1004 id, 'divs' name, 7 age, 'F' gender, 0 balance from dual union all select 1005 id, 'neha' name, 4 age, 'F' gender, 0 balance from dual) select id, name, age, gender, sum(case when gender = 'F' then -1 * age else age end) over (order by id) balance from sample_data order by id; end calc_balance2; /
我看到了你写的程序；这是我的替代方法：
-- mimicking your test_divs table: create table test_divs as select 1001 id, 'John' name, 10 age, 'M' gender, 10 balance from dual union all select 1002 id, 'Meena' name, 5 age, 'F' gender, 0 balance from dual union all select 1003 id, 'Nikh' name, 11 age, 'M' gender, 0 balance from dual union all select 1004 id, 'divs' name, 7 age, 'F' gender, 0 balance from dual union all select 1005 id, 'neha' name, 4 age, 'F' gender, 0 balance from dual; create or replace procedure t1_d12_v2 as begin merge into test_divs tgt using (select id, name, age, gender, sum(case when gender = 'F' then -1 * age else age end) over (order by id) balance from test_divs) src on (tgt.id = src.id) when matched then update set tgt.balance = src.balance; end t1_d12_v2; / select * from test_divs; ID NAME AGE GENDER BALANCE ---------- ----- ---------- ------ ---------- 1001 John 10 M 10 1002 Meena 5 F 0 1003 Nikh 11 M 0 1004 divs 7 F 0 1005 neha 4 F 0 begin t1_d12_v2; commit; end; / select * from test_divs; ID NAME AGE GENDER BALANCE ---------- ----- ---------- ------ ---------- 1001 John 10 M 10 1002 Meena 5 F 5 1003 Nikh 11 M 16 1004 divs 7 F 9 1005 neha 4 F 5
我 强烈 建议您使用类似生产的数据测试这两种方法，然后看看哪种方法效果更好。（如果您的经理真的对分析函数毫无用处，那么我会将“ src”子查询交换为我想到的另一个sql语句-一个带有join和group by的语句。）
像您所做的那样逐行进行更新，每次循环时，都会在sql和pl / sql之间进行两次上下文切换。当您可以在单个sql语句中完成全部操作时，何必麻烦您呢？严重地。
