一个取数有关问题

2012-06-21

一个取数问题SQL codeCREATE TABLE TAE_BDZH_TJ(F_ZHZDHSHL varchar2(20), F_ZHZDHBL varchar2(20), F_WGB

一个取数问题

SQL code

  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'都取为'从化'。

[解决办法]

SQL code

--引用楼主提供的资料建测试表和加入测试数据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_testasminF_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 10013 100 0 0 300 从化 110 13 23 714.5 13 10015 100 0 0 301 从化小计 15 27 1374.5 15 1001 100 0 0 302 花都 500 1 2 3000 1 1003 100 0 0 303 花都 220 3 6 1020 3 10021 100 0 0 304 花都 110 21 41 1640 21 10025 100 0 0 305 花都小计 25 49 5660 25 1003 100 0 0 306 增城 220 3 7 1200 3 10029 100 0 0 307 增城 110 29 63 2454.5 29 10032 100 0 0 308 增城小计 32 70 3654.5 32 1001 100 0 0 309 番禺 500 1 2 2000 1 1007 100 0 0 310 番禺 220 7 16 3420 7 10039 100 0 0 311 番禺 110 39 92 4161 39 10047 100 0 0 312 番禺小计 47 110 9581 47 1005 100 0 0 313 荔湾 220 5 10 1860 5 10012 100 0 0 314 荔湾 110 12 26 1129 12 10017 100 0 0 315 荔湾小计 17 36 2989 17 1002 100 0 0 316 海珠 220 2 6 1080 2 1006 100 0 0 317 海珠 110 6 14 626 6 1008 100 0 0 318 海珠小计 8 20 1706 8 1008 100 0 0 319 越秀 110 8 22 946 8 1008 100 0 0 320 越秀小计 8 22 946 8 1003 100 0 0 321 天河 220 3 9 1620 3 10011 100 0 0 322 天河 110 11 30 1519 11 10014 100 0 0 323 天河小计 14 39 3139 14 1001 100 0 0 324 黄埔 220 1 2 300 1 1005 100 0 0 325 黄埔 110 5 12 500 5 1006 100 0 0 326 黄埔小计 6 14 800 6 1001 100 0 0 327 萝岗 500 1 3 2250 1 1002 100 0 0 328 萝岗 220 2 5 960 2 1006 100 0 0 329 萝岗 110 6 14 600 6 1009 100 0 0 330 萝岗小计 9 22 3810 9 1001 100 0 0 331 白云 500 1 3 2250 1 1009 100 0 0 332 白云 220 9 20 4020 9 10024 100 0 0 333 白云 110 24 58 2322.6 24 10034 100 0 0 334 白云小计 34 81 8592.6 34 1004 100 0 0 335 小计 500 4 10 9500 4 10037 100 0 0 336 小计 220 37 85 16140 37 100174 100 0 0 337 小计 110 174 395 16612.6 174 100215 100 0 0 338 合计 ^LEFT 215 490 42252.6 215 100

[解决办法]

SQL code

--我的完成了，应你的要求不修改数据，只查询--我这里所用的还是之前我说明的测试表测试数据。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 10013 100 0 0 300 从化 110 13 23 714.5 13 10015 100 0 0 301 从化小计 15 27 1374.5 15 1001 100 0 0 302 花都 500 1 2 3000 1 1003 100 0 0 303 花都 220 3 6 1020 3 10021 100 0 0 304 花都 110 21 41 1640 21 10025 100 0 0 305 花都小计 25 49 5660 25 1003 100 0 0 306 增城 220 3 7 1200 3 10029 100 0 0 307 增城 110 29 63 2454.5 29 10032 100 0 0 308 增城小计 32 70 3654.5 32 1001 100 0 0 309 番禺 500 1 2 2000 1 1007 100 0 0 310 番禺 220 7 16 3420 7 10039 100 0 0 311 番禺 110 39 92 4161 39 10047 100 0 0 312 番禺小计 47 110 9581 47 1005 100 0 0 313 荔湾 220 5 10 1860 5 10012 100 0 0 314 荔湾 110 12 26 1129 12 10017 100 0 0 315 荔湾小计 17 36 2989 17 1002 100 0 0 316 海珠 220 2 6 1080 2 1006 100 0 0 317 海珠 110 6 14 626 6 1008 100 0 0 318 海珠小计 8 20 1706 8 1008 100 0 0 319 越秀 110 8 22 946 8 1008 100 0 0 320 越秀小计 8 22 946 8 1003 100 0 0 321 天河 220 3 9 1620 3 10011 100 0 0 322 天河 110 11 30 1519 11 10014 100 0 0 323 天河小计 14 39 3139 14 1001 100 0 0 324 黄埔 220 1 2 300 1 1005 100 0 0 325 黄埔 110 5 12 500 5 1006 100 0 0 326 黄埔小计 6 14 800 6 1001 100 0 0 327 萝岗 500 1 3 2250 1 1002 100 0 0 328 萝岗 220 2 5 960 2 1006 100 0 0 329 萝岗 110 6 14 600 6 1009 100 0 0 330 萝岗小计 9 22 3810 9 1001 100 0 0 331 白云 500 1 3 2250 1 1009 100 0 0 332 白云 220 9 20 4020 9 10024 100 0 0 333 白云 110 24 58 2322.6 24 10034 100 0 0 334 白云小计 34 81 8592.6 34 1004 100 0 0 335 小计 500 4 10 9500 4 10037 100 0 0 336 小计 220 37 85 16140 37 100174 100 0 0 337 小计 110 174 395 16612.6 174 100215 100 0 0 338 合计 ^LEFT 215 490 42252.6 215 100--ps:描述问题要清楚点，浪费我那么多精力，最后还是帮你搞定了，要下班了，再有什么要求我就不管了
------解决方案--------------------

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

热点排行

Bad Request.

oracle

一个取数有关问题