楼主依然没有把问题阐述清楚
两个问题
1、好像是以前6位分组的
2、有下面的情况吧,如果有,那么它们的值分别应该是多少呢?
003006007 100
003006003 NULL
003006000 NULL
003000000 NULL
两个问题
1、好像是以前6位分组的
2、有下面的情况吧,如果有,那么它们的值分别应该是多少呢?
003006007 100
003006003 NULL
003006000 NULL
003000000 NULL
substr(col1,1,3)||'000000'=col1)
where instr(col2,-6)='000000';
update tbname set col2=(select sum(col2) scol2 from tb where
substr(col1,1,6)||'000'=col1)
where instr(col2,-6)='000';
你的考虑是多余的,没有这样的项。
我的数据只是举个例子,没有更多的组合了。也就是说表中的树状结构已经生成,就看如何得到那些汇总项了,需要分步汇总。to:bzszp(SongZip)
好像一次不能更新多个字段吧,有好多字段呢,难道写n个update?谢谢楼上2位
update tbname set col2=decode(substr(col1,-6),
'000000',(select sum(col2) scol2 from tb where substr(col1,1,3)||'000000'=col1),
(select sum(col2) scol2 from tb where
substr(col1,1,6)||'000'=col1))
where instr(col1,-6)='000' or instr(col1,-6)='000000';
否则就只能用多条sql语句了。
'000000',(select sum(col2) scol2 from tb where substr(col1,1,3)||'000000'=col1),
(select sum(col2) scol2 from tb where
substr(col1,1,6)||'000'=col1))
where instr(col1,-6)='000' or instr(col1,-6)='000000';这个语句能实现更新‘000’的情况吗?update tbname set col2=(select sum(col2) scol2 from tb where
substr(col1,1,3)||'000000'=col1)
where instr(col2,-6)='000000';
update tbname set col2=(select sum(col2) scol2 from tb where
substr(col1,1,6)||'000'=col1)
where instr(col2,-6)='000';能实现这2句的功能吗?
'000000',(select sum(col2) scol2 from tb where substr(col1,1,3)||'000000'=col1),
(select sum(col2) scol2 from tb where
substr(col1,1,6)||'000'=col1))
where instr(col1,-3)='000' or instr(col1,-6)='000000';
--笔误,应该是-3,可以更新后三位是'000'的数据
再问一下,这个decode中的2个条件选择都是针对一开始的表中数据吧,我是在想,如果先处理完第一层,在处理第二层的时候,会不会把第一层的数据也加进来,因为这部分数据也满足第二次汇总的条件,好像还是表达的不清楚,就是说下面两条语句顺序执行,好像结果是不对的啊
update tbname set col2=(select sum(col2) scol2 from tb where
substr(col1,1,3)||'000000'=col1)
where instr(col2,-6)='000000';update tbname set col2=(select sum(col2) scol2 from tb where
substr(col1,1,6)||'000'=col1)
where instr(col2,-3)='000';第一句的那些项也符合第2句的查询条件,这样岂不是重复?
用decode不知道能不能避免这个问题?请指教,msn:[email protected],如果可以,可以暂时加一下,聊完删,不胜感谢
:(对,确实有问题,where instr(col1,-3)='000' and instr(col1,-6)<>'000000';
SQL> select * from aa;ID SCORE
--------- ----------
001002000 100
001000000
003004005 100
003004000
003006007 100
003006000
0030000007 rows selected.SQL> update aa x set x.score=(select y.nn from (
2 select qqq.id,0 qq,qqq.nn from(
3 SELECT id,decode(score,'',0,score) qq,
4 SUM(decode(score,'',0,score))
5 OVER (PARTITION BY substrb(id,1,6)
6 ORDER BY substrb(id,1,6),substrb(id,7,3) desc
7 ROWS 2 PRECEDING) nn
8 FROM aa) qqq where qqq.nn>0
9 union
10 select substr(a.id,1,3)||'000000' myid,count(substr(a.id,1,3)) nn,sum(a.nn)/decode((count(subst
r(a.id,1,3))/2),0.5,1,(count(substr(a.id,1,3))/2)) nn from(
11 SELECT id,decode(score,'',0,score) qq,
12 SUM(score)
13 OVER (PARTITION BY substrb(id,1,6)
14 ORDER BY substrb(id,1,6),substrb(id,7,3) desc
15 ROWS 2 PRECEDING) nn
16 FROM aa) a where substr(a.id,4,6)<>'000000' group by substr(a.id,1,3)||'000000') y where x.id=y
.id);7 rows updated.
--------- ----------
001002000 100
001000000 100
003004005 100
003004000 100
003006007 100
003006000 100
003000000 2007 rows selected.
update aa x set x.score=(select y.nn from (
select qqq.id,0 qq,qqq.nn from(
SELECT id,decode(score,'',0,score) qq,
SUM(decode(score,'',0,score))
OVER (PARTITION BY substrb(id,1,6)
ORDER BY substrb(id,1,6),substrb(id,7,3) desc
ROWS 2 PRECEDING) nn
FROM aa) qqq where qqq.nn>0
union
select substr(a.id,1,3)||'000000' myid,count(substr(a.id,1,3)) nn,sum(a.nn)/decode((count(substr(a.id,1,3))/2),0.5,1,(count(substr(a.id,1,3))/2)) nn from(
SELECT id,decode(score,'',0,score) qq,
SUM(score)
OVER (PARTITION BY substrb(id,1,6)
ORDER BY substrb(id,1,6),substrb(id,7,3) desc
ROWS 2 PRECEDING) nn
FROM aa) a where substr(a.id,4,6)<>'000000' group by substr(a.id,1,3)||'000000') y where x.id=y.id);
辛苦了,^_^我这个问题不一定非用一个sql实现,这个我应该早点说
这是想问问大家有什么好的方法,提点建议
不过一个sql实现也无所谓,因为我的数据很少,再复杂的语句也不存在效率问题我回家继续研究你的sql,也请其它达人帮助看看,我特别想找个最优的方法谢谢楼上2位,也感谢后来的,继续关注。
--------- ----------
001002000 100
001000000
003004005 100
003004000
003006007 100
003006000
003000000已选择7行。update aa x set x.score=(select y.score from
(
select id,score from
(
select id,score,row_number()
over (partition by substr(id,1,6) order by id desc) top2
from aa where substr(id,4,6)!='000000'
)
where top2=1
union
select min(substr(id,1,6))||'000' id,max(score) score from aa
where substr(id,4,6)!='000000'
group by substr(id,1,6)||'000' having count(substr(id,1,6))>1
union
select substr(id,1,3)||'000000' id,sum(score) score from(
select * from
(
select id,score,row_number()
over (partition by substr(id,1,6) order by id desc) top2
from aa where substr(id,4,6)!='000000'
)
where top2=1) a
group by substr(a.id,1,3)||'000000'
) y
where x.id=y.id);已更新7行。SQL> select * from aa;ID SCORE
--------- ----------
001002000 100
001000000 100
003004005 100
003004000 100
003006007 100
003006000 100
003000000 200已选择7行。再执行一遍,
SQL> update aa x set x.score=(select y.score from
2 (
3 select id,score from
4 (
5 select id,score,row_number()
6 over (partition by substr(id,1,6) order by id desc) top2
7 from aa where substr(id,4,6)!='000000'
8 )
9 where top2=1
10 union
11 select min(substr(id,1,6))||'000' id,max(score) score from aa
12 where substr(id,4,6)!='000000'
13 group by substr(id,1,6)||'000' having count(substr(id,1,6))>1
14 union
15 select substr(id,1,3)||'000000' id,sum(score) score from(
16 select * from
17 (
18 select id,score,row_number()
19 over (partition by substr(id,1,6) order by id desc) top2
20 from aa where substr(id,4,6)!='000000'
21 )
22 where top2=1) a
23 group by substr(a.id,1,3)||'000000'
24 ) y
25 where x.id=y.id);已更新7行。SQL> select * from aa;ID SCORE
--------- ----------
001002000 100
001000000 100
003004005 100
003004000 100
003006007 100
003006000 100
003000000 200已选择7行。
即:
ID SCORE
--------- ----------
001002000 100
001000000
003004005 100
003004000
003006007 100
003006000
003000000
每次UPDATE都会得到如下结果,除非你更改了叶节点的值
ID SCORE
--------- ----------
001002000 100
001000000 100
003004005 100
003004000 100
003006007 100
003006000 100
003000000 200
BTW:刚才俺向你提出的问题,你说我想多余了
可俺不是这么认为,从现在看来,你的叶节点(即第3层或第2层只有一条记录,
也就是没有UPDATE之前有值的那条,而我当时考虑了多于1张叶子的情况,
你又没说有几张叶子,只是给了一个结构,那谁知道你的叶子有几张呢?
还有,你的ID编号只能是现在这样的格式,即:
后6位为零表示根
前6位不为零,后3位为零表示第2层如果上述条件不变,俺的语句会每次仅根据叶子的值来计算上层的值
而不管当前非叶子的值是多少
所以可以update任意次,
结果都是对的
祝你好运~
001000000--001002000
001000000--001003000
004000000--004005000--004005006
004000000--004005000--004005007
004000000--004008000--004008009表:
id value
001000000
001002000 100 (一定没有001002004这样的项,对于前6位是001002的id,001002000是最底层了)
001003000 100
004000000
004005000 (这项一开始不会有数据,因为它是004005006延伸上来的)
004005006 100
004005007 100 (对于前6位是004005的项,可能有多个下层)
004008000
004008009 100
(总之,表中只有叶子结点有数据)结果:
001003000 100
001002000 100
001000000 200
004005006 100
004005007 100
004005000 200
004008009 100
004008000 100
004000000 300其实就是个3层的树状结构,叶子结点有数据,但是叶子结点不一定是最底层,求各层根结点数据。
如果更多层数据,是不是可以有个通用的算法?
达人多多帮忙。PS:谁能给点 分析函数语法 介绍类的东东,给我学习一下,感谢。
update aa x set x.score=(select y.score from
(
select id,score from
(
select id,score,row_number()
over (partition by substr(id,1,6) order by id desc) top2
from aa where substr(id,4,6)!='000000'
)
where top2=1
union
select distinct substr(id,1,6)||'000' id,
first_value(score)
over (partition by substr(id,1,6)||'000'
order by id desc) score
from aa where substr(id,4,6)!='000000'
union
select substr(id,1,3)||'000000' id,sum(score) score from(
select * from
(
select id,score,row_number()
over (partition by substr(id,1,6) order by id desc) top2
from aa where substr(id,4,6)!='000000'
)
where top2=1) a
group by substr(a.id,1,3)||'000000'
) y
where x.id=y.id);
003006007 100
003006003 NULL
003006000 NULL
003000000 NULL
=============
下面是你列出的,只不过我写了个NULL而已
004000000
004005000 (这项一开始不会有数据,因为它是004005006延伸上来的)
004005006 100
004005007 100 (对于前6位是004005的项,可能有多个下层)
还是让俺来解释一下楼主的意思吧第一层 第二层 第三层
001000000--001002000
001000000--001003000
004000000--004005000--004005006
004000000--004005000--004005007
004000000--004008000--004008009在表初始状态
第一层,总是没有值
第二层,如果没有第三层,那么这一层总是有值
第三层,总是有值不用管什么编号是否001002000啦,
下面的语句是通用的,可以适应楼主说的任何情况,如果我没有理解错
update aa x set x.score=(select y.score from
(
select id,score from
(
select id,score,row_number()
over (partition by substr(id,1,6) order by id asc) top1
from aa where substr(id,4,6)!='000000'
)
where top1!=1
union
select substr(id,1,6)||'000' id,sum(score) score from aa
where substr(id,4,6)!='000000'
group by substr(id,1,6)
having count(substr(id,1,6))=1
union
select substr(id,1,6)||'000' id,sum(score) score from aa
where substr(id,4,6)!='000000' and substr(id,7,3)!='000'
group by substr(id,1,6)
union
select substr(id,1,3)||'000000' id,sum(score) score from(
select substr(id,1,6)||'000' id,sum(score) score from aa
where substr(id,4,6)!='000000'
group by substr(id,1,6)
having count(substr(id,1,6))=1
union
select substr(id,1,6)||'000' id,sum(score) score from aa
where substr(id,4,6)!='000000' and substr(id,7,3)!='000'
group by substr(id,1,6)) b group by substr(b.id,1,3)||'000000') y
where x.id=y.id);