一条sql语句

Question

表结构
id(主键)   projectid(项目编号)   money(金额)
1               301(一级)           求合计
2               301001（二级）      求合计
3               301001001（三级）   100.00
4               301001002（三级）   100.00 
5               301002              
6               301002001           
7               301002002           
8               302                 
9               302001              
10              302001001
11              302001002
12              302002
13              302002001
14              302002002
15              303                
16              303001             
17              303002
18              303003表中数据 如上
现在想写一条update语句
更新一级目前和二级目前的金额
例如：
  301     ---- (301001 + 301002)
  301001  ---- (301001001 + 301001002)
注意： 303 这个项目少了一级

Answer 1

update table1 a
   set money = (select money
                  from (select 2 level, projectid, sum(money) money
                          from table1 t
                         where length(projectid) > 6
                           and exists
                         (select '1'
                                  from table1 b
                                 where length(b.projectid) > 6
                                   and b.projectid like t.projectid || '%')
                         group by substr(projectid, 1, 6) --取出各二级编号中的金额（不包括303的下一级）
                        union all
                        select 1 level, projectid, sum(money) money
                          from table1 t
                         where length(projectid) > 6
                           and exists
                         (select '1'
                                  from table1 b
                                 where length(b.projectid) > 6
                                   and b.projectid like t.projectid || '%')
                         group by substr(projectid, 1, 3) --取出有三级编号的各一级编号的金额（不包括303）
                        union all
                        select 1 level, projectid, sum(money) money
                          from table1 t
                         where length(projectid) > 6
                           and not exists
                         (select '1'
                                  from table1 b
                                 where length(b.projectid) > 6
                                   and b.projectid like t.projectid || '%')
                         group by substr(projectid, 1, 3) --取出没有三级编号的各一级编号的金额（303）
                        ) b
                 where b.projectid = a.projectid)
 where length(a.projectid) <= 6
试试这个