我的一道面试sql试题,我不是高手,我不会,是你会做?

[yanleigis]

有如下表:
create table test (id varchar2(3),total number(14,1));
insert into test (id) values ('1');
insert into test (id) values ('11');
insert into test (id,total) values ('111',300);
insert into test (id,total) values ('112',400);
insert into test (id) values ('12');
insert into test (id,total) values ('121',310);
insert into test (id,total) values ('122',420);
insert into test (id,total) values ('123',400);
insert into test (id) values ('2');
insert into test (id,total) values ('21',300);
insert into test (id,total) values ('121',30);
insert into test (id,total) values ('122',400);
commit;
id是代码分级,就是"1"是所有"1"开头两位父,"11"又是所有"11"开头三为父,依次类推,一个级的子级是不定的,假如所有三级都赋值,请你使用一或者两sql给二级,和一级赋值(父等于所有子之和)

[yanleigis]

update test a set total=( select sum(total) from test b where instr(b.id,a.id)>0 and length(b.id)=3) where length(id)=2;
update test a set total=( select sum(total) from test b where instr(b.id,a.id)>0 and length(b.id)=2) where length(id)=1;
是可以的,有一句的吗?
还有没有更好的方法?

[Robertlee3511]

可可兄，你的有误多计算了一些。
应该如下：
update test a set a.total=( select sum(b.total)
from test b where instr(substr(b.id,1,2),a.id)>0 and length(b.id)=3)
where length(a.id)=2;
update test a set a.total=( select sum(b.total)
from test b where instr(substr(b.id,1,2),a.id)>0 and length(b.id)=2)
where length(a.id)=1；

[zero88]

update test a set total = (select count(id) from test b where
length(a.id) < length(b.id)
and substr(b.id,1,length(a.id)) = a.id
and length(a.id) + 1 = length(b.id)) where length(a.id) <> 3;
一句可以解决问题

[zero88]

update test a set total = (select count(id) from test b where
substr(b.id,1,length(a.id)) = a.id
and length(a.id) + 1 = length(b.id)) where length(a.id) <> 3;
上面的多了一个条件，Sorry

[snowy_howe]

update test a set total = (select sum(total) from test b where b.id like a.id||'%');

如果id是主键的话，是没有错误的，但是我看你的数据，好象不是主键。
但是又说不过去。
insert into test (id,total) values ('121',310);
insert into test (id,total) values ('122',420);

insert into test (id,total) values ('121',30);
insert into test (id,total) values ('122',400);

这里两次插入，都被我的语句更新成了一样的拉。
希望能有人提出更好的SQL

[snowy_howe]

附上测试结果
SQL> select * from test;

ID TOTAL
--- ----------
1
11
111 300
112 400
12
121 310
122 420
123 400
2
21 300
121 30

ID TOTAL
--- ----------
122 400

已选择12行。

SQL> update test a set total = (select sum(total) from test b where b.id like a.id||'%');

已更新12行。

SQL> commit;

提交完成。

SQL> select * from test;

ID TOTAL
--- ----------
1 2260
11 700
111 300
112 400
12 1560
121 340
122 820
123 400
2 300
21 300
121 340

ID TOTAL
--- ----------
122 820

已选择12行。

[yanleigis]

还有没有三级类的,

[yanleigis]

三级类可以重复,二级,和一级没有重复

[KingSunSha]

这个问题只讨论更新某一层意义不大，这里有一个语句可以完成对所有层的更新（除了最底层，并且前提条件是最底层的数据是完整的）：
UPDATE test x
SET total =
(SELECT SUM (total)
FROM test y
WHERE y.ID LIKE x.ID || '%'
AND y.ID != x.ID
AND NOT EXISTS (SELECT 'x'
FROM test z
WHERE z.ID LIKE y.ID || '%'
AND z.ID != y.ID))
WHERE NOT EXISTS (SELECT 'x'
FROM test
WHERE ID LIKE x.ID || '%'
AND ID != x.ID);

[zhzhs007]

snowy_howe(天下有雪)
KingSunSha(弱水三千)
错的东西也发上来啊，为什么自己不试试呢？



哎~~~~~~

[wenjy]

不好意思，我不懂oracle的sql语法只用了笨方法得出结果,
请大家看一下,应该得出结果了

update test set total =
(select s.b
from
(select substr(a,1,1) as a,sum(b) as b from
(select substr(id,1,2) as a,sum(total) as b from test group by substr(id,1,2)) t
group by substr(a,1,1)
union all
select substr(id,1,2) as a,sum(total) as b from test where length(id) <> 1 group by substr(id
,1,2)
) s
where s.a = test.id)
where length(id)<>3

[wenjy]

SQL> select * from test;

ID TOTAL
--- ----------
1
11
111 300
112 400
12
121 310
122 420
123 400
2
21 300
121 30

ID TOTAL
--- ----------
122 400

已选择12行。

[wenjy]

执行后:
SQL> select * from test order by id;

ID TOTAL
--- ----------
1 2260
11 700
111 300
112 400
12 1560
121 310
121 30
122 420
122 400
123 400
2 300

ID TOTAL
--- ----------
21 300

已选择12行。

[ghl200]

update test a set total=(select sum(total) from test b where b.id like a.id
||'%' and a.rowid<>b.rowid)
好像是个很古老的问题
rowid避免更新太多行

[KingSunSha]

对不起，我的语句里最后的where条件多写了一个not，结果意思全反了。现在修正过并通过了测试：
UPDATE test x
SET total =
(SELECT SUM (total)
FROM test y
WHERE y.ID LIKE x.ID || '%'
AND y.ID != x.ID
AND NOT EXISTS (SELECT 'x'
FROM test z
WHERE z.ID LIKE y.ID || '%'
AND z.ID != y.ID))
WHERE EXISTS (SELECT 'x'
FROM test
WHERE ID LIKE x.ID || '%'
AND ID != x.ID);

**注：贴主给出的例子有点小问题，id=121，122的纪录各有两条
解题的思路：对于任何一层非底层的纪录，要取的总计值总是该纪录最底层纪录的总和。这么考虑，就不需要限定哪一层了。