本帖最后由 fulima007 于 2012-11-23 11:44:17 编辑 两个表
表1：主键是(id, name, path)
id name path value
1 fu1 /home 0
2 fu2 /home 1
3 fu3 /home 2
3 fu3 /test 0
1 fu1 /test 4
其中value=0 代表成功，value！= 0代表失败
表2：表的主键是(id, name)同样是外键，
id name succ fail
1 fu1 1 1
2 fu2 0 1
3 fu3 1 1
写一个sql语句，通过查询表1并将结果插入到表2中。
------最佳解决方案--------------------
insert into tb2
select
id ,
name ,
sum(decode(value,0,1,0)) succ ,
sum(decode(value,0,0,1)) fail
from tb1
group by id ,name
order by id ,name
------其他解决方案--------------------
merge into tb2
using tb1
on(tb1.id=tb2.id and tb1.name=tb2.name)
when matched then
update tb2
set (succ,fail)=(select a.succ,a.fail from(
select id , name , sum(decode(value,0,1,0)) succ ,
sum(decode(value,0,0,1)) fail
from tb1
group by id ,name
order by id ,name)a);
when not matched then
insert into tb2
select id ,name ,sum(decode(value,0,1,0)) succ ,sum(decode(value,0,0,1)) fail
from tb1
group by id ,name
order by id ,name;
------其他解决方案--------------------
merge into tb2
using tb1
on(tb1.id=tb2.id and tb1.name=tb2.name)
when matched then
update tb2
set (succ,fail)=(select a.succ,a.fail from(
select id , name , sum(decode(value,0,1,0)) succ ,
sum(decode(value,0,0,1)) fail