一个取数问题

Question

  CREATE TABLE TAE_BDZH_TJ(F_ZHZDHSHL varchar2(20), F_ZHZDHBL varchar2(20), F_WGBCHRL varchar2(20), F_WGBCHBL varchar2(20), F_NUM varchar2(20),
  F_FQ varchar2(20), F_DYDJ varchar2(20), F_BDZHSHL varchar2(20), 
  F_ZHBTSH varchar2(20), F_RL varchar2(20), F_WRZHBSHL varchar2(20), F_WRZHBBL varchar2(20), CONSTRAINT PKEY PRIMARY KEY (F_NUM))
insert into tae_bdzh_tj values('2','100','0','0',299,'从化','220','2','4','660','2','100');
insert into tae_bdzh_tj values('13','100','0','0',300,'^UP','110','13','23','714.5','13','100');
insert into tae_bdzh_tj values('15','100','0','0',301,'^UP','小计','15','27','1374.5','15','100');
insert into tae_bdzh_tj values('1','100','0','0',302,'花都','500','1','2','3000','1','100');
insert into tae_bdzh_tj values('3','100','0','0',303,'^UP','220','3','6','1020','3','100');
insert into tae_bdzh_tj values('21','100','0','0',304,'^UP','110','21','41','1640','21','100');
insert into tae_bdzh_tj values('25','100','0','0',305,'^UP','小计','25','49','5660','25','100');
insert into tae_bdzh_tj values('3','100','0','0',306,'增城','220','3','7','1200','3','100');
insert into tae_bdzh_tj values('29','100','0','0',307,'^UP','110','29','63','2454.5','29','100');
insert into tae_bdzh_tj values('32','100','0','0',308,'^UP','小计','32','70','3654.5','32','100');
insert into tae_bdzh_tj values('1','100','0','0',309,'番禺','500','1','2','2000','1','100');
insert into tae_bdzh_tj values('7','100','0','0',310,'^UP','220','7','16','3420','7','100');
insert into tae_bdzh_tj values('39','100','0','0',311,'^UP','110','39','92','4161','39','100');
insert into tae_bdzh_tj values('47','100','0','0',312,'^UP','小计','47','110','9581','47','100');
insert into tae_bdzh_tj values('5','100','0','0',313,'荔湾','220','5','10','1860','5','100');
insert into tae_bdzh_tj values('12','100','0','0',314,'^UP','110','12','26','1129','12','100');
insert into tae_bdzh_tj values('17','100','0','0',315,'^UP','小计','17','36','2989','17','100');
insert into tae_bdzh_tj values('2','100','0','0',316,'海珠','220','2','6','1080','2','100');
insert into tae_bdzh_tj values('6','100','0','0',317,'^UP','110','6','14','626','6','100');
insert into tae_bdzh_tj values('8','100','0','0',318,'^UP','小计','8','20','1706','8','100');
insert into tae_bdzh_tj values('8','100','0','0',319,'越秀','110','8','22','946','8','100');
insert into tae_bdzh_tj values('8','100','0','0',320,'^UP','小计','8','22','946','8','100');
insert into tae_bdzh_tj values('3','100','0','0',321,'天河','220','3','9','1620','3','100');
insert into tae_bdzh_tj values('11','100','0','0',322,'^UP','110','11','30','1519','11','100');
insert into tae_bdzh_tj values('14','100','0','0',323,'^UP','小计','14','39','3139','14','100');
insert into tae_bdzh_tj values('1','100','0','0',324,'黄埔','220','1','2','300','1','100');
insert into tae_bdzh_tj values('5','100','0','0',325,'^UP','110','5','12','500','5','100');
insert into tae_bdzh_tj values('6','100','0','0',326,'^UP','小计','6','14','800','6','100');
insert into tae_bdzh_tj values('1','100','0','0',327,'萝岗','500','1','3','2250','1','100');
insert into tae_bdzh_tj values('2','100','0','0',328,'^UP','220','2','5','960','2','100');
insert into tae_bdzh_tj values('6','100','0','0',329,'^UP','110','6','14','600','6','100');
insert into tae_bdzh_tj values('9','100','0','0',330,'^UP','小计','9','22','3810','9','100');
insert into tae_bdzh_tj values('1','100','0','0',331,'白云','500','1','3','2250','1','100');
insert into tae_bdzh_tj values('9','100','0','0',332,'^UP','220','9','20','4020','9','100');
insert into tae_bdzh_tj values('24','100','0','0',333,'^UP','110','24','58','2322.6','24','100');
insert into tae_bdzh_tj values('34','100','0','0',334,'^UP','小计','34','81','8592.6','34','100');
insert into tae_bdzh_tj values('4','100','0','0',335,'小计','500','4','10','9500','4','100');
insert into tae_bdzh_tj values('37','100','0','0',336,'^UP','220','37','85','16140','37','100');
insert into tae_bdzh_tj values('174','100','0','0',337,'^UP','110','174','395','16612.6','174','100');
insert into tae_bdzh_tj values('215','100','0','0',338,'合计','^LEFT','215','490','42252.6','215','100');
用组织数据，不排序， F_FQ 字段，值为'^UP'的记录都为其上面的不为'^UP'的记录。
比如：
insert into tae_bdzh_tj values('2','100','0','0',299,'从化','220','2','4','660','2','100');
insert into tae_bdzh_tj values('13','100','0','0',300,'^UP','110','13','23','714.5','13','100');
insert into tae_bdzh_tj values('15','100','0','0',301,'^UP','小计','15','27','1374.5','15','100');两个'^UP'都取为'从化'。

Answer 1

with t as(
select 324 id,'黄埔' name,'220' cc from dual
union all select 325,'^UP','110' from dual
union all select 326,'^UP','小计' from dual
union all select 327,'萝岗','500' from dual
union all select 328,'^UP','220' from dual
union all select 329,'^UP','110' from dual
union all select 330,'^UP','小计' from dual
)
update t t1 set name = (select name from t where t.id+1 =t1.id) where name ='^UP';
update t t1 set name = (select name from t where t.id+2 =t1.id) where name ='^UP';
update t t1 set name = (select name from t where t.id+3 =t1.id) where name ='^UP';

Answer 2

drop table t;
create table  t as(
select 324 id,'黄埔' name,'220' cc from dual
union all select 325,'^UP','110' from dual
union all select 326,'^UP','小计' from dual
union all select 327,'萝岗','500' from dual
union all select 328,'^UP','220' from dual
union all select 329,'^UP','110' from dual
union all select 330,'^UP','小计' from dual
);
update t t1 set name = (select name from t where t.id+1 =t1.id) where name ='^UP';
update t t1 set name = (select name from t where t.id+2 =t1.id) where name ='^UP';
update t t1 set name = (select name from t where t.id+3 =t1.id) where name ='^UP';
commit;
select * from t;

Answer 3

--引用楼主提供的资料建测试表和加入测试数据
CREATE TABLE TAE_BDZH_TJ(F_ZHZDHSHL varchar2(20), F_ZHZDHBL varchar2(20), F_WGBCHRL varchar2(20), F_WGBCHBL varchar2(20), F_NUM varchar2(20),
  F_FQ varchar2(20), F_DYDJ varchar2(20), F_BDZHSHL varchar2(20), 
  F_ZHBTSH varchar2(20), F_RL varchar2(20), F_WRZHBSHL varchar2(20), F_WRZHBBL varchar2(20), CONSTRAINT PKEY PRIMARY KEY (F_NUM))
insert into tae_bdzh_tj values('2','100','0','0',299,'从化','220','2','4','660','2','100');
insert into tae_bdzh_tj values('13','100','0','0',300,'^UP','110','13','23','714.5','13','100');
insert into tae_bdzh_tj values('15','100','0','0',301,'^UP','小计','15','27','1374.5','15','100');
insert into tae_bdzh_tj values('1','100','0','0',302,'花都','500','1','2','3000','1','100');
insert into tae_bdzh_tj values('3','100','0','0',303,'^UP','220','3','6','1020','3','100');
insert into tae_bdzh_tj values('21','100','0','0',304,'^UP','110','21','41','1640','21','100');
insert into tae_bdzh_tj values('25','100','0','0',305,'^UP','小计','25','49','5660','25','100');
insert into tae_bdzh_tj values('3','100','0','0',306,'增城','220','3','7','1200','3','100');
insert into tae_bdzh_tj values('29','100','0','0',307,'^UP','110','29','63','2454.5','29','100');
insert into tae_bdzh_tj values('32','100','0','0',308,'^UP','小计','32','70','3654.5','32','100');
insert into tae_bdzh_tj values('1','100','0','0',309,'番禺','500','1','2','2000','1','100');
insert into tae_bdzh_tj values('7','100','0','0',310,'^UP','220','7','16','3420','7','100');
insert into tae_bdzh_tj values('39','100','0','0',311,'^UP','110','39','92','4161','39','100');
insert into tae_bdzh_tj values('47','100','0','0',312,'^UP','小计','47','110','9581','47','100');
insert into tae_bdzh_tj values('5','100','0','0',313,'荔湾','220','5','10','1860','5','100');
insert into tae_bdzh_tj values('12','100','0','0',314,'^UP','110','12','26','1129','12','100');
insert into tae_bdzh_tj values('17','100','0','0',315,'^UP','小计','17','36','2989','17','100');
insert into tae_bdzh_tj values('2','100','0','0',316,'海珠','220','2','6','1080','2','100');
insert into tae_bdzh_tj values('6','100','0','0',317,'^UP','110','6','14','626','6','100');
insert into tae_bdzh_tj values('8','100','0','0',318,'^UP','小计','8','20','1706','8','100');
insert into tae_bdzh_tj values('8','100','0','0',319,'越秀','110','8','22','946','8','100');
insert into tae_bdzh_tj values('8','100','0','0',320,'^UP','小计','8','22','946','8','100');
insert into tae_bdzh_tj values('3','100','0','0',321,'天河','220','3','9','1620','3','100');
insert into tae_bdzh_tj values('11','100','0','0',322,'^UP','110','11','30','1519','11','100');
insert into tae_bdzh_tj values('14','100','0','0',323,'^UP','小计','14','39','3139','14','100');
insert into tae_bdzh_tj values('1','100','0','0',324,'黄埔','220','1','2','300','1','100');
insert into tae_bdzh_tj values('5','100','0','0',325,'^UP','110','5','12','500','5','100');
insert into tae_bdzh_tj values('6','100','0','0',326,'^UP','小计','6','14','800','6','100');
insert into tae_bdzh_tj values('1','100','0','0',327,'萝岗','500','1','3','2250','1','100');
insert into tae_bdzh_tj values('2','100','0','0',328,'^UP','220','2','5','960','2','100');
insert into tae_bdzh_tj values('6','100','0','0',329,'^UP','110','6','14','600','6','100');
insert into tae_bdzh_tj values('9','100','0','0',330,'^UP','小计','9','22','3810','9','100');
insert into tae_bdzh_tj values('1','100','0','0',331,'白云','500','1','3','2250','1','100');
insert into tae_bdzh_tj values('9','100','0','0',332,'^UP','220','9','20','4020','9','100');
insert into tae_bdzh_tj values('24','100','0','0',333,'^UP','110','24','58','2322.6','24','100');
insert into tae_bdzh_tj values('34','100','0','0',334,'^UP','小计','34','81','8592.6','34','100');
insert into tae_bdzh_tj values('4','100','0','0',335,'小计','500','4','10','9500','4','100');
insert into tae_bdzh_tj values('37','100','0','0',336,'^UP','220','37','85','16140','37','100');
insert into tae_bdzh_tj values('174','100','0','0',337,'^UP','110','174','395','16612.6','174','100');
insert into tae_bdzh_tj values('215','100','0','0',338,'合计','^LEFT','215','490','42252.6','215','100');
--执行批量修改的存储过程
create or replace procedure proc_test
as
minF_num number;
maxF_num number;
theValue varchar2(20);
totalNum number;
begin
  maxF_num:=0;
  minF_num:=0;
  totalNum:=2;
  while totalNum>1 loop
  dbms_output.put_line('totalNum'||totalNum);
  dbms_output.put_line('minF_num'||minF_num);
  dbms_output.put_line('maxF_num'||maxF_num);
  dbms_output.put_line('theValue'||theValue);
  dbms_output.put_line('totalNum'||totalNum);
  --获取开始点
  select min(f_num) into minF_num from tae_bdzh_tj where f_fq !='^UP' and f_num>=maxF_num; 
  --获取结束点
  select min(f_num) into maxF_num from tae_bdzh_tj where f_fq !='^UP' and f_num>minF_num;
  --需要更改的值
  select f_fq into theValue from tae_bdzh_tj where f_num=minF_num;
  --更改值
  update tae_bdzh_tj set f_fq=theValue where f_num>minF_num and f_num<maxF_num;
  commit;
  --获取总行数
  select count(1) into totalNum from tae_bdzh_tj where f_num>=maxF_num;
  end loop;
end;
--执行存储过程
begin
  -- Call the procedure
  proc_test;
end;
--执行后的数据结果
2 100 0 0 299 从化 220 2 4 660 2 100
13 100 0 0 300 从化 110 13 23 714.5 13 100
15 100 0 0 301 从化 小计 15 27 1374.5 15 100
1 100 0 0 302 花都 500 1 2 3000 1 100
3 100 0 0 303 花都 220 3 6 1020 3 100
21 100 0 0 304 花都 110 21 41 1640 21 100
25 100 0 0 305 花都 小计 25 49 5660 25 100
3 100 0 0 306 增城 220 3 7 1200 3 100
29 100 0 0 307 增城 110 29 63 2454.5 29 100
32 100 0 0 308 增城 小计 32 70 3654.5 32 100
1 100 0 0 309 番禺 500 1 2 2000 1 100
7 100 0 0 310 番禺 220 7 16 3420 7 100
39 100 0 0 311 番禺 110 39 92 4161 39 100
47 100 0 0 312 番禺 小计 47 110 9581 47 100
5 100 0 0 313 荔湾 220 5 10 1860 5 100
12 100 0 0 314 荔湾 110 12 26 1129 12 100
17 100 0 0 315 荔湾 小计 17 36 2989 17 100
2 100 0 0 316 海珠 220 2 6 1080 2 100
6 100 0 0 317 海珠 110 6 14 626 6 100
8 100 0 0 318 海珠 小计 8 20 1706 8 100
8 100 0 0 319 越秀 110 8 22 946 8 100
8 100 0 0 320 越秀 小计 8 22 946 8 100
3 100 0 0 321 天河 220 3 9 1620 3 100
11 100 0 0 322 天河 110 11 30 1519 11 100
14 100 0 0 323 天河 小计 14 39 3139 14 100
1 100 0 0 324 黄埔 220 1 2 300 1 100
5 100 0 0 325 黄埔 110 5 12 500 5 100
6 100 0 0 326 黄埔 小计 6 14 800 6 100
1 100 0 0 327 萝岗 500 1 3 2250 1 100
2 100 0 0 328 萝岗 220 2 5 960 2 100
6 100 0 0 329 萝岗 110 6 14 600 6 100
9 100 0 0 330 萝岗 小计 9 22 3810 9 100
1 100 0 0 331 白云 500 1 3 2250 1 100
9 100 0 0 332 白云 220 9 20 4020 9 100
24 100 0 0 333 白云 110 24 58 2322.6 24 100
34 100 0 0 334 白云 小计 34 81 8592.6 34 100
4 100 0 0 335 小计 500 4 10 9500 4 100
37 100 0 0 336 小计 220 37 85 16140 37 100
174 100 0 0 337 小计 110 174 395 16612.6 174 100
215 100 0 0 338 合计 ^LEFT 215 490 42252.6 215 100

Answer 4

--ps:我的可以做到你的汉字下面任意数量的up都能修改，你可以在你原有的数据基础上再加数据测试

Answer 5

谢谢4楼5楼的回答，不想update ，只想返回这样的结果。

Answer 6

with tb as (
select top 1 1 as num,rank() over(order by f_num desc) as row,* from tae_bdzh_tj
union all
select cast(b.f_num as int)-cast(a.f_num as int) as num,a.* from (select rank() over(order by f_num desc) as row,* from tae_bdzh_tj where f_fq<>'^UP') a join tb b
on a.row-1=b.row
)select (select f_fq from tb where a.f_num >=f_num and a.f_num<f_num+num),* from tae_bdzh_tj a 
从化 2 100 0 0 299 从化 220 2 4 660 2 100
从化 13 100 0 0 300 ^UP 110 13 23 714.5 13 100
从化 15 100 0 0 301 ^UP 小计 15 27 1374.5 15 100
花都 1 100 0 0 302 花都 500 1 2 3000 1 100
花都 3 100 0 0 303 ^UP 220 3 6 1020 3 100
花都 21 100 0 0 304 ^UP 110 21 41 1640 21 100
花都 25 100 0 0 305 ^UP 小计 25 49 5660 25 100
增城 3 100 0 0 306 增城 220 3 7 1200 3 100
增城 29 100 0 0 307 ^UP 110 29 63 2454.5 29 100
增城 32 100 0 0 308 ^UP 小计 32 70 3654.5 32 100
番禺 1 100 0 0 309 番禺 500 1 2 2000 1 100
番禺 7 100 0 0 310 ^UP 220 7 16 3420 7 100
番禺 39 100 0 0 311 ^UP 110 39 92 4161 39 100
番禺 47 100 0 0 312 ^UP 小计 47 110 9581 47 100
荔湾 5 100 0 0 313 荔湾 220 5 10 1860 5 100
荔湾 12 100 0 0 314 ^UP 110 12 26 1129 12 100
荔湾 17 100 0 0 315 ^UP 小计 17 36 2989 17 100
海珠 2 100 0 0 316 海珠 220 2 6 1080 2 100
海珠 6 100 0 0 317 ^UP 110 6 14 626 6 100
海珠 8 100 0 0 318 ^UP 小计 8 20 1706 8 100
越秀 8 100 0 0 319 越秀 110 8 22 946 8 100
越秀 8 100 0 0 320 ^UP 小计 8 22 946 8 100
天河 3 100 0 0 321 天河 220 3 9 1620 3 100
天河 11 100 0 0 322 ^UP 110 11 30 1519 11 100
天河 14 100 0 0 323 ^UP 小计 14 39 3139 14 100
黄埔 1 100 0 0 324 黄埔 220 1 2 300 1 100
黄埔 5 100 0 0 325 ^UP 110 5 12 500 5 100
黄埔 6 100 0 0 326 ^UP 小计 6 14 800 6 100
萝岗 1 100 0 0 327 萝岗 500 1 3 2250 1 100
萝岗 2 100 0 0 328 ^UP 220 2 5 960 2 100
萝岗 6 100 0 0 329 ^UP 110 6 14 600 6 100
萝岗 9 100 0 0 330 ^UP 小计 9 22 3810 9 100
白云 1 100 0 0 331 白云 500 1 3 2250 1 100
白云 9 100 0 0 332 ^UP 220 9 20 4020 9 100
白云 24 100 0 0 333 ^UP 110 24 58 2322.6 24 100
白云 34 100 0 0 334 ^UP 小计 34 81 8592.6 34 100
小计 4 100 0 0 335 小计 500 4 10 9500 4 100
小计 37 100 0 0 336 ^UP 220 37 85 16140 37 100
小计 174 100 0 0 337 ^UP 110 174 395 16612.6 174 100
合计 215 100 0 0 338 合计 ^LEFT 215 490 42252.6 215 100

Answer 7

辛苦辛苦，表是没有主键的，不能用f_num 这个字段的
另外with tb as (
select top 1 1 as num,rank() over(order by f_num desc) as row,* from tae_bdzh_tj
union all
select cast(b.f_num as int)-cast(a.f_num as int) as num,a.* from (select rank() over(order by f_num desc) as row,* from tae_bdzh_tj where f_fq<>'^UP') a join tb b
on a.row-1=b.row
)select (select f_fq from tb where a.f_num >=f_num and a.f_num<f_num+num),* from tae_bdzh_tj a 
这个语法有错误吧？运行不了

Answer 8

--我的完成了，应你的要求不修改数据，只查询
--我这里所用的还是之前我说明的测试表测试数据。select
  v1.F_ZHZDHSHL,
  v1.F_ZHZDHBL,
  v1.F_WGBCHRL,
  v1.F_WGBCHBL,
  v1.F_NUM,
  (case
    when (select count(1) from tae_bdzh_tj where v1.f_fq=f_num)=0
    then v1.f_fq
    when (select count(1) from tae_bdzh_tj where v1.f_fq=f_num)>0
    then (select f_fq from tae_bdzh_tj where v1.f_fq=f_num)
  end) f_fq,
  v1.F_DYDJ,
  v1.F_BDZHSHL,
  v1.F_ZHBTSH,
  v1.F_RL,
  v1.F_WRZHBSHL,
  v1.F_WRZHBBL  
from(select 
      t.F_ZHZDHSHL,
      t.F_ZHZDHBL,
      t.F_WGBCHRL,
      t.F_WGBCHBL,
      t.F_NUM,
      (
      case
          when t.f_fq ='^UP'
          then (select max(s.f_num)
                from tae_bdzh_tj s 
                where s.f_num<t.f_num
                and s.f_fq !='^UP'
               )
          when t.f_fq !='^UP'
          then t.f_fq
      end
      ) as f_fq,
      t.F_DYDJ,
      t.F_BDZHSHL,
      t.F_ZHBTSH,
      t.F_RL,
      t.F_WRZHBSHL,
      t.F_WRZHBBL
      from tae_bdzh_tj t) v1
--数据结果
2 100 0 0 299 从化 220 2 4 660 2 100
13 100 0 0 300 从化 110 13 23 714.5 13 100
15 100 0 0 301 从化 小计 15 27 1374.5 15 100
1 100 0 0 302 花都 500 1 2 3000 1 100
3 100 0 0 303 花都 220 3 6 1020 3 100
21 100 0 0 304 花都 110 21 41 1640 21 100
25 100 0 0 305 花都 小计 25 49 5660 25 100
3 100 0 0 306 增城 220 3 7 1200 3 100
29 100 0 0 307 增城 110 29 63 2454.5 29 100
32 100 0 0 308 增城 小计 32 70 3654.5 32 100
1 100 0 0 309 番禺 500 1 2 2000 1 100
7 100 0 0 310 番禺 220 7 16 3420 7 100
39 100 0 0 311 番禺 110 39 92 4161 39 100
47 100 0 0 312 番禺 小计 47 110 9581 47 100
5 100 0 0 313 荔湾 220 5 10 1860 5 100
12 100 0 0 314 荔湾 110 12 26 1129 12 100
17 100 0 0 315 荔湾 小计 17 36 2989 17 100
2 100 0 0 316 海珠 220 2 6 1080 2 100
6 100 0 0 317 海珠 110 6 14 626 6 100
8 100 0 0 318 海珠 小计 8 20 1706 8 100
8 100 0 0 319 越秀 110 8 22 946 8 100
8 100 0 0 320 越秀 小计 8 22 946 8 100
3 100 0 0 321 天河 220 3 9 1620 3 100
11 100 0 0 322 天河 110 11 30 1519 11 100
14 100 0 0 323 天河 小计 14 39 3139 14 100
1 100 0 0 324 黄埔 220 1 2 300 1 100
5 100 0 0 325 黄埔 110 5 12 500 5 100
6 100 0 0 326 黄埔 小计 6 14 800 6 100
1 100 0 0 327 萝岗 500 1 3 2250 1 100
2 100 0 0 328 萝岗 220 2 5 960 2 100
6 100 0 0 329 萝岗 110 6 14 600 6 100
9 100 0 0 330 萝岗 小计 9 22 3810 9 100
1 100 0 0 331 白云 500 1 3 2250 1 100
9 100 0 0 332 白云 220 9 20 4020 9 100
24 100 0 0 333 白云 110 24 58 2322.6 24 100
34 100 0 0 334 白云 小计 34 81 8592.6 34 100
4 100 0 0 335 小计 500 4 10 9500 4 100
37 100 0 0 336 小计 220 37 85 16140 37 100
174 100 0 0 337 小计 110 174 395 16612.6 174 100
215 100 0 0 338 合计 ^LEFT 215 490 42252.6 215 100
--ps:描述问题要清楚点，浪费我那么多精力，最后还是帮你搞定了，要下班了，再有什么要求我就不管了

Answer 9

如果没有么个字段，恐怕不好做 真的如果没有，就建个临时表 加一个这样的字段 就可以。
我执行了没问题的 sql server

Answer 10

--我的貌似没问题吧？
--还有这里貌似是Oracle板块