sql高手进来看看如何用sql实现舍位平衡？（带测试数据）

Question

本帖最后由 xuangxing 于 2009-09-21 23:14:07 编辑

Answer 1

附上测试数据：
create table TEST
(
  ZC_CODE  VARCHAR2(20),
  MONEY_元  NUMBER(35),
  MONEY_万元 NUMBER(35)
);
insert into TEST (ZC_CODE, MONEY_元, MONEY_万元)
values ('201', 18600, null);insert into TEST (ZC_CODE, MONEY_元, MONEY_万元)
values ('20101', 9600, null);insert into TEST (ZC_CODE, MONEY_元, MONEY_万元)
values ('2010101', 3300, null);insert into TEST (ZC_CODE, MONEY_元, MONEY_万元)
values ('2010102', 3200, null);insert into TEST (ZC_CODE, MONEY_元, MONEY_万元)
values ('2010103', 3100, null);insert into TEST (ZC_CODE, MONEY_元, MONEY_万元)
values ('20102', 9000, null);insert into TEST (ZC_CODE, MONEY_元, MONEY_万元)
values ('2010201', 2000, null);insert into TEST (ZC_CODE, MONEY_元, MONEY_万元)
values ('2010202', 7000, null);commit;

Answer 2

感谢wildwave利用中午时间帮我看问题.......

Answer 3

被别的问题弄得晕头转向了..
这里麻烦在：若201为第一级，2010101为最后一级，那么当第二级需要将金额值最大的那条记录的万元位加一时，第三级就要根据第二级的新值来判断，然后第四级。。到最后一级
所以一条sql实现可能性很小
还有，如果例中20102      9000      1 的万元不是1，而是进位后变成2，怎么办，要让下一级的3300的万元变成2吗

Answer 4

如果不会出现我上面说的情况的话
update test tt set money_万元=
  (select case when rn=1 and a1<b then a+1 else a end money_万元  from(
    select a.zc_code,row_number()over(partition by b.zc_code order by a.money_元 desc)rn,
      round(a.money_元/10000)a,
      sum(round(a.money_元/10000))over(partition by substr(a.zc_code,1,length(a.zc_code)-2))a1,
      round(b.money_元/10000)b
      from test a left join test b
      on substr(a.zc_code,1,length(a.zc_code)-2)=b.zc_code
      )t where zc_code=tt.zc_code)
否则比如3300元=2万元3万元，会有点雷人..

Answer 5

否则如果有n级，执行最多n-1次
update test tt set money_万元=
  (select case when rn=1 and a1<b then a+1 else a end money_万元  from(
    select a.zc_code,row_number()over(partition by b.zc_code order by a.money_元 desc)rn,
      a.money_万元 a,
      sum(a.money_万元)over(partition by substr(a.zc_code,1,length(a.zc_code)-2))a1,
      b.money_万元 b
      from test a left join test b
      on substr(a.zc_code,1,length(a.zc_code)-2)=b.zc_code
      )t where zc_code=tt.zc_code)
where exists(
  select 1  from(
    select a.zc_code,row_number()over(partition by b.zc_code order by a.money_元 desc)rn,
      a.money_万元 a,
      sum(a.money_万元)over(partition by substr(a.zc_code,1,length(a.zc_code)-2))a1,
      b.money_万元 b
      from test a left join test b
      on substr(a.zc_code,1,length(a.zc_code)-2)=b.zc_code
      ) where rn=1 and a1<b and tt.zc_code=zc_code)
直到没有记录被更新

Answer 6

写了一个好复杂的sql，大家一起讨论一下我的数据
create table TEST
(
  ZC_CODE  VARCHAR2(20),
  MONEY  NUMBER(35)
);insert into TEST (ZC_CODE, MONEY) values ('201', 18600);
insert into TEST (ZC_CODE, MONEY) values ('20101', 9600);
insert into TEST (ZC_CODE, MONEY) values ('2010101', 3300);
insert into TEST (ZC_CODE, MONEY) values ('2010102', 3200);
insert into TEST (ZC_CODE, MONEY) values ('2010103', 3100);
insert into TEST (ZC_CODE, MONEY) values ('20102', 9000);
insert into TEST (ZC_CODE, MONEY) values ('2010201', 2000);
insert into TEST (ZC_CODE, MONEY) values ('2010202', 7000);SQL> select zc_code, money from test;
ZC_CODE                                                           MONEY
------------------------------------------------------------ ----------
201                                                               18600
20101                                                              9600
2010101                                                            3300
2010102                                                            3200
2010103                                                            3100
20102                                                              9000
2010201                                                            2000
2010202                                                            7000alter table test add (money_2 NUMBER(35));

Answer 7

第一步，
算出父子结构关系，并且计算出儿子的合集select level l,
                       substr(zc_code, 1, length(zc_code) - 2) pname,
                       zc_code,
                       money,
                       round(money / 10000) t,
                       lpad(' ', level * 2, ' ') || zc_code t2
                  from test
                connect by prior
                            zc_code = substr(zc_code, 1, length(zc_code) - 2)
                 start with length(zc_code) = 3
         L PNAME                                                        ZC_CODE                                             MONEY          T T2
---------- ------------------------------------------------------------ -------------------- ------------------------------------ ---------- --------------------------------------------------------------------------------
         1 2                                                            201                                                 18600          2   201
         2 201                                                          20101                                                9600          1     20101
         3 20101                                                        2010101                                              3300          0       2010101
         3 20101                                                        2010102                                              3200          0       2010102
         3 20101                                                        2010103                                              3100          0       2010103
         2 201                                                          20102                                                9000          1     20102
         3 20102                                                        2010201                                              2000          0       2010201
         3 20102                                                        2010202                                              7000          1       2010202

Answer 8

第二步，上面的结果，进行自关联， 用PNAME 和 ZC_CODE进行关联，以便我们可以用pname来分组统计，来计算一个父亲下的儿子的合计是否保持平衡
SQL> col zccode format a10;
SQL> col pzccode format a6;
SQL> col pmoney format 999999;
SQL> col pt format 99;
SQL> col money format 999999;
SQL> col currentt format 99;
SQL> col t2 format a15;
SQL> col MAXMONEY format 999999;
SQL> col SUMMONEY format 99;select a1.zc_code pzccode,
               a1.money pmoney,
               a1.t pt,
               a2.money money,
               a2.zc_code zccode,               
               a2.t currentt,
               a2.t2,
               max(a2.money) over(partition by a1.zc_code) maxmoney,
               sum(a2.t) over(partition by a1.zc_code) summoney
          from (select level l,
                       substr(zc_code, 1, length(zc_code) - 2) pname,
                       zc_code,
                       money,
                       round(money / 10000) t,
                       lpad(' ', level * 2, ' ') || zc_code t2
                  from test
                connect by prior
                            zc_code = substr(zc_code, 1, length(zc_code) - 2)
                 start with length(zc_code) = 3) a1,
               (select level l,
                       substr(zc_code, 1, length(zc_code) - 2) pname,
                       zc_code,
                       money,
                       round(money / 10000) t,
                       lpad(' ', level * 2, ' ') || zc_code t2
                  from test
                connect by prior
                            zc_code = substr(zc_code, 1, length(zc_code) - 2)
                 start with length(zc_code) = 3) a2
         where a1.zc_code = a2.pnamePZCCOD PMONEY PT  MONEY ZCCODE     CU T2              MAXMON SU(SUMMONEY)
------ ------ -- ------ ---------- -- --------------- ------ --
201     18600  2   9600 20101       1     20101         9600  2
201     18600  2   9000 20102       1     20102         9600  2
20101    9600  1   3300 2010101     0       2010101     3300  0
20101    9600  1   3200 2010102     0       2010102     3300  0
20101    9600  1   3100 2010103     0       2010103     3300  0
20102    9000  1   2000 2010201     0       2010201     7000  1
20102    9000  1   7000 2010202     1       2010202     7000  1
通过这里的结果集 可以看到，我们需要平衡的地方是 PT<>SUMMONEY  SUMMONEY是一层儿子的合计，如果和父亲的不一样就需要做平衡了。而且需要做平衡的儿子的记录，应该是money里最大的也就是值为MAXMON的

Answer 9

第三步，根据上面的结果找出需要修改的记录select * from (select tmp.*, row_number() over(partition by pzccode order by zccode desc) rn
  from (select a1.zc_code pzccode,
               a1.money pmoney,
               a1.t pt,
               a2.money money,
               a2.zc_code zccode,               
               a2.t currentt,
               a2.t2,
               max(a2.money) over(partition by a1.zc_code) maxmoney,
               sum(a2.t) over(partition by a1.zc_code) summoney
          from (select level l,
                       substr(zc_code, 1, length(zc_code) - 2) pname,
                       zc_code,
                       money,
                       round(money / 10000) t,
                       lpad(' ', level * 2, ' ') || zc_code t2
                  from test
                connect by prior
                            zc_code = substr(zc_code, 1, length(zc_code) - 2)
                 start with length(zc_code) = 3) a1,
               (select level l,
                       substr(zc_code, 1, length(zc_code) - 2) pname,
                       zc_code,
                       money,
                       round(money / 10000) t,
                       lpad(' ', level * 2, ' ') || zc_code t2
                  from test
                connect by prior
                            zc_code = substr(zc_code, 1, length(zc_code) - 2)
                 start with length(zc_code) = 3) a2
         where a1.zc_code = a2.pname) tmp where pt<>summoney and money=maxmoney) where rn=1这里需要注意，由于考虑到可能有的孩子可能有多条都和maxmoney一致的，所以加了一个rn=1，如果有此情况，只找第一条。PZCCOD PMONEY PT  MONEY ZCCODE     CU T2              MAXMON SU         RN
------ ------ -- ------ ---------- -- --------------- ------ -- ----------
20101    9600  1   3300 2010101     0       2010101     3300  0          1

Answer 10

以上执行出来的结果没有舍位平衡。以下结果为：
L PNAME ZC_CODE MONEY T   T2
1 2 201 18600 2   201
2 201 20101 9600 1     20101
3 20101 2010101 3300 0       2010101
3 20101 2010102 3200 0       2010102
3 20101 2010103 3100 0       2010103
2 201 20102 9000 1     20102
3 20102 2010201 2000 0       2010201
3 20102 2010202 7000 1       2010202
正确的结果应为：
L PNAME ZC_CODE MONEY T   T2
1 2 201 18600 2   201
2 201 20101 9600 1     20101
3 20101 2010101 3300 1       2010101
3 20101 2010102 3200 0       2010102
3 20101 2010103 3100 0       2010103
2 201 20102 9000 1     20102
3 20102 2010201 2000 0       2010201
3 20102 2010202 7000 1       2010202平衡原则：201金额＝20101金额＋20102金额；20101金额＝2010101金额＋2010102金额＋2010103金额；20102金额＝2010201金额＋2010202金额 

Answer 11

好复杂哦，inthirties兄怎么把他们的执行结果并在一起啊

Answer 12

第四步，大功告成了只需要做一下关联，就可以把需要的信息选出来了。select test.zc_code, test.money, nvl(t.currentt+1, round(test.money / 10000)) money2 from test left join
(select * from (select tmp.*, row_number() over(partition by pzccode order by zccode desc) rn
  from (select a1.zc_code pzccode,
               a1.money pmoney,
               a1.t pt,
               a2.money money,
               a2.zc_code zccode,               
               a2.t currentt,
               a2.t2,
               max(a2.money) over(partition by a1.zc_code) maxmoney,
               sum(a2.t) over(partition by a1.zc_code) summoney
          from (select level l,
                       substr(zc_code, 1, length(zc_code) - 2) pname,
                       zc_code,
                       money,
                       round(money / 10000) t,
                       lpad(' ', level * 2, ' ') || zc_code t2
                  from test
                connect by prior
                            zc_code = substr(zc_code, 1, length(zc_code) - 2)
                 start with length(zc_code) = 3) a1,
               (select level l,
                       substr(zc_code, 1, length(zc_code) - 2) pname,
                       zc_code,
                       money,
                       round(money / 10000) t,
                       lpad(' ', level * 2, ' ') || zc_code t2
                  from test
                connect by prior
                            zc_code = substr(zc_code, 1, length(zc_code) - 2)
                 start with length(zc_code) = 3) a2
         where a1.zc_code = a2.pname) tmp where pt<>summoney and money=maxmoney) where rn=1) t on t.zccode=test.zc_code order by test.zc_code;现在看看结果吧
ZC_CODE               MONEY     MONEY2
-------------------- ------ ----------
201                   18600          2
20101                  9600          1
2010101                3300          1
2010102                3200          0
2010103                3100          0
20102                  9000          1
2010201                2000          0
2010202                7000          1
 
8 rows selected
有了这个结果，update那个字段，就交给你自己叻吧。总结：这里为了简化方法，没有考虑 父<子+子的状况
比如
201    12000
20101  5500
20101  650012000=5500+65001=1+1 这样也是不平衡的，在这里没有考虑这样的情况，不过应该还是可以按照上面的思路来解决，无非是在第三步的时候，用case when来判断，如果大于，给个标示，如果小于，给个表示在第四步的时候，根据第三步的标示来进行+或者-的计算。

Answer 13

非常感谢inthirties兄大力帮忙，我先消化下～～

Answer 14

..inthirties大哥这么晚还在研究这个问题
还是没考虑到一个问题：这次改动将某些money_万元的值+1，由此会导致新的问题：这个记录的子记录中也有一个最大的值需要加1以达平衡
还有，那个t2，前面加了若干空格有什么用啊..

Answer 15

是呀，回来的晚，兴趣是学习的最好老师，所以一起和你们研究了哟这里
t2是用来讲解的作用的，只是表现的一个父子的显示格式，所以用的lpad的，在结果集里有几个字段都是和我们需要的结果没有关系的，比如level和t2, 他们是让大家更好的观察逻辑的。这个sql这是一个解题的思路，实际处理里面应该要考虑的更细一些。不过不会出现你顾虑的这个问题。
和你的思路不一样，你是用递归的procedure来解决的，我这里是先做的要更新的结果集的查询，先把每个row的结果先确定下来，然后一次性的做所有的update，所以只有一次update，不会出现你说的update了1条，又影响到下一条这样的情况。

Answer 16

呵呵，这个问题，我也想到了，20000=4000+4000+4000+4000+4000
2=1+0+0+0+0
所以这里还要平衡第二位。
所以这里需要比较好的一个平衡的规则。不过基本上这样的还是可以用sql搞定的。

Answer 17

不是这样的..我看了下代码，其实你的思路和我8楼的代码是差不多的，我先前也考虑过用层次化查询，但写了那个connect by 字句后就觉得没有必要，那个条件完全可以转成外连接条件，level也可以通过长度来确定。
你说一定确定每个row的结果，实际上确定的只是update前的值，至于那个万元要进1事先无法得知，因此该值的下一级记录无法做到在此基础上多加1。
给你个例子，比如楼主的实例里改2个值，20101和20102的金额分别改成4500和4000，那么，4500的完全本是0，但因为18000大于0，要将4500的万元字段进到1，由此引发2010101的万元字段也要加1。但是一条能实现吗

Answer 18

感觉你好像还是有些误解。我这里不需要udpate多次，update一次就可以了，先通过select把所有row需要更新成的值找出来，一次性用zc_code和我提供的结果集关联，更新就可以了。level是无意义的。是为了让大家看的更清楚一些逻辑而已。

Answer 19

汗..我的update多次是为了处理可能碰到的上面说的情况
我也将所有的记录关联了，问题就在于你说的需要更新成的值随着更新的进行可能是会改变的
你就按我28楼说的，将test表中那两条记录的值改下，再运行代码，然后看看2010101    3300      这一项的万元还为1吗
但它实际上应该为1 ，这就是我的问题所在

Answer 20

不好意思，用户的需求有点小变化,以下测试数据，用户希望得到如下结果：
ZC_CODE MONEY MONEY_2
201 17000 2
20101 13000 1
2010101 9000 1
2010102 4000 0
2010103 0 0
20102 4000 1
2010201 1000 0
2010202 3000 3附上测试数据：insert into test (ZC_CODE, MONEY, MONEY_2)
values ('201', 17000, null);insert into test (ZC_CODE, MONEY, MONEY_2)
values ('20101', 13000, null);insert into test (ZC_CODE, MONEY, MONEY_2)
values ('2010101', 9000, null);insert into test (ZC_CODE, MONEY, MONEY_2)
values ('2010102', 4000, null);insert into test (ZC_CODE, MONEY, MONEY_2)
values ('2010103', 0, null);insert into test (ZC_CODE, MONEY, MONEY_2)
values ('20102', 4000, null);insert into test (ZC_CODE, MONEY, MONEY_2)
values ('2010201', 1000, null);insert into test (ZC_CODE, MONEY, MONEY_2)
values ('2010202', 3000, null);

Answer 21

不好意思结果写错了，正确结果应为：
ZC_CODE MONEY MONEY_2 
201     17000 2 
20101   13000 1 
2010101 9000  1 
2010102 4000  0 
2010103 0     0 
20102   4000  1 
2010201 1000  0 
2010202 3000  1 

Answer 22

按原先的需求得到结果为：
ZC_CODE MONEY MONEY2
201 17000 2
20101 13000 2
2010101 9000 1
2010102 4000 0
2010103 0 0
20102 4000 0
2010201 1000 0
2010202 3000 0
但这个结果不是用户想要的。

Answer 23

...那么这个逻辑是什么样的？
下一级的万元总和小于上一级的万元时
多出的这个1加在哪一项上
是万元最小，万元相同时money最大的那项吗